Dirty Read is one of the most common problems that occurs 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.
Dirty Read does not occur 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 occurs 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 get 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 this in practical, 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 an artificial delay (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 is completed.
During the process TRANSACTION 1, first update the quantity value in table then rolled back the quantity, yet TRANSACTION 2 returns the correct quantity just because transactions is running under isolation Level ‘READ COMMITTED’, which prevent to Dirty Reads.
So far, we have not encountered with any DIRTY READS problems, Lets take a scenario to produce Dirty Reads :
lets assume the situation, 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 because TRANSACTION 1 updates the quantity then waited for 10 sec. While TRANASCTION 2 reads that updated value in the same moment that is 14, while TRANSACTION 1 is rolled back after 10 sec and reverted the quantity value to it’s original value that is 15.
So, the quantity value read 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 isolation with other isolation that is provided by SQL Server then this is the only one isolation level that has the dirty read problem.
So, the READ UNCOMMITTED allows concurrent transactions which reads same data that has yet not committed by other transaction and which produce dirty reads.
