Is your transaction as lonely as you think? A cheat sheet for isolation levels in SQL Server

This post aims to give you a brief introduction into what isolation levels are and how they work within SQL Server. In short, they are a measure for controlling concurrency on the server.

What is an isolation level?

We will start with a definition and then evolve it: the isolation level specifies how much one transaction must be protected from resource or data modifications made by other transactions.

Consider the case where user A is trying to read a list of products out of a table, meanwhile user B comes along and changes some of the product information in the table. As part of their same transaction, user A comes back to try and read the product table, but it has changed. Do you want user A to read the new information or not?

Isolation levels allow you to decide what would happen in scenarios like the one I’ve just described and so it should come as no surprise that they are described in terms of which concurrency side effects they allow. The following is a brief description of each of the possible unwanted concurrency side effects:

Dirty reads:

This refers to the situation where transaction A reads an uncommitted set of data that transaction B is working on. This can be problematic if transaction B fails or is rolled back.

Non-repeatable reads:

This is the situation where a piece of data, which is read twice inside the same transaction, cannot be guaranteed to contain the same information. From the example this would mean that the second read picked up the data that user B had changed in the product table.

Phantom reads:

This refers to the case when transaction A inserts or deletes a row from a set of data, that transaction B is currently reading.

Missing and double reads:

This is the case where a transaction A can be performing a range scan on a table and transaction B can come along and move a row so that transaction A reads it twice or misses it.

Lost updates:

This can happen when two processes read the same data and then both try and update it at the same time, but with different values. Only one of them will succeed and the other will be lost.

Halloween effect:

Refers to situation where data moves position in the result set and thus could be updated multiple times.

Many people do not fully understand isolation levels and how they control locking and protect data within their environment. Because of this you will often see codes full of hints, where the locks required are explicitly declared. Often this involves throwing in the NOLOCK hint, for that ‘magical’ query speed boost. Using that hint is the same as using the read uncommitted isolation level, which I will introduce you to soon. Understanding the isolation levels and how they use locking will help you understand the suitable use cases and the side effects of using them.

That’s not to say that using hints is always bad, there are many cases where they work, however it is useful to understand what is happening so that you don’t have any unexpected consequences.

What do isolation levels control and how do they prevent some of the above side effects?

The main way that isolation levels vary is in the locks they decide to take. Each level uses different approaches in deciding which locks are taken when data is read and how long the locks are held for. The lower isolation levels increase the ability of multiple users to access the same data, but consequently they increase the number of concurrency effects. Isolation levels are focused on the locks used when reading and do not interfere with the locks acquired to protect data modification.

Isolation levels can be set at server level, database or transactional level (depending on the one you pick). To change the isolation level in a query is as simple as adding: SET TRANSACTION ISOLATION LEVEL <Isolation Level> at the start of your query. It is worth nothing that you can also change your isolation level during the transaction if required.

However, you should carefully consider the impacts of it before doing so.

What are the different isolation levels?

The different isolation levels are normally split into two groups, the ones that are described as pessimistic and the ones that fall under optimistic. The main difference being that optimistic levels try to reduce the amount of locks needed, but as a consequence suffer other overheads, such as increased tempdb usage. Optimistic levels use row versioning to prevent reads from being blocked by data modification locks. We’ll start with the 4 pessimistic ones:

Read Uncommitted:

This isolation level specifies that statements can read rows that have been modified by other transactions, but not yet committed. This is the lowest isolation level and consequently, many side effects are present. Reads are not blocked by exclusive locks and do not need to take shared locks; in essence they can do whatever they want. This means that you will allow a lot of concurrency, but you’ll sacrifice the reliability of the data.

Read Committed:

This is the default isolation level for SQL Server. It stops statements from reading data that has been modified but not yet committed by other transactions. This prevents dirty reads from taking place, but not phantom or non-repeatable reads. It does this by using shared locks for reads.

Repeatable Read:

This isolation level stops statements from reading data that has been modified but not yet committed by other transactions. It also prevents other transactions from modifying data that has been read by the current transaction until it has completed. It does this by generating shared locks on all data that is read and holding these locks until the transaction is finished.

Serializable:

Specifies the following:

• Statements are prevented from reading data that has been modified but not yet committed by other transactions.
• Transactions cannot modify data that has been read by the current transaction until the current transaction completes.
• Other transactions aren’t allowed to insert new rows into a table read by the current transaction, if their key values fall in the range of keys read by any statements in the current transaction. So they are blocked until the current transaction completes.
• Range locks are placed on the range of key values that match the search conditions of each statement executed in a transaction.

Due to this the serializable isolation level allows for the lowest level of concurrency.

We’ll now move onto the 2 optimistic levels, it is important to note that they both need to be enabled at database level to enable them to be used:

Snapshot:

The first of the two optimistic isolation levels. There is no locking as it uses row versioning in tempdb instead. This means that it will read a transactionally consistent version of the data that existed at the start of the transaction. As a result it can only recognise data modifications that were committed before the start of the transaction.

Read Committed Snapshot:

Very similar to read committed, apart from the fact that row versioning is used instead.  Each statement uses a transactionally consistent snapshot of the data showing how it existed at the time the transaction started. This means that locks are not used or needed to protect the data from updates by other transactions.

Below is a table summarising the different isolation levels and the side effects they allow:

Summary

This is a very brief description of isolation levels that aims to introduce you to the different types. There are use cases for all of them. In the case of transactions that need to guarantee perfect data, higher isolation levels should be used. In systems where there are thousands of users and concurrency is paramount, the lower levels should be used.

The decision on which isolation level to use is always a trade-off between avoiding concurrency side effects, while also keeping your system usable and not constantly having transactions sat idle waiting for locks to be released. The main point, is that when you make your decision, you should now be a little bit more informed about the consequences and benefits of each level.