Error 41368: Memory-Optimized Tables and Transactions

Error 41368 stops an explicit transaction that reads a memory-optimized table at the read committed level. It appears when one transaction touches a memory-optimized table and a disk-based table. The cause is a rule of the memory-optimized engine, and two short fixes remove it.

Build a Memory-Optimized Table and a Disk Table

The demo needs a database with a memory-optimized filegroup. The first script builds it from the instance default path, so it runs on a default install. The second script creates one table of each kind and fills them with a few garden products.

USE master;
GO
IF DB_ID(N'MemoryOptTxnDemo') IS NULL
BEGIN
    DECLARE @path nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
    DECLARE @sql nvarchar(max) =
        N'CREATE DATABASE MemoryOptTxnDemo ON PRIMARY (NAME = MemoryOptTxnDemo_data, FILENAME = N''' + @path + N'MemoryOptTxnDemo.mdf''), '
      + N'FILEGROUP MemData CONTAINS MEMORY_OPTIMIZED_DATA (NAME = MemoryOptTxnDemo_mod, FILENAME = N''' + @path + N'MemoryOptTxnDemo_mod'') '
      + N'LOG ON (NAME = MemoryOptTxnDemo_log, FILENAME = N''' + @path + N'MemoryOptTxnDemo_log.ldf'');';
    EXEC (@sql);
END;

USE MemoryOptTxnDemo;
GO
DROP TABLE IF EXISTS dbo.PriceCache;
DROP TABLE IF EXISTS dbo.Products;
CREATE TABLE dbo.PriceCache (
    ProductID int NOT NULL PRIMARY KEY NONCLUSTERED,
    Price decimal(8,2) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
CREATE TABLE dbo.Products (
    ProductID int NOT NULL PRIMARY KEY,
    ProductName nvarchar(40) NOT NULL
);
INSERT INTO dbo.Products VALUES (1, N'Basil seeds'), (2, N'Garden gloves'), (3, N'Clay pot');
INSERT INTO dbo.PriceCache VALUES (1, 3.50), (2, 12.00), (3, 6.25);

Reproduce Error 41368

A single statement that joins the two tables works. SQL Server runs it as its own transaction, and an autocommit statement is the case the engine allows.

SELECT p.ProductName, c.Price
FROM dbo.Products AS p
JOIN dbo.PriceCache AS c ON c.ProductID = p.ProductID;

Now run the same join inside an explicit transaction. The default isolation level is read committed, and the memory-optimized engine does not support that level there.

BEGIN TRANSACTION;
SELECT p.ProductName, c.Price
FROM dbo.Products AS p
JOIN dbo.PriceCache AS c ON c.ProductID = p.ProductID;
COMMIT TRANSACTION;

The message ends with the fix: provide a supported isolation level using a table hint, such as WITH (SNAPSHOT). The error also ends the transaction, so no COMMIT follows it. The transaction count is back at 0 when the batch stops. A COMMIT in a later batch answers with Msg 3902, because no transaction is left.

Error 41368 is not about the join. The join only brings both kinds of table into one transaction. SET IMPLICIT_TRANSACTIONS ON starts a transaction by itself, so it meets the same error, as the message says.

Why the Rule Exists

Memory-optimized tables do not take locks. They keep row versions and check for conflicts when the transaction commits. Read committed, as a disk table knows it, depends on locks. The engine cannot give that behavior here, so it asks for a level it can honor. Snapshot is that level.

Fix It With a Table Hint

The hint asks for snapshot isolation on one table. Put it on the memory-optimized table and nothing else.

BEGIN TRANSACTION;
SELECT p.ProductName, c.Price
FROM dbo.Products AS p
JOIN dbo.PriceCache AS c WITH (SNAPSHOT) ON c.ProductID = p.ProductID;
COMMIT TRANSACTION;

The query returns the three rows. Writes follow the same rule. An UPDATE of the memory-optimized table needs the hint inside a transaction. Without it, the UPDATE fails with the same error.

Fix It With a Database Option

The hint is exact, and it must appear in every statement. The database option covers all of them at once. It tells SQL Server to read memory-optimized tables at snapshot isolation whenever a transaction asks for a lower level.

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Run the unhinted transaction again. With the option on, Error 41368 no longer appears and the transaction succeeds. To check the setting, read it as shown in MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: How to Check It Is On. The undo is the same statement with OFF.

BEGIN TRANSACTION;
SELECT p.ProductName, c.Price
FROM dbo.Products AS p
JOIN dbo.PriceCache AS c ON c.ProductID = p.ProductID;
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
COMMIT TRANSACTION;

The second result is 2, which is read committed. The option did not change your session. It changed how the memory-optimized table is read. That also answers the question of other objects. A disk-based table keeps its normal behavior. Every transaction in this database now reads memory-optimized tables at snapshot isolation when it asks for a lower level. Check any code that depends on read committed behavior.

Two Traps the Fixes Do Not Cure

A session level setting is not the same as a table hint. Run SET TRANSACTION ISOLATION LEVEL SNAPSHOT, then touch the memory-optimized table in a transaction. You get Msg 41332, with the option on or off. The engine wants the snapshot request on the table, and it refuses the session level.

The option also does not rescue stronger levels. A session at REPEATABLE READ or SERIALIZABLE fails with Msg 41333 even when the option is on. A REPEATABLE READ session that puts WITH (SNAPSHOT) on the memory-optimized table runs. Otherwise those levels need a different design.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT Price FROM dbo.PriceCache;
COMMIT TRANSACTION;
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT Price FROM dbo.PriceCache;
COMMIT TRANSACTION;
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

You could argue that the option hides a design problem. A transaction that mixes both kinds of table now behaves differently from what its isolation level says. That is a fair point. The snapshot read is consistent for the whole transaction. It is not the read committed behavior you wrote the query for. Test any query that depends on seeing fresh changes.

What to Remember

Error 41368 means a read committed transaction touched a memory-optimized table. The fix is a snapshot request, not a different query. Add WITH (SNAPSHOT) to that table for a precise fix, or set the database option for all statements. Read the session level from sys.dm_exec_sessions, and expect 2.

Do not use the session level SNAPSHOT as a fix. Do not expect the option to help REPEATABLE READ or SERIALIZABLE. Test the change on a copy, then run the cleanup script.

USE master;
GO
IF DB_ID(N'MemoryOptTxnDemo') IS NOT NULL
BEGIN
    ALTER DATABASE MemoryOptTxnDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE MemoryOptTxnDemo;
END;

A memory-optimized table is not a faster copy of a disk table, it is a table with its own rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.