Dirty read in SQL
Prerequisite – Types of Schedules, Transaction Isolation Levels in DBMS There are mainly four types of common concurrency problems: dirty read, lost read, non-repeatable read and phantom reads. Dirty Reads – When a transaction is allowed to read a row that has been modified by an another transaction which is not committed yet that time Dirty Reads occurred. It is mainly occurred because of multiple transaction at a time which is not committed. Example – Table – Record
ID | Cus_Name | Balance |
---|---|---|
1 | S Adam | 100 |
2 | Zee Young | 150 |
Transaction – Transfer 10 from S Adam account to Zee Young account:
Input: BEGIN TRY BEGIN TRANSACTION UPDATE Table SET Balance = Balance - 10 WHERE ID=1; UPDATE Table SET Balance = Balance + 10 WHERE ID='C'; COMMIT TRANSACTION PRINT 'Committed' END TRY BEGIN CATCH ROLLBACK TRANSACTION PRINT 'Not Committed' END CATCH Output: Not committed
By executing the above query the output will be “Not committed” because there is an error, there is no ID=C. So at that time if we want to execute another transaction with that row that time Dirty Reads occurs. There is no partial commitment if both the UPDATE query succeed only then the output will be “Committed”. Before Execution: Table – Record
ID | Cus_Name | Balance |
---|---|---|
1 | S Adam | 100 |
2 | Zee Young | 150 |
After Execution:
Input: BEGIN TRY BEGIN TRANSACTION UPDATE Table SET Balance = Balance - 10 WHERE ID=1; UPDATE Table SET Balance = Balance + 10 WHERE ID='C'; COMMIT TRANSACTION PRINT 'Committed' END TRY BEGIN CATCH ROLLBACK TRANSACTION PRINT 'Not Committed' END CATCH Output: (1row affected) (0row affected) Not Committed
Note that if we put a valid ID first transaction result will come out as committed or 1 row affected but 2nd one will not be affected. Explanation – If we have a ticket booking system and One Customer is trying to book a ticket at that time available number of the ticket is 10, before completing the payment, the Second Customer wants to book a ticket that time this 2nd transaction will show the second customer that the number of the available tickets is 9. The twist is here if the first customer does not have sufficient fund in his debit card or in his wallet then the 1st transaction will Rollback, that time 9 seat available which is read by the 2nd transaction is Dirty Read. Example: Available ticket: For 1st customer
- 1st Step –
Input: -- Transaction 1 Select *from Bus_ticket;
- Output:
ID | Bus_Name | Available_Seat |
---|---|---|
1 | KA0017 | 10 |
- 2nd Step – Booking time for 1st customer
Input: --Transaction 1 BEING Transaction UPDATE Bus_Ticket set Available_Seat=9 WHERE ID=1 --Payment for Transaction 1 Waitfor Delay '00.00.30' Rollback transaction
- Available ticket: For 2nd customer while 1st customer is paying for the ticket.
- 3rd Step –
Input: -- Transaction 1 set transaction isolation level read uncommitted Select *from Bus_ticket where ID=1;
- Output:
ID | Bus_Name | Available_Seat |
---|---|---|
1 | KA0017 | 9 |
Note that during the payment of 1st customer 2nd transaction read it 9 seat is available if some how 1st transaction Rollback then available seat 9 that is Dirty read data. After rollback of the 1st transaction available seat is 10 again. 2nd and 3rd step happening at the same time. Actually available seat after rollback of transaction 1:
ID | Bus_Name | Available_Seat |
---|---|---|
1 | KA0017 | 10 |
Please Login to comment...