Sql-server – Accessing Memory Optimised Tables Under Snapshot Isolation

isolation-levelmemory-optimized-tablessql-server-2016t-sql

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).

Best Answer

If you intend to be able to update/insert through the view, it will likely not work. Please see my post here:

http://nedotter.com/archive/2017/07/dml-for-memory-optimized-tables-in-partitioned-views/

Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either cause a stack dump or give an error saying it's unsupported.

Though Microsoft has marketed memory-optimized tables as a hot/cold solution, it can't be done in most cases, at least not without tons of manual intervention. There is no way to validate a FK across the traditional/memory-optimized divide. See my post here:

http://nedotter.com/archive/2016/05/in-memory-oltp-relationship-status-its-complicated/

I've written a few posts on memory-optimized tables and isolation levels here:

http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-1/

http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2/

Have you proven that the performance issues you are experiencing would be solved by In-Memory OLTP? The use cases are quite narrow.

Related Solutions

Best Isolation Level for Non-Blocking Select Statements in SQL Server

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.

This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Best Answer

Related Solutions

Best Isolation Level for Non-Blocking Select Statements in SQL Server

Postgresql – Read Committed Isolation Level

Related Question