Serializable Isolation Level in SQL Server with example

Serializable 

Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause. If table doesn't have index then it locks complete table.

 IF OBJECT_ID('Product') is not null

    begin

    DROP TABLE Product

    end

create table Product

(ProdID int,

ProdName Varchar(50),

ProdPrice int

)

 insert into Product(ProdID,ProdName,ProdPrice)

values

( 1,'Black Shirt',1000),

( 2,'White Suits',2000),

( 3,'Red Kurta',700),

( 4,'Half Shirt',1700)

Run both the sessions side by side

 Example 1:

Product table does not have any index. SO it will lock whole records

 Session 1:

set transaction isolation level serializable

begin tran

select * from Product

waitfor delay '00:00:10'

select * from Product

rollback

 Session 2:

 insert into Product(ProdID,ProdName,ProdPrice)

values ( 6,'Blue Pant',1400)

 Output from Session 1:

Since table does not have any index session 1 lock whole table. So session 2 waited till session 1 completed transaction. So it prevents from phantom read.

 Example 2:

 Product table is having index on ProdID. SO it will lock only selected range of records

Session 1:

set transaction isolation level serializable

begin tran

select * from Product where ProdID in(1,4)

waitfor delay '00:00:10'

select * from Product where ProdID in(1,4)

rollback

 Session 2:

 insert into Product(ProdID,ProdName,ProdPrice)

values ( 7,'Grey Kurta',1200)

 Now session 2 will execute without any delay. Since session locked the records from ID 1 to 4.

Click on Below links to see the Different types of isolation levels with example

• Read UnCommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot