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.

Answer 1

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.