I finished reading the Books Online and CTP1 whitepaper introduction to transactions and memory-optimized tables a couple of days ago. Although I know what the transaction isolation levels mean, I was sort of surprised by some of the descriptions and found them a bit confusing. So I decided to go back to basics and write some simple examples that demonstrated the transaction isolation levels.

Firstly though, I have to define some terms: Books online mentions differences with memory optimized tables between autocommit transactions and user/implicit transactions. “Autocommit transactions” refers to the fact that individual SQL statements are atomic by definition; they either commit or rollback as a unit. For example, if I issue a SQL UPDATE statement that updates 10 rows and 1 row fails update because of a constraint, the statement fails and 0 rows are updated at end-of-statement. In these examples, I’m using “user transactions” where 1-n statements are made part of a single transaction with the BEGIN TRANSACTION and the COMMIT/ROLLBACK TRANSACTION statements. Implicit transactions are another way to make multiple SQL statements transactional, I’m not using this here.

I’m using memory-optimized tables in a database named “simple”. To mention a bit more about the “environment”:

— The database simple snapshot_isolation_state and RCSI turned off at the database level (sys.databases)
— I’m running at default isolation level of read committed (dbcc useroptions)
— XACT_ABORT session setting is turned off (dbcc useroptions doesn’t have a row declaring it is on)

Memory-optimized tables support three transaction isolation levels in user transactions: SNAPSHOT, READCOMMITTED, and SERIALIZABLE. My first puzzle was the BOL describes SNAPSHOT as the “lowest” of the three where memory-optimized tables are concerned. I’ve always considered SQL Server’s SNAPSHOT isolation level as most like Oracle’s SERIALIZABLE isolation level (SQL Server couldn’t use the word serializable because that is implemented by locking in SQL Server). But in the world of memory-optimized tables and user transactions, SNAPSHOT is indeed a lower isolation level than READCOMMITTED. Hmmm.

Also, all three of these levels, when used with memory-optimized tables, can fail the user transaction at commit time, if there’s a conflict detected. SQL Server’s SNAPSHOT against “traditional” tables fail during user transactions at the statement where the conflict is detected.

So, on the examples on the simplest user-transaction behavior… complete script is at the end for you to try. I execute the statements in SQL Server Management Studio, one statement at a time. I’m not using compiled stored procedures.

1. SNAPSHOT

begin tran
select * from dbo.t1 with (snapshot)
— update a row in another session here
commit — OK

begin tran
select * from dbo.t1 with (snapshot)
— update a row in another session here
select * from dbo.t1 with (snapshot) — sees old value
commit — OK

The only “strange” thing here is that you can use SNAPSHOT against memory-optimized tables even though snapshot_isolation_state is OFF at a database level. That’s because memory-optimized tables don’t implement snapshot isolation level the same way as regular tables (e.g. no version store for them).

2. REPEATABLE READ
begin tran
select * from dbo.t1 with (repeatableread)
— update a row in another session
commit — Msg 41305, Level 16, State 0, Line 39 The current transaction failed to commit due to a repeatable read validation failure.

begin tran
select * from dbo.t1 with (repeatableread)
— insert a row in another session
commit — OK

begin tran
select * from dbo.t1 with (repeatableread)
— insert a row in another session
select * from dbo.t1 with (repeatableread) — does not see new row
commit — OK

Repeatable read doesn’t block after reading rows (no locking for memory-optimized tables), but fails at commit time if repeatable read semantics have been violated during the transaction (e.g. any of the rows have changed since you read them). Note that the transaction starts at the first SQL statement, NOT at the same you issue BEGIN TRANSACTION.

begin tran
— update row in another session before reading
select * from dbo.t1 with (repeatableread) — sees new value (and tx starts here)
commit — OK

begin tran
select * from dbo.t2 with (repeatableread) — transaction starts with first SQL statement
— update row in t1 in another session before reading
select * from dbo.t1 with (repeatableread) — sees the old value
commit — Msg 41305, Level 16, State 0, Line 72 The current transaction failed to commit due to a repeatable read validation failure.

3. SERIALIZABLE.

Note that the READCOMMITTED transaction failed if you changed a row you’d read, but did NOT fail if someone else inserted a row during your transaction. Failing a transaction when someone else inserts a row inside a keyrange you’ve read (table you’ve read, if you’re reading all the rows) only happens in serializable isolation level.

begin tran
select * from dbo.t1 with (repeatableread)
— insert a row in another session
select * from dbo.t1 with (repeatableread) — does not see new row
commit — OK, commits in repeatableread

begin tran
select * from dbo.t1 with (serializable)
— insert a row in another session
commit — Msg 41325, Level 16, State 0, Line 55 The current transaction failed to commit due to a serializable validation failure.

And there you have it!

However, it’s not always that simple. Here’s a couple of things that differ with respect to environment, right off the bat…
1. There are subtly different rules for “autocommit” transactions.
2. There are differences when you mix memory-optimzed and non-memory optimized tables in the same user transaction.

This is just meant to be a gentle introduction. The script is enclosed for your reference. For more information, refer to “Transactions in Memory Optimized Tables” topics in BOL.

Cheers, Bob

Here’s a hyperlink to the script:

hek_simple_tx