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

The SNAPSHOT isolation level uses a row-versioning model where all data modifications done by other transactions
are invisible to the transaction. Though it is implemented differently in on-disk than in memory-optimized tables,
logically it behaves the same. Aborting and rolling back the transactions resolves write/write conflicts in this model.

■ While SERIALIZABLE and SNAPSHOT isolation levels provide the same level of protection against data
inconsistency issues, there is a subtle difference in their behavior. a SNAPSHOT isolation level transaction sees data as of
the beginning of a transaction. With the SERIALIZABLE isolation level, the transaction sees data as of the time when the
data was accessed for the first time. Consider the situation when a session is reading data from a table in the middle of
a transaction. If another session changed the data in that table after the transaction started but before data was read, the
transaction in the SERIALIZABLE isolation level would see the changes while the SNAPSHOT transaction would not.

Note

In-Memory OLTP Transaction Isolation Levels
In-memory OLTP supports three transaction isolation levels: SNAPSHOT , REPEATABLE READ , and SERIALIZABLE .
However, in-memory OLTP uses a completely different approach to enforce data consistency rules as compared to
on-disk tables. Rather than block or being blocked by other sessions, in-memory OLTP validates data consistency at
the transaction COMMIT time and throws an exception and rolling back the transaction if rules were violated.
Let's look at a few examples that demonstrate this behavior. As a first step, shown in Listing 32-6, let's create a
memory-optimized table and insert a few rows there.

Listing 32-6. Data consistency and transaction isolation levels: Table creation

create table dbo.HKData
(
ID int not null,
Col int not null,
constraint PK_HKData
primary key nonclustered hash(ID)
with (bucket_count=64),
)
with (memory_optimized=on, durability=schema_and_data)
go

insert into dbo.HKData(ID, Col)
values(1,1),(2,2),(3,3),(4,4),(5,5);

Table 32-2 shows how concurrency works in the REPEATABLE READ transaction isolation level. The Session 1
transaction starts at the time when the first SELECT operator executes. Remember that SQL Server starts a transaction
at the moment of first data access rather than at the time of the BEGIN TRAN statement.