We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains.
Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view unioning both together.
Create Table Demo_Cold ( [ID] INT NOT NULL PRIMARY KEY, [Description] VARCHAR(MAX) NOT NULL ); GO Create Table Demo_Hot ( [ID] INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED, [Description] VARCHAR(MAX) NOT NULL ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ); GO CREATE VIEW Demo AS Select * From Demo_Hot UNION ALL Select * From Demo_Cold; GO
When I attempt to query this (either directly or view the view) everything is fine:
Select * From Demo
But when I try to set the isolation level to snapshot, I get an error instead:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT Select * From Demo
Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.
We use snapshot isolation in this database at the moment to allow unrelated parallel commits, and fail on concurrent commits on the same data. According to the MS Documentation, Snapshot isolation is supported by IM-OLTP.
I've even tried setting the escalation setting on to allow this:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
But it doesn't change the error.
If I set the isolation level to
REPEATABLE READ, I get another error:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Select * From Demo
The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
The same for accessing just the memory-optimised table:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Select * From Demo_Hot
Am I missing a key step required? Ultimately I'd like to be able to continue using Snapshot isolation from our code (to minimise any other changes).