Wednesday, September 2, 2015

SQL Server Isolation Levels with concurrent updates on nonclustered index

Testing under Read Committed Isolation Level

Session 1(PID 90):
begin transaction
update DimGeography set PostalCode='000001' where GeographyKey=1;

Session 2(PID 93): (Session will wait after executing)
update DimGeography set PostalCode='000002' where GeographyKey=1;
Session 1 (PID 90):
update DimGeography set PostalCode='000003' where GeographyKey=1;
Session 2 (PID 93):  (Session receive error)
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


image

 

Testing under Repeatable Read Isolation Level

Session 1 (PID 90):
set transaction isolation level repeatable read
go
begin transaction
update DimGeography set PostalCode='000001' where GeographyKey=1;

Session 2(PID 93): (Session will wait after executing)
update DimGeography set PostalCode='000002' where GeographyKey=1;
Session 1 (PID 90):
update DimGeography set PostalCode='000003' where GeographyKey=1;
Session 2 (PID 93): (Session will continue wait for lock)
Session 1 (PID 90):
commit;
Session 2 (PID 93): (returned with message “(1 row(s) affected)”)

Query the data from Session 1, the update performed by session 1 lost
image