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.

 

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