In-Memory OLTP Internals - Pro SQL Server Internals - page 655

Table 32-3. Concurrency in the SERIALIZABLE transaction isolation level

Session 1 Session 2 Results

begin tran
select ID, Col
from dbo.HKData
with (serializable)

update dbo.HKData
set Col = -2
where ID = 2

select ID, Col
from dbo.HKData
with (serializable)

Return old version of a row (Col = 2)

commit Msg 41305, Level 16, State 0, Line 0

The current transaction failed to commit due
to a repeatable read validation failure.

begin tran
select ID, Col
from dbo.HKData
with (serializable)

insert into dbo.HKData
values(10,10)

select ID, Col
from dbo.HKData
with (serializable)

Does not return new row (10,10)

commit Msg 41325, Level 16, State 0, Line 0

The current transaction failed to commit due
to a serializable validation failure.

As you can see, the SERIALIZABLE isolation level prevents the session from committing a transaction when
another session inserted a new row and violated the serializable validation. Like the REPEATABLE READ isolation level,
this behavior is different from that of on-disk tables, where the SERIALIZABLE transaction would successfully commit
blocking other sessions until it is done.
Finally, let's repeat the tests in the SNAPSHOT isolation level. The code and results are shown in Table 32-4 .