Isolation levels affect how aggressively SQL Server places and holds locks on tables and schemas. Get too lazy and you’ll end up with phantom data and dirty reads. Be too zealous, and you’ll end up troubleshooting deadlocks. Here’s an overview of the different types of isolation levels available, to help you choose which one is best for you.

How to set the isolation level

The isolation level is set using a SET command.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The isolation level is context specific; if you set it in a stored procedure, it reverts to the connection’s previous state once the procedure exits.

READ UNCOMMITTED

READ UNCOMMITTED is the least restrictive isolation level and is equivalent to using the NOLOCK locking hint on all tables. This allows for what’s known as dirty reads, i.e. that the transaction can read data that other transactions have not yet committed. This means that if the other transactions roll back, these rows may longer exist.

The difference from using the NOLOCK hint is that READ UNCOMMITTED does not even place schema-stability locks, which means that it is even possible that for other transactions to drop or modify tables or views that you’ve included in your query.

Avoid using READ COMMITTED or the NOLOCK hint, unless you are sure the underlying data does not change. Even then, you should selectively apply NOLOCK to specific tables.

READ COMMITTED

READ COMMITTED is the SQL Server default isolation level. Uncommitted changes from other transactions cannot be read, which means that dirty reads are not possible. However, reading data does not place a lock on it, which means that other transactions can still make changes to “your” data, which can result in what’s known as non-repeatable reads.

REPEATABLE READ

This isolation level provides protection against not only dirty reads but also non-repeatable reads. It does this by placing shared locks on everything your transaction reads or modifies. Because the shared locks are not range locks, phantom reads are still possible.

The REPEATABLE READ isolation level holds these shared locks for the duration of the transaction, making it less concurrent than for instance READ COMMITTED.

SNAPSHOT

The SNAPSHOT isolation level, like the name implies, provides your transaction with a snapshot of how the database looked like at the time the transaction started. Outside changes to the data made after this point are not visible to the transaction. Because of the snapshot approach, this isolation level does not implicitly block data that it reads, allowing for fairly good concurrency, but at the price of not properly locking the rows that you modify in the transaction.

This mode requires that you turn on row versioning by setting ALLOW_SNAPSHOT_ISOLATION to ON.

SERIALIZABLE

The SERIALIZABLE isolation level is the most restrictive and least concurrent, but it provides very good transactional integrity. Statements in the transaction cannot read other transactions’ uncommitted data (dirty reads), and it locks any data you’ve selected or modified (including placing range locks to prevent phantom data). All locks are held until the transaction completes, so it’s equivalent to placing HOLDLOCK hints on all tables.

Needless to say, a restrictive isolation level like this one may be suitable for some purposes, while other situations on systems with high transaction volumes may generate frequent deadlocks between transaction.

Dirty reads

A dirty read is when data you just selected may have changed or may not exist at all by the time you commit your transaction. This can typically happen when using the NOLOCK hint or the READ UNCOMMITTED isolation level.

Non-repeatable and phanom reads

A non-repeatable read is when two identical SELECT statements, within the same transaction, return different data the first and second time you run them. REPEATABLE READ provides some protection against non-repeatable reads by placing shared locks.

Rows that appear in the second SELECT but not in the first are called phantom reads. The only way to eliminate phantom reads are by explicitly placing an exclusive table or range lock, or using the SERIALIZABLE isolation level.

Row versioning and READ_COMMITTED_SNAPSHOT

Working with the READ COMMITTED and SNAPSHOT isolation modes, you should also pay attention to the database’s READ_COMMITTED_SNAPSHOT setting, which controls if transactions are row-versioned. Row versioned transactions allows for a “read only” snapshot view of the data, as it looked when you started your transaction, regardless of other transactions that may modify it in the meantime. A comprehensive discussion about row versioning is available on MSDN.