SQL Server : SQL Server Dirty Read Concurrency Problem

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server Dirty Read Concurrency Problem

SQL Server Dirty Read is a part of Concurrency problem, it occurs when one transaction is allowed to read the uncommitted data.

When we set Transactions Isolation Level Read Uncommitted, then they allows the Dirty reads.

 

Suppose two transactions are running simultaneously, First transaction update a row and meanwhile Second transaction reads the updated row before First transaction commits the update.

That means, if First transaction rolls back the change, Second transaction still read the old change that is called Dirty Read.

Note:- Concurrency problem is a situation where two or more users accessing same data at same time and while they are accessing the same data, we do not want any kind of inconsistency result or abnormal behavior.

What is Read Committed & Read Uncommitted Isolation Level?

1. Read Committed Isolation Level: (Not allows dirty read)

By default Isolation Level between transactions are ‘Read Committed’, that’s mean Second transaction will wait till first transaction not completed. There is no need to set Isolation Level Read Committed before any transactions.

Syntax:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Example:

Suppose we have one sample table with two columns

Scenario:

We will use two transactions in different-different SQL query windows, In first transaction we will update row and wait till 7 second then we will rollback the change, meanwhile we will execute second transaction in next query window.


Now, create first transaction, here we will update First Name of customer on the basis of Customer Key.

First Transaction:-

BEGIN TRANSACTION;
UPDATE DimCustomer 
SET FirstName = 'James'
WHERE CustomerKey=11000;
WAITFOR DELAY '00:00:07.000';
ROLLBACK TRANSACTION;

Update Transaction with Roll back

In second transaction, we will select same row on the basis of customer key

Second Transaction:-

SELECT FirstName, LastName
FROM DimCustomer
WHERE CustomerKey=11000;

Output:

When you will execute both transaction together then you will see second transaction will not return result till first transaction not completed. And after that it will return correct result.

2. Read Uncommitted Isolation Level: (Allows dirty reads)

When you allows Read uncommitted then second transaction does not care first transaction is completed or not, and it returns dirty reads to users.

For this we will use same example but in second transaction we will set Isolation level ‘Read uncommitted’ and first transaction will be same.

Syntax:

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

Second Transaction:-

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT FirstName, LastName
FROM DimCustomer
WHERE CustomerKey=11000;

Now execute both transaction together, it return first name as ‘James’ but it should be ‘Jon’.

It happens because we set isolation level read uncommitted and it allows not committed data, that is called dirty read.

Output:

Read Uncommitted Isolation Level

Where to use Read Committed & Read Uncommitted Isolation Level?

• Performance wise Read Uncommitted is fast because it does not wait for any running transactions and return result very fast and in other side Read Committed is slow because it does not allow other transaction till first not completed.
• Online shopping portal is a good example of Read Committed, because we cannot show users wrong data. And if you want to analyze some data for internal purpose and your data is very huge then you can use Read Uncommitted.