Database Reference
In-Depth Information
Table 32-6.
Isolation levels for cross-container transactions
Isolation levels for on-disk tables
Isolation levels for memory-optimized tables
READ UNCOMMITTED
,
READ COMMITTED
,
READ COMMITTED SNAPSHOT
SNAPSHOT
,
REPEATABLE READ
,
SERIALIZABLE
REPEATABLE READ
,
SERIALIZABLE
SNAPSHOT
only
SNAPSHOT
Not supported
Internal implementations of
REPEATABLE READ
and
SERIALIZABLE
isolation levels are very different for on-disk
and memory-optimized tables. In cross-container transactions, SQL Server only supports
SNAPSHOT
isolation levels
for memory-optimized tables when on-disk tables require such isolation levels. Moreover, SQL Server does not
allow access to memory-optimized tables when on-disk tables require
SNAPSHOT
isolation. Such isolation levels
guarantee the state of the data as of the moment when the transaction starts, which is impossible to guarantee with
cross-container transactions when, in a nutshell, there are two internal transactions: one for on-disk and another for
memory-optimized tables. It is impossible to start both transactions at exactly the same time.
As you may have already noticed, SQL Server requires you to specify the transaction isolation level with a
table hint when you are accessing memory-optimized tables. This does not apply to individual statements that
execute outside of the explicitly started (with
BEGIN TRAN
) transaction. Those statements are called
autocommitted
transactions,
and each of them executes in a separate transaction that is active for the duration of the statement
execution. Listing 32-7 illustrates code with three statements. Each of them will run in their own autocommitted
transactions.
Listing 32-7.
Autocommitted transactions
delete from dbo.HKData;
insert into dbo.HKData(ID, Col)
values(1,1),(2,2),(3,3),(4,4),(5,5);
select ID, Col
from dbo.HKData;
An isolation level hint is not required for statements running in autocommitted transactions. When the hint is
omitted, the statement runs in
SNAPSHOT
isolation level.
■
SQL Server allows you to keep a
NOLOCK
hint while accessing memory-optimized tables from autocommitted
transactions. that hint is ignored. a
READUNCOMMITTED
hint, however, is not supported and triggers an error.
Note
The
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT
database option is disabled by default. When this option is
enabled, SQL Server allows you to omit the isolation level hint in non-autocommitted transactions. SQL Server uses
the
SNAPSHOT
isolation level, as with autocommitted transactions, if the isolation level hint is not specified. Consider
enabling this option when you port an existing system to in-memory OLTP and have T-SQL code that accesses tables
that become memory-optimized.
