SQL Server provides pessimistic and optimistic concurrency models that define how concurrent queries are executed. In todays blog posting I want to give you an overview of the various isolation levels that are part of the pessimistic concurrency model, and in a subsequent blog posting I will talk in more detail about the optimistic isolation levels that were introduced back with SQL Server 2005.

Pessimistic Isolation Levels

Pessimistic concurrency means that read queries (SELECT) are blocking write queries (INSERT, UPDATE, DELETE), and write queries are blocking read queries. SQL Server uses for this behavior so-called Locks:

• Readers are acquiring Shared Locks (S)
• Writers are acquiring Exclusive Locks (X)

Both locks are incompatible to each other. This means that you can’t read and write a record at the same time. If this happens, a so-called Blocking scenario happens. When you set a specific transaction isolation level, you are directly influencing read queries (SELECT statements) while they are holding their S locks. You can’t influence writers in any way – an X lock is always taken when you change a record in a table (INSERT, UPDATE, DELETE).

By default every query runs in the isolation level Read Committed. Read Committed means that SQL Server acquires an S lock on the record for the time the record is read. As soon as the record is completely read and processed, the S lock is immediately released. When you run a Scan operator against a table (single-threaded), only 1 S lock is held at a given time. Because of this behavior it is possible that some other transaction changes the record afterwards. If you read the same record again within the same transaction, you would have a so-called Non-Repeatable Read: you read a record multiple times, but you get different values back.

If you can’t live with the behavior of Non-Repeatable Reads, you can use a more restrictive isolation level like Repeatable Read. This isolation level gives you repeatable reads (hence the name) by holding S locks until the end of your transaction when you read a record. Therefore no one else can acquire an X lock and change your record while you are reading it within a transaction (it would yield to a blocking scenario because of the incompatibility). This approach has advantages and also disadvantages: on one hand you are getting more correct results (repeatable reads), on the other hand you will have more blocking scenarios, because readers are holding their S locks until the end of their transactions. You are always balancing concurrency against data correctness.

You can be even more restrictive by changing the isolation level to Serializable. With that isolation level – the most restrictive one in SQL Server – you can avoid so-called Phantom Records. A phantom record is a record that appears and disappears when you retrieve a subset of records from a table multiple times. To avoid phantom records SQL Server is using a so-called Key Range Locking technique by locking the range of records that you initially retrieve.

Therefore no other concurrent query is able to insert rows into that locked range. It’s also impossible to delete a row from the range, or perform an update statement that “moves” another row into the range. Such queries will just block. You also need a supporting index on the search predicate that defines the range of your rows. With a supporting index, SQL Server will lock individual index keys. Without a supporting index, SQL Server has to lock your complete table, which will hurt the concurrency and throughput of your database tremendously!

And finally SQL Server supports the isolation level Read Uncommitted. With read uncommitted no S locks are acquired when reading data. Therefore it’s possible to read uncommitted data from current ongoing in-flight transactions. That’s a so-called Dirty Read. If such a transaction rolls back, you have read data that never ever existed logically in your database. It’s not really a recommended isolation level, and you should be very selective about using it. Dirty Reads can be also enforced by using the famous NOLOCK query hint.

The pessimistic isolation levels aren’t that complicated, are they? The isolation level just tells SQL Server how long to hold S locks for reading data. And based on that, the isolation level defines which actions are allowed or not allowed during data reading. The following picture gives you an overview about it.

In addition to requesting a specific isolation level, SQL Server is also able to promote queries temporarily to the isolation level to guarantee the correctness of the query. You can find more information about this, in my weblog posting Myths and Misconceptions about Transaction Isolation Levels.

Summary

Today you have learned the basics about the various pessimistic isolation levels in SQL Server. This is also the foundation for you have to know when troubleshooting locking and blocking scenarios in SQL Server: readers are blocking writers, and writers are blocking readers.

Thanks for your time,

-Klaus