Database Reference
In-Depth Information
Table 32-2.
Concurrency in the
REPEATABLE READ
transaction isolation level
Session 1
Session 2
Results
begin tran
select ID, Col
from dbo.HKData
with (repeatableread)
update dbo.HKData
set Col = -2
where ID = 2
select ID, Col
from dbo.HKData
with (repeatableread)
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 (repeatableread)
insert into dbo.HKData
values(10,10)
select ID, Col
from dbo.HKData
with (repeatableread)
Does not return new row (10,10)
commit
Success
As you can see, with memory-optimized tables, other sessions were able to modify data that was read by the
active
REPEATABLE READ
transaction. This led to a transaction abort at the time of
COMMIT
. This is a completely
different behavior than that of on-disk tables, where other sessions would be blocked until the
REPEATABLE READ
transaction successfully commits.
It is also worth noting that in the case of memory-optimized tables, the
REPEATABLE READ
isolation level protects
you from the
Phantom Read
phenomenon, which is not the case with on-disk tables.
As a next step, let's repeat these tests in the
SERIALIZABLE
isolation level. You can see the code and the results of
the execution in Table
32-3
.
