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:

StepsSession 1Session 2
1BEGIN TRANSACTION
2INSERT INTO InMemory Table
3Read from InMemory Table
4COMMIT TRANSACTION
5Read from InMemory Table

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

SQL SERVER InMemory OLTP: Understanding Optimistic Multi-Version Concurrency Concepts inmemory-optimistic-concurrency-01

As you can see, we are still seeing only 5 records and the 6th 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

SQL SERVER InMemory OLTP: Understanding Optimistic Multi-Version Concurrency Concepts inmemory-optimistic-concurrency-02

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.

StepsSession 1Session 2
1BEGIN TRANSACTION
2UPDATE InMemory Table
3Read from InMemory Table
4COMMIT TRANSACTION
5Read from InMemory Table

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

SQL SERVER InMemory OLTP: Understanding Optimistic Multi-Version Concurrency Concepts inmemory-optimistic-concurrency-03

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)

, ,
Previous Post
SQL SERVER – Performance counter missing! DMV sys.dm_os_performance_counters is Empty
Next Post
SQL SERVER – ShowPlan XML Additions for ColumnStore Indexes

Related Posts

4 Comments. Leave new

  • Thanks for introducing the concept of In-Memory. I had 2 doubts:
    1. Can I implement In-Memory in SQL Server 2014 Web Edition?
    2. Can In-Memory tables help me to minimize the issue of Table Locks, Row Locks, during an Insert on table or Update on table or Bulk record select on the table?
    Thanks in advance. Your blogs really help me to learn new concepts and you make it really easier to understand with good examples.
    Keep the good work coming….

    Reply
  • Foster, Rick (TS)
    February 3, 2016 10:28 pm

    Hi Dave,

    Thanks for the information, but without looking up on the internet I don’t see where Optimistic Concurrency is explained, without searching the internet, I would guess it is set by “MEMORY_OPTIMIZED_DATA”.

    But I don’t see the value in using this option, the same can be accomplished in regular use the “(nolock)” table hint to read only uncommitted.

    Rick Foster
    [fone number removed]

    Reply
  • Welcome to Oracle world !!!

    Reply
  • Hi Pinal,

    Where is InMemory OLTP concept used.
    The provided example behavior is same in earlier version is SQL.

    Reply

Leave a Reply

Menu