Optimistic Isolation Levels - Pro SQL Server Internals - page 424

Listing 21-1. Using READCOMMITTED hint to prevent 3960 error
set transaction isolation level snapshot
begin tran
select count(*) from Delivery.Drivers
update Delivery.Orders with (readcommitted)
set Cancelled = 1
where OrderId = 10
rollback
SNAPSHOT isolation levels can change the behavior of the system. Let's assume there is a table dbo.Colors with
two rows: Black and White. The code that creates the table is shown in Listing 21-2.
Listing 21-2. SNAPSHOT isolation level update behavior: Table creation
create table dbo.Colors
(
Id int not null,
Color char(5) not null
)
go
insert into dbo.Colors(Id, Color) values(1,'Black'),(2,'White')
Now let's run two sessions simultaneously. In the first session, we run the update that sets the color to white for
the rows where the color is currently black. The code is shown in Listing 21-3.
Listing 21-3. SNAPSHOT isolation level update behavior: Session 1 code
begin tran
update dbo.Colors
set Color = 'White'
where Color = 'Black'
commit
In the second session, let's perform the opposite operation, as shown in Listing 21-4.
Listing 21-4. SNAPSHOT isolation level update behavior: Session 2 code
begin tran
update dbo.Colors
set Color = 'Black'
where Color = 'White'
commit
Let's run both sessions simultaneously in READ COMMITTED or any other pessimistic transaction isolation level.
In the first step, as shown in Figure 21-8 , we have the race condition. One of the sessions places exclusive (X) locks on
the row it updated while another session will be blocked trying to acquire an update (U) lock on the same row.