SERIALIZABLE Isolation level

 

In my blog about Read Phenomenon against each isolation level . we saw and understood what is Dirty, non repeatable, Phantom reads.

SERIALIZABLE ISOLATION : This Isolation Level is Same as Repeatable Read Isolation Level. The Main Difference Between SERIALIZABLE And REPEATABLE READ is that SERIALIZABLE Isolation Level Applies a Range Lock so that you cannot insert new rows or records within the range locked for other transaction which prevents Phantom Reads.

We also saw that Dirty, Non Repeatable and Phantom Read phenomenon doesn’t exists with SERIALIZABLE Isolation Level.

Ok. So Lets Prove Above

---Script To Create Test Table

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go

Id	Name	EncodedDate
1	Pankaj	2011-07-28 20:50:53.247
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

CASE -1

CASE – 1 ( Does SERIALIZABLE ISOLATION Prevents Dirty Read ?)

Query Window 1

Begin Transaction
Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

--Also Note That We Have Not Changed Isolation Level For Above Transaction So Update Is Taking Place Under Default Isolation Level (Read Committed).

 

Lets Open Another Connection

Query Window 2

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Select * From CheckIsolationLevel Where Id=1

 

As you see Query at Query Window 2 waits till Transaction at Query Window 1 Completes. So here while reading data or records which are in-flight or between transaction wait till transaction completes (which is preventing dirty read).

The Above Query At Query Window 2 Can be Written In Following Way Also.

Syntax 1 : Using “REPEATABLEREAD” Hint

Select * From CheckIsolationLevel (REPEATABLEREAD)  Where Id=1

 

Conclusion of CASE-1 (“YES”, SERIALIZABLE Isolatiton Prevents Dirty Read)

 

CASE –2

 

CASE – 2 (Does SERIALIZABLE Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Begin Transaction

Select * From CheckIsolationLevel Where Id = 1

--Note the transaction is still pending since no commit and rollback has been issued

Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=1

--And you noted that Above Query waits. The reason is that to prevent Non-Repeatable Read. Query at Query Window 1 Puts Shared Lock on the Key Records(If Table Is Cluster) and Share Lock on Table(If Table Is Heap) and shared lock prevents data from being modified by other transactions until shared lock is released at Query Window 1.

Commit Transaction

Let’s Go Back to Query Window 1

Query Window 1

--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As You see the Data Is Same As We Read Before With the Same Query, So It Clearly Shows That It Prevents No-Repeatable Read

Conclusion Of CASE –2 ( “YES” , SERIALIZABLE Isolation Prevent Non-Repeatable Read)

 

CASE – 3

CASE – 3 (Does SERIALIZABLE Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2

Begin Transaction

Insert Into CheckIsolationLevel Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

--So you see above insert wait as there is range lock acquired by Transaction at Query Window 1 and Query at Query Window 2 waits till lock is released by Query at Query Window 1.

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Following result is Retrieved

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

As You Can See The Above result is same as First Result (Preventing Phantom Read)

Conclusion Of CASE – 3 (“YES”,SERIALIZABLE Isolation Level Prevents Phantom Read)