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)
No comments:
Post a Comment