SQL SERVER InMemory OLTP: Understanding Optimistic Multi-Version Concurrency Concepts

InMemory is an awesome concept and I have been fortunate to learn about the same from my friends in the industry over blogs. It holds deep concepts and is sometimes tough to understand how the building blocks come together. I am not the only one doing the exploration, but a number of you also do and pass those learnings to me via your interesting questions. Thanks to each one of you who take a moment to ask me some of these questions that shake the fundamentals and make my understanding stronger. In the same lines, one of the readers wrote back to me after reading the MSDN on InMemory OLTP, what does the concepts of Optimistic multi-version concurrency really mean. On first thought, it looks simple, but the question was very loaded.

Optimistic multi-version concurrency

This blog is very much inspired by this question and I thought to pen this down as a blog with examples to show how SQL Server handles the same. We will look at various simple examples to illustrate the behavior for easy understanding.

Creating our basic DB and Table

I will do the basic structure from previous posts on what the template would be. This is a simple script to start:

USE [master] GO
-- Below statement works only from SQL Server 2016
-- Change this appropriately if you are running in prior versions
DROP DATABASE IF EXISTS  [InMem_OLTP] GO
CREATE DATABASE [InMem_OLTP] ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf', 
SIZE = 20480KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
FILEGROUP [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'InMem_OLTP_InMemory', FILENAME = N'C:\Data\InMem_OLTP_mopt', 
MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf', 
SIZE = 20480KB, FILEGROWTH = 10%)
GO

-- Creating the table for experimentations
USE [InMem_OLTP] GO
CREATE TABLE dbo.SalesOrder_inmem
(
order_id INT IDENTITY NOT NULL,
order_date DATETIME NOT NULL,
order_status tinyint NOT NULL,
amount FLOAT NOT NULL,
CONSTRAINT PK_SalesOrderID PRIMARY KEY NONCLUSTERED 
HASH (order_id) WITH (BUCKET_COUNT = 10000)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO
INSERT INTO dbo.SalesOrder_inmem VALUES
('1/1/2010',1,1000),
('1/1/2011',1,2000),
('1/1/2012',1,3000),
('1/1/2013',1,4000),
('1/1/2014',1,5000)

Understanding Optimistic part

Let me try to break the question into multiple parts and we will create the scenario that will showcase the optimistic part. The steps I am going to take has been explained in the table below:

As you can see above, this is done in two sessions windows and we will switch between sessions for the demo.

-- Session 1
BEGIN TRANSACTION
INSERT INTO dbo.SalesOrder_inmem VALUES
('1/1/2015',1,6000)

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

As you can see, we are still seeing only 5 records and the 6^th record for which the transaction is still open is not visible. This will be the state in our session 2 infinitely till the transaction in Session 1 finishes. You can see SQL Server has made a version and made sure our query on Session 2 is not getting blocked. Since we have taken an optimistic concurrency, the Session 2 is still able to query.

Let us Commit on Session 1 and do the query again on Session 2.

-- Session 1
COMMIT

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

Now you can see, without doing anything, the additional record now pops up automatically here now.

Understanding Multi-Versioning better

The multi-versioning is not clear yet and clearly. So to make that point, let me go ahead an update a record and see the different versions in our session.

Let us create the transaction to update the InMemory table. This is achieved as below:

-- Scenario 2
-- Session 1
BEGIN TRANSACTION
UPDATE dbo.SalesOrder_inmem WITH (SNAPSHOT)
SET amount = 10000
WHERE order_id = 6

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

The value of 10000 is NOT visible to the Session 2 till the transaction is committed on Session 1. This shows SQL Server’s capability to maintain multiple version of the records in an InMemory objects much more clearly.

Hope you were able to get a clearer understanding of how InMemory tables work in an optimistic concurrency model. Do let me know if you learnt something new or if I have missed anything that you would like to have more clarity on Optimistic Multi-Version Concurrency.

Reference: Pinal Dave (https://blog.sqlauthority.com)