Home » SQL Server Dirty Reads in concurrent transactions

SQL Server Dirty Reads in concurrent transactions

 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)
Now go to your query window and paste TRANSACTION 1 Script as given below.
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
Then open another query window and paste TRANSACTION 2 Script as given below.
------- ANOTHER SESSION/ QUERY WINDOW

SELECT @@SPID AS 'TRANSACTION 2' 

-----TRANSACTION 2

SELECT * FROM ITEM WHERE ITEM_ID =1
Now execute TRANSACTION 1  scripts then immidately  execute the TRANSACTION 2  scripts in query window,  you will notice that TRANSACTION 2 keeps executing for10 sec than returns the data as soon as TRANASCTION 1 finshes it’s execution.

 

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.

So the question comes why did TRANSACTION 2 have to wait for the completion of TRANSACTION 1?

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.




So to make transactions not to wait for its turn, you can change it isolation level to READ UNCOMMITTED. 
Now change your TRANSACTION 2 Scripts as given below.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

------- ANOTHER SESSION/ QUERY WINDOW

SELECT @@SPID AS 'TRANSACTION 2' 

-----TRANSACTION 2

SELECT * FROM ITEM WHERE ITEM_ID =1
After modifying the TRANSACTION 2 Scripts , execute TRANSACTION 1 then immidately execute the TRANSACTION 2 scripts in query window.
You will notice this time TRANSACTION 1 keeps executing until 10 sec while TRANSACTION 2 returns the data.

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.




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.