So far we have saw following Isolation Levels and read phenomenon that exists with them
4. Serializable
With the above series of post we saw that each isolation has different read phenomenon, the above isolation levels exists in SQL Server 2000 and higher version. From SQL Server 2005 onwards we have another isolation level which is “SNAPSHOT ISOLATION LEVEL”.
Since in our last blog over Serializable we saw that, with Serializable Isolation Level we can prevent Dirty, Non-Repeatable and Phantom Reads. So what other things Snapshot can do ?
Let see the behavior of Snapshot isolation level.
Create Database From Following Script
IF Exists(SElect * From Sys.databases where name='DBCheckIsoLevel') Begin Drop Database DBCheckIsoLevel End Go Create Database DBCheckIsoLevel Go Use DBCheckIsoLevel Go
Before using Snapshot Isolation Level we need to allow database to use Snapshot isolation level.
Check whether database is allowed to use Snapshot Isolation level or Not,
Select snapshot_isolation_state_desc from sys.databases Where name='DBCheckIsoLevel'
Go
IF the Value returned by Above Query is “OFF” means Snapshot Isolation Is Not Allowed, If Returned “ON” Means Allowed.
Since Query Above Returned value “OFF” Then we need to allow snapshot isolation, Use following query to allow snapshot isolation on a database.
Alter Database DBCheckIsoLevel Set Allow_Snapshot_Isolation On Go
Check The Status Now
Select snapshot_isolation_state_desc from sys.databases Where name='DBCheckIsoLevel' Go
Should return “ON”
---Script To Create Test Table
Use DBCheckIsoLevel Go 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 |
Now, Open a Connection At Query Window 1
Query Window 1
Set Transaction Isolation Level Snapshot Begin Tran Update CheckIsolationLevel Set Name='Pankaj Arya' Where Id=1
--Note: The Transaction is Still Pending.
Query Window 2
Set Transaction Isolation Level Snapshot Select * From CheckIsolationLevel Where Id=1
As You Can See Below Record Is Returned At Query Window 2
Id | Name | EncodedDate |
1 | Pankaj | 2011-07-28 20:50:53.247 |
Surprisingly, We saw that transaction at query window 2 was not locked by Pending transaction at query window 1 ( which is working same as Read Uncommitted Isolation Level) but here the main thing is that it did not return the updated value rather it returned old value.
Lets Go To Query Window 1, We have one pending transaction at Query Window 1
Let Fire The Following Query At Query Window 1
Query Window 1
--Continue after pending transaction
Select * From CheckIsolationLevel Where Id=1
Id | Name | EncodedDate |
1 | Pankaj Arya | 2011-07-28 20:50:53.247 |
We noted that Query Window 1 result has been changed from “Pankaj” To “Pankaj Arya” But Similar Query At Query Window 2 Returned Old result. So now the question arises is where the changes of Query Window 1 are taking place ?.
So This is The Special Feature provided by snapshot isolation where modification or data inserts within snapshot isolation level creates row version in tempdb.
So It is clear in snapshot isolation level reads are not locked or blocked by writes.
But What about Write with Write ??
Now Fire The Following Query At Query Window 2 , Remember That Query Window 1 has a pending transaction.
Update CheckIsolationLevel Set Name='Pankaj Kumar' Where Id=1
--You will see that the above Query is blocked by Query at Query Window 1
So It is clear that Write with Write in Snapshot Isolation Level is Blocked.
Ok Let See Another Case Under Snapshot Isolation
Rollback Transaction At Query Window 1 and Run the Table Script to Re-Create and Fill Data.
Query Window 1
Set Transaction Isolation Level Snapshot Begin Tran Select * From CheckIsolationLevel Where Id=2
--Output ":
Id | Name | EncodedDate |
2 | Santosh | 2011-07-28 20:50:53.247 |
--Note Transaction is Still Pending
Query Window 2
Update CheckIsolationLevel Set Name='Satosh Panday' Where Id=2
--Note : Above Query Updates the Data. Also note that query runs without transaction and under Read_Committed Isolation Level.
Let Go Back to Query Window 1 where You have 1 pending transaction and run the following Query.
Update CheckIsolationLevel Set Name='Satosh Kumar Panday' Where Id=2
So you can see that above query at Query Window 1 raises error. Due to Update Conflict and rollback the entire transaction. These conflict error is a problem with snapshot isolation and one must handle there error while writing queries.
Hope Above Detail Might have given you basis info about Snapshot isolation.
No comments:
Post a Comment