Cross-container transactions
A transaction is called a cross-container transaction if it:
Accesses a memory-optimized table from interpreted Transact-SQL; or
Executes a native proc when a transaction is already open (XACT_STATE() = 1).
The term "cross-container" derives from the fact that the transaction runs across the two transaction management containers, one for disk-based tables and one for memory-optimized tables.
Within a single cross-container transaction, different isolation levels can be used for accessing disk-based and memory-optimized tables. This difference is expressed through explicit table hints such as WITH (SERIALIZABLE) or through the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which implicitly elevates the isolation level for memory-optimized table to snapshot if the TRANSACTION ISOLATION LEVEL is configured as READ COMMITTED or READ UNCOMMITTED.
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-ver15#cross-container-transaction
Cross-container transactions are either implicit or explicit user transactions that include calls to natively-compiled stored procedures or operations on memory-optimized tables.
In SQL Server, calls to stored procedures do not initiate a transaction. Executions of natively compiled procedures in autocommit mode (not in the context of a user transaction) are not considered cross-container transactions.
Any interpreted query that references memory-optimized tables is considered a part of a cross-container transaction, whether executed from an explicit or implicit transaction or in auto-commit mode.
Each SQL Server transaction has an isolation level. The default isolation level is Read Committed. To use a different isolation level, you can set the isolation level using SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
To set a different isolation level for a set of statements in a transaction, you can use SET TRANSACTION ISOLATION LEVEL. The following example of a transaction uses the serializable isolation level as default. The insert and select operations on t3, t2, and t1 are executed under repeatable read isolation.
https://docs.microsoft.com/en-us/sql/database-engine/cross-container-transactions?view=sql-server-2014
Supported Isolation Levels for Cross-Container Transactions
There are limitations on the isolation levels used with operations on memory-optimized tables in cross-container transactions.
Memory-optimized tables support the isolation levels SNAPSHOT, REPEATABLE READ, and SERIALIZABLE. For autocommit transactions, memory-optimized tables support the isolation level READ COMMITTED.
The following scenarios are supported:
READ UNCOMMITTED, READ COMMITTED, and READ_COMMITTED_SNAPSHOT cross-container transactions can access memory-optimized tables under SNAPSHOT, REPEATABLE READ, and SERIALIZABLE isolation. The READ COMMITTED guarantee holds for the transaction; all rows read by the transaction have been committed to the database.
REPEATABLE READ and SERIALIZABLE transactions can access memory-optimized tables under SNAPSHOT isolation.
This can be summarized as follows,
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
This 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.
http://www.data-united.co.uk/disk-and-memory-optimized-tables-in-a-single-query-cross-container-transactions
for queries that only reference memory-optimized tables, we must use autocommit. But the rules are different for cross-container transactions, and in that case, we can use explicit transactions.
Example:
BEGIN TRANSACTION SELECT * FROM dbo.OnDiskT1 (READCOMMITTED) INNER JOIN dbo.InMemT1 WITH (SNAPSHOT)
ON InMemT1.pk = OnDiskT1.pk ROLLBACK
http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2
Example:
You are creating the following two stored procedures:
A natively-compiled stored procedure
An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions. You need to ensure that cross-container transactions are possible. Which setting or option should you use?
A. the SET TRANSACTION_READ_COMMITTED isolation level for the connection
B. the SERIALIZABLE table hint on disk-based tables
C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database
Answer C
Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit cross-container transactions.