Transaction Isolation Levels
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
The following isolation levels are supported for transactions that access memory-optimized tables.
The transaction isolation level can be specified as part of the atomic block of a natively compiled stored procedure. For more information, see CREATE PROCEDURE (Transact-SQL). When accessing memory-optimized tables from interpreted Transact-SQL, the isolation level can be specified using table-level hints.
You must specify the transaction isolation level when you define a natively compiled stored procedure. You must specify the isolation level in table hints when accessing memory-optimized tables from user transactions in interpreted Transact-SQL.
The isolation level READ COMMITTED is supported for memory-optimized tables with autocommit transactions. READ COMMITTED is not valid in user transactions or in an atomic block. READ COMMITTED is not supported with explicit or implicit user transactions. Isolation level READ_COMMITTED_SNAPSHOT is supported for memory-optimized tables with autocommit transactions and only if the query does not access any disk-based tables. In addition, transactions that are started using interpreted Transact-SQL with SNAPSHOT isolation cannot access memory-optimized tables. Transactions that are use interpreted Transact-SQL with either REPEATABLE READ or SERIALIZABLE isolation must access memory-optimized tables using SNAPSHOT isolation. For more information about this scenario, see Cross Container Transactions.
READ COMMITTED is the default isolation level in SQL Server. When the isolation level of the session is READ COMMITED (or lower), you can do one of the following:
Explicitly use a higher isolation level hint for accessing the memory-optimized table (for example, WITH (SNAPSHOT)).
Specify the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT set option, which will set the isolation level for memory-optimized tables to SNAPSHOT (as if you included WITH(SNAPSHOT) hints to every memory-optimized table). For more information about MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, see ALTER DATABASE SET Options (Transact-SQL).
Alternatively, if the isolation level of the session is READ COMMITTED, you can use autocommit transactions.
SNAPSHOT transactions started in interpreted Transact-SQL cannot access memory-optimized tables.
The transaction isolation levels supported for memory-optimized tables provide the same logical guarantees as disk-based tables. The mechanism used for providing isolation level guarantees is different.
For disk-based tables, most isolation level guarantees are implemented using locking, which prevent conflicts through blocking. For memory-optimized tables, the guarantees are enforced using a conflict detection mechanism, which avoids the need to take locks. The exception is SNAPSHOT isolation on disk-based tables. This is implemented similarly to SNAPSHOT isolation on memory-optimized tables using a conflict detection mechanism.