SQL Server In-Memory OLTP: Transaction Isolation Levels
Introduction
With the introduction of In-Memory OLTP, there have been some changes to the way SQL Server behaved in versions without In-Memory OLTP. One of the areas which work slightly differently in the case of In-Memory OLTP is Transaction Isolation Level. An Isolation Level is something which every transaction in SQL Server runs under and which determines the degree to which each transaction is isolated from the concurrent changes made by other simultaneous transactions.
In this article, we will start off by looking at the isolation levels supported by the traditional disk-based tables and discuss the transaction isolation levels supported by the In-Memory OLTP engine. We will learn how each one works and what types of validation checks are performed under each transaction isolation level. While the In-Memory OLTP engine is fully integrated with the main database engine and works transparently with applications, you will see in this article that there are some gotchas to keep in mind when a transaction involves memory-optimized tables.
Tip: If you would like to read about the transaction isolation levels supported with Disk-Based tables and their internal working, then consider reading this detailed TechNet Wiki article.
Types of Transactions
Before we dive into the transaction isolation levels supported in In-Memory OLTP, let’s first understand the types of transactions possible in SQL Server. The two default types of transactions supported by SQL Server are Explicit and Auto-commit transactions. In addition to the default types, we can have a non-default transaction called an implicit transaction.
1. Explicit Transactions: These types of transactions use the BEGIN TRANSACTION clause indicating the beginning of the transaction followed by the code logic and at the end, they must either have COMMIT TRANSACTION or a ROLLBACK TRANSACTION statement to either commit the changes made by the transaction or roll it back completely.
2. Auto-Commit Transactions: These are the single statements that perform the data modification such as INSERT, UPDATE, DELETE, MERGE and BULK INSERT. Each statement, in itself, is a transaction for which, there is no forced roll back — hence the name auto-commit. However, a rollback occurs when the transaction fails due to network, hardware or system failure.
3. Implicit Transactions: These transactions are invoked under the option SET IMPLICIT_TRANSACTIONS ON prior to the transaction statements. Note that just like explicit transactions, these implicit transactions must end with either COMMIT TRANSACTION or ROLLBACK TRANSACTION. There is a setting in SSMS to enable this option globally, however, if you do that, you will be required to finish each and every transaction when this option is enabled. Every single statement of INSERT, DELETE, including SELECT, will start a transaction which must be finished.
Transaction Behaviors
In a multi-user environment, it is difficult to achieve isolation as there is always a chance of two sessions accessing the same object/row simultaneously. This is exactly where isolation levels come into the picture. They define the isolation requirements for the transaction at the cost of one or more of the following undesirable behaviors.
**1. Dirty Reads: **A transaction can read dirty i.e uncommitted data from another concurrent transaction. As you can imagine, this can potentially lead to inconsistencies and other undesirable issues.
2. Non-Repeatable Reads: Two select statements reading the same data within a single transaction yielding different results as a result of modification/deletion by another concurrent transaction in between the two reads leads to this behavior. In other words, if the same data is read for a second time within the same transaction, a different result is returned.
3. Phantom Reads: This behavior occurs when subsequent reads within the same transaction return new rows that didn’t exist during the first read. This is caused by a concurrent transaction that inserted new rows in between the two reads.
Remember, the above phenomena are undesirable and an isolation level which addresses all these undesirable behaviors is ideal, however, there is always some cost associated and there are some potential side-effects even with the best isolation levels.
Transaction Isolation Levels With Disk-Based Tables
Now that we have covered the types of transactions possible in SQL Server and the undesirable transaction behaviors, let’s see how many types of isolation levels are supported in SQL Server with disk-based tables. Note that the term “disk-based” is used to indicate the traditional tables that are on disk. The In-Memory OLTP tables are known as “memory-optimized” tables.
There are two concurrency models in SQL Server — Optimistic and Pessimistic. Under optimistic concurrency, the data is not locked i.e. readers and writers do not block each other, however, multiple updates to the same piece of data trigger write-write conflicts and a rollback is initiated for one of the transaction. Pessimistic concurrency, on the other hand, is the opposite of optimistic. SQL Server uses locks to lock the data to prevent other concurrent transactions from accessing the same piece of data. An isolation level could either be optimistic or pessimistic.
Note: The following isolation levels follow the least to most-restrictive order. Also, they are discussed briefly as their detailed explanation is outside the scope of this article. Again, for a thorough understanding of them, check out this TechNet article.
To that end, let’s look at the transaction isolation levels supported with disk-based tables.
1. Read Uncommitted: Under this isolation level, dirty reads, non-repeatable reads, and phantom reads are allowed. As it allows all undesirable behaviors, this isolation level is not recommended. However, there may be cases when this may make sense (for example, when you don’t care about these behaviors and all you want it to avoid blocking).
2. Read Committed: This isolation level prevents dirty reads but allows non-repeatable and phantom reads. This is the default isolation level in SQL Server. Under this, Shared locks are held on the rows being read and released immediately as soon as they are read.
3. Read Committed Snapshot: This is an Optimistic version of the default Read Committed discussed above. In this, SQL Server uses a version-store in tempdb to keep the older row versions, thus avoiding locks. Readers don’t block Writers and vice-versa. Exclusive locks are still acquired by the update transactions.
**4. Repeatable Read: ** Under this isolation level, dirty reads and non-repeatable reads are avoided. However, phantom reads are allowed. Shared locks are held until the end of the transaction.
**5. Serializable: **All undesirable read behaviors are avoided in this level by enforcing excessive locking. To prevent phantom reads, SQL Server acquires key-range locks and holds them until the end of the transaction and this prevents other concurrent transactions from inserting new records in the ranges read.
6. Snapshot: This optimistic isolation level guarantees read consistency by ensuring that a transaction reads data as it existed at the start of the transaction. If there are any changes to the data after the transaction under the Snapshot isolation level is started, then those changes will be invisible. Note that repeatable reads and phantom reads are **not **prevented, however, they do not appear in the query results due to the snapshot behavior.
A quick summary of the isolation levels and their behaviors in disk-based tables is shown in the following table.
| Isolation Level | Level Type | Dirty Reads | Non-Repeatable Reads | Phantom Reads |
| READ UNCOMMITTED | Pessimistic | YES |
YES | YES |
| READ COMMITTED | Pessimistic | NO | YES |
YES |
| READ COMMITTED SNAPSHOT | Optimistic for read transactions | NO | YES | YES |
| REPEATABLE READ | Pessimistic | NO | NO | YES |
| SERIALIZABLE | Pessimistic | NO | NO |
NO |
| SNAPSHOT | Optimistic | NO |
NO | NO |
Transaction Isolation Levels with Memory-Optimized Tables
In-Memory OLTP supports three types of transaction isolation levels. They are SNAPSHOT, REPEATABLE READ and SERIALIZABLE. An important point to note is that these transaction isolation levels behave slightly differently in the case of memory-optimized tables than with disk-based tables. SQL Server In-Memory OLTP uses a complete lock- and latch-free approach when dealing with transactions and thus, there is no blocking.
There are three phases of every transaction in In-Memory OLTP — Processing, Validation and Post Processing. A thorough explanation of these phases is beyond the scope of this article but just know that one of the first things that happen during the validation phase is checking of isolation level violation. Note that the validation phase begins after a transaction has been committed but before the commit is acknowledged to the client. In other words, after a transaction issues a commit, the validation phase starts and SQL checks to see if there were any isolation level violations and if so, the transaction is aborted. Unlike transactions in disk-based tables which use locks to ensure consistency, memory-optimized tables follow a pure optimistic concurrency model and allow concurrent changes to the data. However, the isolation level validation is done during the validation phase to check for any violations.
As mentioned earlier, SQL Server acquires no locks or latches during DML operations, hence it is entirely possible for any concurrent transactions to access the data and change it while a transaction is already in progress. Contingent upon the transaction isolation level of the active transaction, these concurrent changes might result in an isolation level violation and roll back the active transaction after a commit is issued. SQL Server, during the validation phase, ensures that there is no invalid data.
During the processing of a transaction, the In-Memory OLTP engine keeps track of the **read-set **or write-set or **scan-set **for the transaction depending upon the transaction isolation level. These “sets” are the sets of pointers to the data rows that have been read, written or scanned respectively by the transaction. SQL Server uses the read-set to check for repeatable reads and scan-set to check for phantoms.
Before we proceed with the explanation of isolation levels with memory-optimized tables, let's set up the environment. We will create a non-durable memory-optimized table and perform the tests against it. Note that as each isolation level discussion is completed, the changes to the table will be reverted to bring it back to the original state i.e. with a single record.
--CREATING A NEW IN-MEMORY NON-DURABLE TABLE CALLED "INMEMTBL"
CREATE TABLE DBO.INMEMTBL
( ID INT NOT NULL CONSTRAINT PK_INMEMTBL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 128),
NAME VARCHAR(20) NOT NULL)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
--INSERTING A SINGLE RECORD
INSERT INTO DBO.INMEMTBL (ID, NAME)
VALUES (1, 'JACK')
Let's run a select against the newly created table and see what it returns.
The select statement returned the single record that we inserted in the code above. Notice that "WITH (SNAPSHOT)" was automatically added upon selecting the top 1000 option in SSMS.
1. Snapshot Isolation Level
Under this isolation level, concurrent changes are invisible to the active transaction that is already in progress. A transaction only sees data as it existed at the time the transaction is started and any changes past the logical start time of the active transaction are invisible to it. The only isolation level validation during the commit time is checking for the primary key violations also known as snapshot validation.
Note that this level doesn’t prevent phantom reads nor does it guarantee read stability (repeatable read). However, sessions running under this isolation level do not see any non-repeatable reads or phantom reads. This is due to its snapshot behavior. Interpreted T-SQL must specify the transaction isolation level when working with memory-optimized tables in the form of a table-hint or, through the database option called “MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT”.
In this isolation level, concurrent changes are allowed but that doesn’t matter since a transaction only sees the version of the data as it existed at the transaction start time. Thus, other concurrent sessions could update/insert data while a transaction is in progress but those changes would be invisible to the active transaction.
Let us take an example and understand what type of validation occurs with snapshot isolation level and how violations are encountered.
| Time | Transaction T1 | Transaction T2 | Results |
| 1 | Begin transaction Select ID, name From dbo.InMemTbl with (snapshot) |
||
| 2 | Update dbo.InMemTbl Set name = ‘Josh’ Where ID = 1 |
||
| 3 | Select ID, name From dbo.InMemTbl with (snapshot) |
The old version of the row (Jack) is returned. | |
| 4 | Commit | success | |
| 5 | Begin transaction Select ID, name From dbo.InMemTbl with (snapshot) |
||
| 6 | Insert into dbo.InMemTbl Values (2, ‘Wendy’) |
||
| 7 | Select ID, name From dbo.InMemTbl with (snapshot) |
Does not return the phantom record (2, ‘Wendy’). | |
| 8 | commit | success |
As you can see in the table above, there is no impact to the transaction running under snapshot isolation level by the concurrent changes made by other concurrent transaction. At time 2, there was an update to the row (by transaction T2) which was already read by the transaction T1 at time 1. However, that update was invisible to T1 since it only sees data as it existed at the time T1 started. Thus preventing non-repeatable reads. Similarly, at time 6, there was an attempt to insert a phantom record but that record did not show up at time 7 due to the snapshot behavior of this isolation level.
Now that we have seen how snapshot isolation level prevents non-repeatable and phantom reads from occurring, let’s see what violation can potentially occur under this isolation level.
1.1 Primary-Key Violation Check
The only validation check that happens under the snapshot isolation is the check for primary key violations also known as snapshot validation. Note that this check is done in any isolation level.
The table below shows how a primary key violation is triggered. Notice that in the case of In-Memory OLTP, the primary key violation exception is thrown after the commit has been issued i.e. during the validation phase. This behavior is opposite of the disk-based tables where the error is thrown at the time of the second insert i.e as soon as the PK violation is encountered.
| Time | Transaction T1 | Transaction T2 | Results |
| 1 | Begin transaction Insert into dbo.InMemTbl with (snapshot) (ID, name) values (3, ‘MARY’) |
||
| 2 | Begin transaction Insert into dbo.InMemTbl with (snapshot) (ID, name) values (3, ‘MARY’) |
||
| 3 | commit | The first transaction commits successfully | |
| 4 | commit | Error 41325; the current transaction failed to commit because of a Serializable validation failure |
As you can see above, the primary key validation is done post-commit and the transaction T2 fails to commit due to the snapshot validation failure.
Snapshot isolation works in a similar manner to disk-based tables and protects from non-repeatable and phantom reads. Also, notice that the only validation check performed under this level is the snapshot validation. You will see in the next sections how other isolation levels have extra validation checks. As you can imagine, the extra checks potentially cause some overhead and hence, the recommended isolation level for transactions that work with memory-optimized tables is the snapshot isolation level.
2. Repeatable Read Isolation Level
Let’s see how concurrency works under repeatable read isolation level. The transaction is started at the moment the data is accessed the first time and **not **with the BEGIN TRANSACTION statement. This isolation level includes the snapshot validation check discussed above besides the repeatable read check.
| Time | Transaction T1 | Transaction T2 | Results |
| 1 | Begin transaction Select ID, Name From dbo.InMemTbl with (repeatableread) |
||
| 2 | Update dbo.InMemTbl Set name = ‘Josh’ Where ID = 1 |
||
| 3 | Select ID, Name From dbo.InMemTbl with (repeatableread) |
Returns old row version I.e. Jack | |
| 4 | commit | Error 41305. The current transaction failed to commit because of a repeatable read validation failure. |
|
| 5 | Begin transaction Select ID, Name From dbo.InMemTbl with (repeatableread) |
||
| 6 | Insert into dbo.InMemTbl Values (2, ‘Wendy’) |
||
| 7 | Select ID, Name From dbo.InMemTbl with (repeatableread) |
Does not return the phantom record (2, ‘Wendy’). | |
| 8 | Commit | Success |
Notice that a concurrent transaction T2 at time 2 updated the data already read by the first transaction T1 at time 1. However, when T1 attempts to commit at time 4, the repeatable read validation kicks in and fails the transaction T1 due to the fact that T2 changed a row in between the two reads, thus causing non-repeatable reads. An important point to note here is that SQL Server did not block the transaction T2 from updating the data read by T1. However, at time 3, the result didn’t reflect the update done by T2.
Unlike the behavior in disk-based tables where concurrent sessions would be blocked until the repeatable read transaction is committed, repeatable read under In-Memory OLTP works differently and allows changes, however, the validation fails and the transaction is rolled back. Also note that there are no phantoms in the case of repeatable read isolation level in In-Memory OLTP and this is, again, different from disk-based tables. The newly inserted row at time 6 exceeded the logical start time of the active transaction T1 and hence it was invisible for the transaction T1.
3. Serializable Isolation Level
Let’s repeat the tests that we performed under the Repeatable Read isolation level and see how they work under Serializable isolation. Note that under this isolation level, validation checks for snapshot and repeatable read are performed beside serializable validation check.
| Time | Transaction T1 | Transaction T2 | Results |
| 1 | Begin transaction Select ID, Name From dbo.InMemTbl with (serializable) |
||
| 2 | Update dbo.InMemTbl Set name = ‘Josh’ Where ID = 1 |
||
| 3 | Select ID, Name From dbo.InMemTbl with (serializable) |
Returns old row version I.e. Jack | |
| 4 | commit | Error 41305. The current transaction failed to commit because of a repeatable read validation failure. |
|
| 5 | Begin transaction Select ID, Name From dbo.InMemTbl with (serializable) |
||
| 6 | Insert into dbo.InMemTbl Values (2, ‘Wendy’) |
||
| 7 | Select ID, Name From dbo.InMemTbl with (serializable) |
Does not return the phantom record (2, ‘Wendy’). | |
| 8 | commit | Error 41325. The current transaction failed to commit because of a serializable validation failure. |
As you can notice, both update and insert, by the concurrent transaction T2 are allowed. However, the transaction T1 fails in both cases during the validation phase due to repeatable read and serializable violations respectively. Hence, serializable isolation level prevents a transaction from committing when another concurrent session changes or inserts new rows that violate the serializable isolation.
This behavior is different from the disk-based tables where serializable isolation level uses excess locks to prevent other concurrent transactions from making changes or inserting new rows until the transaction running under Serializable finishes.
Conclusion
In-Memory OLTP supports three transaction isolation levels — Snapshot,** Repeatable Read** and Serializable. These isolation levels have different behavior in the case of memory-optimized tables where they don’t acquire locks or latches and the isolation level violation is checked during the validation phase of the transaction at the time of commit i.e. after the commit has been issued but not acknowledged to the client. An exception is thrown and the transaction is aborted if any violations are encountered. Isolation levels in disk-based tables, on the other hand, use locks to address the non-repeatable reads and phantom reads.
As you have seen in the above sections, REPEATABLE READ and SERIALIZABLE have extra validations compared to the snapshot where there is only snapshot validation. Hence, it is recommended to use snapshot isolation level when working with memory-optimized tables unless there is a real need to use the other two. Also, the recommended best practice is to use READ COMMITTED for disk-based tables and SNAPSHOT isolation level for the transactions that use memory-optimized tables.
Note that for single-statement (auto-commit) transactions with memory-optimized tables, SQL automatically uses the SNAPSHOT isolation level so there is no need to specify the table hint, however, when working with natively-compiled stored procedures or explicit transactions, you would need to specify the table hint to indicate the isolation level the transaction should be run under.