SQL Server Dirty Reads problems in concurrent transactions
Dirty Read is one of the most common problems that occures when concurrent transactions runs, and one transaction is permitted to read data that is being modified by another transaction that has not yet committed itself.
Ditry Read does not occure when the first transaction that modifies the data is committed successfully, but if the first transaction that modifies the data is rolled back after the second transaction reads the data then in this case second transaction has dirty data which does not actually exists .
Following diagram shows, how DIRTY READS occures in concurrent transactions.
Lets try to understand how Dirty Reads happens in concurrent transactions with the help of diagram.
As shown in above diagram, there are two concurrent transaction running that is TRANSACTION 1 and TRANSACTION 2.
Lets say, TRANSACTION 1 updates the total Item quantity from 15 to 14 in the table , then move to process the payment request. While TRANSACTION 1 is still in progress, TRANSACTION 2 reads the total quantity from the table that is 14 at the moment. In between TRANSACTION 1 fails due to some error in payment gateway and is rolled back.
So the quantity value is rolled back to the actual value that is 15, while TRANSACTION 2 still working on quantity value that is 14, which is called as dirty data and this issue is called a Dirty Reads in concurrent transactions.
Lets see the practicle in SQL Server.
Here we have a sample table named as ITEM.
CREATE TABLE ITEM (ITEM_ID INT PRIMARY KEY, ITEM_NAME VARCHAR(50), QTY INT) INSERT INTO ITEM (ITEM_ID, ITEM_NAME, QTY) VALUES(1, 'HP- LAPTOP',15)
SELECT @@SPID AS 'TRANSACTION 1'--- SESSION -----TRANSACTION 1 BEGIN TRAN SELECT * FROM ITEM WHERE ITEM_ID =1 UPDATE ITEM SET QTY = QTY -1 WHERE ITEM_ID=1 ---PRODUCED 10 SEC DELAY, ASSUME PAYMENT IS IN PROCESS WAITFOR DELAY '00:00:10' ROLLBACK TRAN --TRANSACTION IS ROLLED BACK DUE TO SOME PAYMENT GAYEWAY ISSUE SELECT * FROM ITEM WHERE ITEM_ID =1
------- ANOTHER SESSION/ QUERY WINDOW SELECT @@SPID AS 'TRANSACTION 2' -----TRANSACTION 2 SELECT * FROM ITEM WHERE ITEM_ID =1
It happens, because you have used artifical dealy (WAITFOR CLAUSE) to make TRANSATION 1 to wait for 10 sec then rolling back the transaction, which makes the TRANSACTION 2 to wait for 10 sec to finish TRANSACTION 1 execution first.
The answer is that the default isolation level for transactions is ‘READ COMMITTED’ which ensures that any transaction cannot read data that has been modified but not committed by other transactions, that’s why TRANSACTION 2 keeps executing for 10 sec until TRANSACTION 1 completed its execution.
As you have noticed that TRANSACTION 1 , first update the quantity value in table then rolled back the quantity, yet TRANSACTION 2 returns the correct quantity just because transactions running under isolation Level ‘READ COMMITTED’ , which prevent Dirty Reads.
So far, we have not encountered any DIRT READS, Lets take a scenario :
In any suituation, when multiple transactions hitting a single database at the same time and you do not want transactions to be queued to wait for its call as transaction has READ COMMITTED isolation level, which ensure that transactions to read data only if other transactions has been modified the data and committed itself.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ------- ANOTHER SESSION/ QUERY WINDOW SELECT @@SPID AS 'TRANSACTION 2' -----TRANSACTION 2 SELECT * FROM ITEM WHERE ITEM_ID =1
You can see, the output returned by TRANSACTION 2 is wrong quantity value beacuse TRANSACTION 1 updates the quantity then waited for 10 sec. While TRANASCTION 2 reads that updated value in that moment that is 14, When TRANSACTION 1 is rolled back after and reverted the quantity value to its original value that is 15.
So the quantity value reads by TRANSACTION 2 is DIRTY DATA.
As you have seen TRANSACTION isolation level Read uncommitted produce the Dirty read problems, and if we talk about the comparison of Read uncommitted isloation with other isolation that is provided by SQL Server then this is the ony one isolation level that has the dirty read problem.
So we can say, READ UNCOMMITTED allows concurrent transactions which reads same data that has yet not committed by other transaction and which produce dirty reads.