// Week 17: Pessimistic Concurrency
Hello and welcome back to the SQL Server Performance Tuning series. Today marks the beginning of the 5th month where we will talk about Locking, Blocking, and Deadlocking in SQL Server.
SQL Server provides you a pessimistic and a optimistic concurrency model that define how concurrent queries are executed. In todays installment I want to give you an overview about the various isolation levels that are part of the pessimistic concurrency model, and next week I will talk in more details 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) how long 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 a 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-Repeable Read: you read a record multiple times, and you are getting back multiple different varying values.
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 repetable 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 dealing with more concurrency against more 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 Uncomitted. 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 of 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 phenomen is allowed or not allowed during data reading. The following table gives you an overview about it.
| Dirtty reads | Non-repeatable reads | Phantom Records | |
|---|---|---|---|
| Read uncommitted | Yes | Yes | Yes |
| Read Commmitted | No | Yes | Yes |
| Repeatable reads | No | No | Yes |
| Serializable | No | No | No |
In addition to request a specific isolation level, SQL Server is also able to promote temporarily for queries the isolation level to guarantee the correctness of the query.
Summary
Today you have learned the basics about the various pessimistic isolation levels in SQL Server. This is also the foundation you have to know when troubleshooting locking and blocking scenarios in SQL Server: readers are blocking writers, and writers are blocking readers.
Next week we will have a more detailed look on 2 other transaction isolation levels that SQL Server supports in combination with Optimistic Concurrency. Stay tuned!