SQL Server in-memory oltp transaction snapshot isolation

Question

Trying to understand how transaction isolation levels work on SQL Server memory optimized tables (in-memory oltp).

If I execute the following query:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks

An error message is displayed:

Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

Now if I modify query by adding table hint, it works:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks WITH(SNAPSHOT)

But if I change transaction isolation level via SET TRANSACTION ISOLATION LEVEL SNAPSHOT and remove table hint:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks

it does not work again displaying error message:

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

Why does it work with the table hint, and setting transaction isolation level via

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

does not?

Update: Tried setting MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON, still getting the same error from last query:

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

You need to use `MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT` in addition to *not* using `SET TRANSACTION ISOLATION LEVEL SNAPSHOT` explicitly. In other words, use `SET TRANSACTION ISOLATION LEVEL READ COMMITTED` in conjunction with this option (and/or `READ_COMMITTED_SNAPSHOT` if you want the same for disk-based objects). This is admittedly a little confusing, but makes a bit more sense when you have transactions with both disk-based and in-memory objects. — Jeroen Mostert, Mar 03 '19 at 11:40
@JeroenMostert As I know READ_COMMITTED_SNAPSHOT(pessimistic writes) is not the same as SNAPSHOT (optimistic writes). So if I need transaction with both disk and memory optimized tables involved, is it impossible to use SNAPSHOT isolation level ? — gio, Mar 03 '19 at 18:35
`READ_COMMITTED_SNAPSHOT` means nothing more and nothing less than that `READ COMMITTED` is treated as if it were `SNAPSHOT`. There is no difference in terms of optimism vs. pessimism; snapshot isolation is always optimistic, in that writes will fail if data was modified since the transaction was started. If you want lock-based `READ COMMITTED` semantics ("pessimistic") with snapshot isolation in effect, you can use the `READCOMMITTEDLOCK` table hint. Explicit transactions where you combine memory and disk tables are a little tricky, but they should not be very common. — Jeroen Mostert, Mar 03 '19 at 18:45

score 6 · Answer 1 · answered Mar 24 '19 at 03:07

If you really want to understand supported isolation levels for In-Memory OLTP when referencing both traditional and memory-optimized tables, you must also understand transaction initiation modes.

All of this is detailed in my posts:

http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-1/ http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2/

Please let me know if you have further questions about this often misunderstood topic.

score 4 · Answer 2 · answered Mar 03 '19 at 11:23

The MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT database option must be on in order to use an explict SNAPSHOT transaction without hints. Below is an excerpt from the documentation (emphasis mine):

Explicit - Your Transact-SQL contains the code BEGIN TRANSACTION, along with an eventual COMMIT TRANSACTION. Two or more statements can be corralled into the same transaction. In explicit mode, you must either use the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.

Below is an example that shows how to turn on this database option:

ALTER DATABASE YourDatabase
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

SQL Server in-memory oltp transaction snapshot isolation

2 Answers2