Database Reference
In-Depth Information
Table 32-4.
Concurrency in the
SNAPSHOT
transaction isolation level
Session 1
Session 2
Results
begin tran
select ID, Col
from dbo.HKData
with (snapshot)
update dbo.HKData
set Col = -2
where ID = 2
select ID, Col
from dbo.HKData
with (snapshot)
Return old version of a row (Col = 2)
commit
Success
begin tran
select ID, Col
from dbo.HKData
with (snapshot)
insert into dbo.HKData
values(10,10)
select ID, Col
from dbo.HKData
with (snapshot)
Does not return new row (10,10)
Commit
Success
The
SNAPSHOT
isolation level works in a similar manner to on-disk tables, and it protects from
Non-Repeatable
Reads
and
Phantom Reads
phenomena. As you can guess, it does not need to perform repeatable read and serializable
validations at the commit stage and, therefore, it reduces the load on SQL Server.
Write/write conflicts work the same way regardless of the transaction isolation level in in-memory OLTP.
SQL Server does not allow a transaction to modify rows that have been modified by other uncommitted transactions.
Table
32-5
illustrates this behavior. It uses the
SNAPSHOT
isolation level, however the behavior does not change with
different isolation levels.
