Oct 16

Disk and memory-optimized tables in a single query: cross-container transactions

When you set up a database for  memory-optimized tables in SQL Server 2014 it is recommended that you enable SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT setting:

ALTER DATABASE IMOLTP_DB SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

Memory x DiskThis setting is important when using transactions involving in-memory tables and disk-based tables, the so-called “cross-container transactions”. As you may know, SQL Server default transaction isolation level is normally READ COMMITTED. The above setting will elevate the isolation level for memory-optimized tables to SNAPSHOT for every cross-container transaction with lower isolation level (such as default, read committed). This setting is equivalent to including WITH(SNAPSHOT) hints to every DML operation involving memory-optimized table in cross-container transaction.

For more information http://msdn.microsoft.com/en-us/library/dn133175.aspx

If this setting is not specified, you may see the following message returned when attempting a cross-container transaction:

Msg 41368, Level 16, State 0, Line 31
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).

So one way of dealing with this message is to use WITH (SNAPSHOT) hint next to every memory-optimised table involved in the DML statement, just as advised by the error message:

BEGIN TRANSACTION
UPDATE dt
SET data = mot.data
FROM MemoryOptimisedTable mot WITH (SNAPSHOT) 
   INNER JOIN DiskTable dt on mot.id = dt.id

DELETE FROM MemoryOptimisedTable WITH (SNAPSHOT)
COMMIT

However, if you expect to use cross-container transactions often, a more convenient way is to specify a database option SETMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

If SERIALIZABLE or REPEATABLE READ transaction isolation level is used, the error message is somewhat different:

Msg 41333, Level 16, State 1, Line 29
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Here, the isolation level is higher than SNAPSHOT, so the elevation setting is not going to help, and you must use WITH (SNAPSHOT) hints:

IF @@TRANCOUNT > 0 ROLLBACK

IF OBJECT_ID('dbo.MemoryOptimisedTable') IS NOT NULL DROP TABLE dbo.MemoryOptimisedTable

CREATE TABLE dbo.MemoryOptimisedTable (
id int NOT NULL PRIMARY KEY NONCLUSTERED,
im_data int) with (MEMORY_OPTIMIZED = ON)
IF OBJECT_ID('dbo.DiskTable') IS NOT NULL DROP TABLE dbo.DiskTable
CREATE TABLE dbo.DiskTable
(id int NOT NULL PRIMARY KEY CLUSTERED,
dt_data int)
GO
INSERT INTO MemoryOptimisedTable (id, im_data)
values (1,3876),(2,6576),(3,3345)
INSERT INTO DiskTable (id, dt_data)
values (1,45756),(2,1231),(3,3432)
GO
SELECT * FROM MemoryOptimisedTable mot INNER JOIN DiskTable dt on mot.id = dt.id
BEGIN TRANSACTION
UPDATE dt
SET dt_data = mot.im_data
FROM MemoryOptimisedTable mot INNER JOIN DiskTable dt on mot.id = dt.id
DELETE FROM MemoryOptimisedTable
COMMIT