Working with Concurrency Problems – (Dirty, Non Repeatable & Phantom Reads)

Dirty Reads:

The transaction which is not yet committed and read by the second transaction is called Dirty Read.

This happens when the transaction isolation level is set to Read Uncommitted.

Since the first transaction is not yet committed it may update the record, but the second transaction reads it.

The Images below tells you exactly what is happening.

1) The Transaction has started

2) The Session 1 is updating a record of ID = 2 with a time delay of 8, mean while session2 read the

Uncommitted data

3) The Transaction Ended

USE Rohan

GO

IF OBJECT_ID('dbo.Movies') IS NOT NULL

DROP TABLE dbo.Movies

GO

CREATE TABLE Movies(ID Int,Name Varchar(20),Genre Varchar(20))

INSERT INTO dbo.Movies (ID,Name,Genre)

VALUES(1,'Shark','Documentery'),(2,'WildAnimal','Thriller')

Session 1 Image:

Session2 Image :

Non Repeatable Reads:

This happens when the transaction isolation level is set to Read Committed or less than that (Read Uncommitted).

This happens when session1 retrieves a row and session2 updates the same row and when session reruns the same row the results will be different.

USE Rohan

GO

IF OBJECT_ID('dbo.Movies') IS NOT NULL

DROP TABLE dbo.Movies

GO

CREATE TABLE Movies(ID Int,Name Varchar(20),Genre Varchar(20))

INSERT INTO dbo.Movies (ID,Name,Genre)

VALUES(1,'Shark','Documentery'),(2,'WildAnimal','Thriller')

Session1:

Session 2:

Session 1:

Observe the Second result different than the first result, which shows clearly Non Repeatable Reads

Phantom Reads:

This happens when the transaction isolation level is set to Repeatable Read or less than that (Read Uncommitted or Read Committed).

This is Similar to Non Repeatable reads with a slight change, the change is that Session1 has selected some range of rows

Ex: Where Id in (1, 2) and in the given range if there is any update by the session2 then it’s called as phantom reads.

USE Rohan

GO

IF OBJECT_ID('dbo.Movies') IS NOT NULL

DROP TABLE dbo.Movies

GO

CREATE TABLE Movies (ID Int, Name Varchar(20),Genre Varchar(20))

INSERT INTO dbo.Movies (ID,Name,Genre)

VALUES(1,'Shark','Documentery'),(2,'WildAnimal','Thriller')

Session 1:

Session2:

Session1:

If we see the first result contains only two rows, whereas the second result contains three results