'TRANSACTION ISOLATION LEVEL is set to SNAPSHOT' error when using TransactionScope on Memory Optimised Table

Question

In Memory disk setup on SQL Server with below up

snapshot_isolation_state	snapshot_isolation_state_desc	is_read_committed_snapshot_on	is_memory_optimized_elevate_to_snapshot_on	is_memory_optimized_enabled
1	ON	1	1	1

Code to setup and complete transaction below

var transactionOptions = new TransactionOptions();
                    transactionOptions.IsolationLevel = IsolationLevel.Snapshot;

using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew, transactionOptions))
    {
        this.Service.Set(object);
        transaction.Complete();
    }

But on transaction.Complete() this error is thrown:

Microsoft.Data.SqlClient.SqlException (0x80131904): Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

To my understanding if is_memory_optimized_enabled and is_memory_optimized_elevate_to_snapshot_on are set to true I should be able to use IsolationLevel.Snapshot?

I set up the in memory Db, created the memory optimized table, then using TransactionScope in code to attempt to write to the Db.

I would preferably use SNAPSHOT as READCOMMITTED is having intermittent issues with the locking.

READCOMMITTED works with retry logic but would rather use SNAPSHOT.

Answer 1

If i understand it right, the problem is that you cannot specify both:

• MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: 1
• IsolationLevel: Snapshot

and the same time.

When you use MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, you continue to use ReadCommitted as normal.

This is admittedly a little confusing, but makes a bit more sense when you have transactions with both disk-based and in-memory objects.

Locking issue

For your locking issue:

I would preferably use SNAPSHOT - as READ COMMITTED is having intermittent issues with the locking.

The preferred solution there is to turn on:

READ_COMMITTED_SNAPSHOT = 1

This functions somewhat like "memory optimized elevate to snapshot":

• memory-optimized elevate to snapshot
• read-committed elevate to snapshot

It means that if you are running in READ_COMMITTED mode, you will get SNAPSHOT isolation enabled for free.

So now you have:

• memory optimized tables elevating to snapshot
• and readcommited transactions elevating to snapshot