SQL SERVER – Memory Optimized Tables, Transactions, Isolation Level and Error

Today we will see an interesting story about Memory Optimized Tables, Transactions, Isolation Level and Error. While I was working with my customer with Comprehensive Database Performance Health Check, we came across a very interesting situation.

My customer was a financial institute and they had very high transactions every single second. We had successfully implemented In-Memory OLTP technology for them. There were situations where we have to use the Memory-Optimized tables and disk-based regular tables in the single transactions.

Every time whenever we ran query which was accessing memory-optimized table and disk-based table, we were getting an error and my client did not want any error.

Here is the script when I ran it gave us an error:

BEGIN TRANSACTION
SELECT *
FROM [dbo].[MemoryOptimizedTable] mt
INNER JOIN [dbo].[DiskBasedTable] dt ON mt.ID = dt.ID
GO
COMMIT TRANSACTION
GO

Please note in the above script one table is memory optimized and another one is disk based table. Whenever I ran the script it gave me following error.

Msg 41368, Level 16, State 0, Line 3
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).
Msg 3902, Level 16, State 1, Line 8
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

The reason for the error is that in SQL Server In-Memory OLTP whenever you try to access any disk-based and memory optimized table in the single transactions, it will give you an error as that is not directly supported unless you change the isolation of the query or database.

WORKAROUND/SOLUTION for Memory Optimized Tables

Here is the simple solution of the above error.

When you change the isolation level for the database to MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT the error will go away and your SQL Server will support implicit or explicit transactions cross-container (across disk and memory)

-- for Cross Container Transactions
ALTER DATABASE CURRENT 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO

Well, that’s it!

Reference: Pinal Dave (https://blog.SQLAuthority.com)