In-Memory Is New #1 : New Transaction Isolation Level

SQL Server 2014 supports 4 types of transaction isolation levels:

  • READ COMMITTED
  • SNAPSHOT
  • REPEATABLE READ
  • SERIALIZABLE

For memory-optimized tables READ COMMITTED transaction isolation level is only supported in auto-commit transactions and cannot be used within an explicit transaction. On the surface, the other 3 transaction isolations types look exactly like same as transaction isolation levels in disk-based tables with same names. Really?

 

Let’s take a look at how memory-optimized tables in snapshot isolation.

 

Disk-Based tables

First thing, create a test database – db1 and a test table table1:

CREATE DATABASE db1;
go
ALTER DATABASE db1 SET ALLOW_SNAPSHOT_ISOLATION ON
go
use db1;
go
CREATE TABLE t1 (id INT, msg varchar(20));
go
INSERT t1 VALUES (1,‘abc’);
go

 

Step 1: start transaction TX1 in session 1 and update table t1 (id = 1)

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go
BEGIN TRAN TX1
UPDATE t1 SET msg =‘session 1’ WHERE id = 1

 

 

Step 2: start transaction TX2 in session 2 and update table t1 (id =1)

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go
BEGIN TRAN
UPDATE t1 SET msg =‘session 2’ WHERE id = 1

 

TX2 is now being blocked by TX1

 

Step 3: commit transaction TX1 in session 1

COMMIT TRAN TX1

 

In disk-based environment, TX2 is rolled back immediately after TX1 commits. Note TX2 could continue if TX1 rolls back instead of commits.

 

Following picture shows how TX1 and TX2 behave in a update conflict scenario:

 

 

Memory-Optimized Tables

Now, let’s take a look at how memory-optimized tables behave in snapshot transaction isolation level.

First thing (again) – update database and create a memory-optimized table:

alter database db1 add filegroup IMOLTP contains memory_optimized_data;
go
alter database db1 add file (name=‘imoltp’,filename=‘c:\db\imoltp’) to filegroup IMOLTP;
go
create table t1_oltp
(id int not null primary key nonclustered hash with (bucket_count = 000000),
msg char(20)) with (memory_optimized = on)
go

 

Step 1: begin transaction TX1 and update row (id = 1)

begin tran TX1
update t1_oltp with (snapshot) set msg=‘TX1’ where id = 1

 

Step 2: begin transaction TX2 and update same row (id=1)

begin tran TX2
update t1_oltp with (snapshot) set msg=‘TX2’ where id = 1

 

SQL Server detects a write-write conflict and rolls back TX2 immediately:

Memory-optimized tables in snapshot transaction isolation:

Summary

Memory-optimized tables use a newly designed, lock-free optimistic transaction isolation level which works differently comparing to traditional disk-based transaction isolation levels. Transaction isolation level in In-memory is new!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s