Locking, Blocking and Isolation Levels

Last updated: 11/11/2018

As I mentioned in a previous post all actions performed against data or the SQL objects, i.e. any DML or DDL statements, are run inside transactions.  This post delves into more details into how transactions work…

SQL Server maintains data integrity and system stability by adhering to the ACID properties which is an acronym for Atomicity, Consistency, Isolation and Durability.

The isolation part of this acronym states that all transactions are run in isolation from each other and no two resources can update the same data or definition of a SQL object at the same time. In the pessimistic isolation levels (Read Uncommitted, Read Committed, Repeatable Read and Serializable) this is controlled by locking.  In the optimistic isolation levels (Read Committed Snapshot and Snapshot) this is controlled by row versioning.  In this post I’m only going to deal with the four pessimistic isolation levels.

The are several types of locks, including shared, exclusive, update, intent and schema. To start with we’ll just look at shared and exclusive, as these are the critical ones for getting an understanding of how SQL Server uses locks when reading and writing data.

The locks can be placed on various resources and by default this is handled by SQL Server.  For example, if you are just updating one row in a table then SQL Server will most likely just place a lock against that one row.  If you are updating every row in a table then most likely the whole table will have a lock placed against it.

The way locks behave is controlled by the isolation level and the the default level is READ COMMITTED.  At this level shared locks are requested when reading data and are released once the data has been read.  They are not held on until the transaction commits.  Exclusive locks are requested whenever data or SQL objects are updated.  In the default level these are not released until the transaction commits.

If a resource has a lock held against it then another transaction that tries to access the same resource (by requesting a lock) may be blocked.  Blocking will occur if the lock currently held is incompatible with the newly requested lock (see below for more on lock compatibility).  Exclusive locks are not compatible with other exclusive locks or with shared locks.  Shared locked are compatible with each other but not with exclusive locks.  This means that two transactions can issue shared locks on a resource and can both read the data.  It also means that one transaction cannot update data until another transaction has finished reading it, and one transaction cannot read data that is being updated by another transaction (except in the READ UNCOMMITTED isolation level which is discussed in the next section).

Isolation Levels

There are four isolation levels that use locks and these allow you to control whether you want more consistency or more concurrency in your application.  The greater the consistency, the lower the concurrency and the greater the concurrency, the lower the consistency.  If you want more resources to be able to access the data quicker then you need to reduce locking which may reduce the consistency of the data.  If you want more consistency in your data then you may have to holds locks for longer which can cause more blocking and can reduce concurrency.

I’m going to demo how some of these different levels work so let’s set up a bit of test data quickly…

CREATE TABLE dbo.IsolationLevelTesting
(
 Id INT NOT NULL PRIMARY KEY
 ,SomeData VARCHAR(100) NOT NULL
);
INSERT INTO dbo.IsolationLevelTesting (Id, SomeData)
VALUES (1, 'Simon');

READ UNCOMMITTED

This level allows the most concurrency but the least consistency.  When this level is set, shared locks are not requested whenever data is read.  This removes the blocking caused by the incompatibility between exclusive and shared locks and means that data can be read even if it has an exclusive lock against it if, for example, it is being updated.  The read request will read the new uncommitted version of the data that is currently held in the other transaction.  If the transaction is rolled back for some reason and the data is re-read then this time the original version of the data will be read and the uncommitted version is lost.  If you application has already read the uncommitted version and started doing something with it then you may end up with data integrity issues if it suddenly disappears.  Reading uncommitted data is called a dirty read.  Here’s a quick demo…

From one connection begin a transaction and update the row in dbo.IsolationLevelTesting.

BEGIN TRAN
UPDATE dbo.IsolationLevelTesting
SET SomeData = 'Peacock'
WHERE Id = 1;

This will leave the transaction open with an exclusive lock.  Now from a second connection set the isolation level to READ UNCOMMITTED and select the data from dbo.IsolationLevelTesting.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM dbo.IsolationLevelTesting;

You should see the value Peacock returned which is the uncommitted version.  Now rollback the transaction from the first connection.

ROLLBACK TRAN

Re-run the select statement from the second connection and you should see the data is back to Simon.  

You can also use hints to override the existing session transaction isolation level and can use the following to run the select statement in the READ UNCOMMITTED level.

SELECT *
FROM dbo.IsolationLevelTesting WITH (NOLOCK);

In my opinion you should only use the WITH (NOLOCK) table hint in your operational production systems in exceptional circumstances.  If you are having performance problems and think using the WITH (NOLOCK) table hint will fix them then you probably need to first look at the code that is requesting the exclusive locks (or other incompatible locks) on the resource you are trying to access and optimise that.  Remember that in the READ UNCOMMITTED level any select statements will read the dirty uncommitted data and not the original data before it was locked.

READ COMMITTED

As mentioned above this is the default level.  This provides more consistency than READ UNCOMMITTED as read commands request shared locks which are incompatible with any exclusive locks held in open transactions that are updating data.  This prevents dirty reads.  Let’s try and re-run the code in the READ UNCOMMITTED level demo above…

Start a new transaction in one connection and update the data in dbo.IsolationLevelTesting.

BEGIN TRAN
UPDATE dbo.IsolationLevelTesting
SET SomeData = 'Peacock'
WHERE Id = 1;

Now from a second connection set the isolation level to READ COMMITTED and then try and read from dbo.IsolationLevelTesting.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT *
FROM dbo.IsolationLevelTesting;

You’ll find that the read request doesn’t return and just hangs.  This request is blocked by the open transaction in the first connection and will just hang until the lock is released.  Now is an appropriate time to look at how we can view what locks are currently in place and whether any of them are blocking other requests.  The SPID of my first connection with the open transaction is 51 and the SPID of the second connection with the hanging read request is 52.

You can run the built-in sp_who2 stored proc to list all connections and their status.  When I run this I see the following two rows…

You can see that SPID 52 is SUSPENDED and is blocked by SPID 51 (shown in column 5). This stored proc is quite handy for seeing if there is currently any blocking if a query you are running is hanging.  However if you want to see more information then download and run Adam Machanic’s sp_whoisactive. If I run this I can see the following two rows returned…

This shows that SPID 51 is blocking 52 and also gives you a sql_text column that when you click on it shows you the statement being run.  Here’s what I see when I click on the one of SPID 51…

You can also delve deeper into what locks are being held by looking at the sys.dm_tran_locks view.  If I filter it by my two SPIDs as follows…

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id IN (51, 52)
ORDER BY request_session_id;

I get the following rows returned…

Here you can see all the different locks that are placed on the various resources within each session.

The first four rows show the locks taken out for SPID 51 which is the one with the open transaction.  You can see that there is an exclusive lock (request_mode = X) placed on the KEY resource_type.  The KEY value means a row in a clustered index (which was created by default because of the PRIMARY KEY).  You can also see that there is a shared lock (request_mode = S) on the DATABASE and intent exclusive locks (request_mode = IX) on the OBJECT and PAGE resources.  Intent locks are used to allow SQL Server to determine if there are any locks further down the resource chain that would prevent a lock being placed on a higher-level resource.  For example, if there was an exclusive lock on a single row in a table, there would be an intent exclusive lock on the page and the table.  If a query was run to select all data in the table, then a shared lock on the table would be requested. Because there is already an intent exclusive lock on the table, SQL Server knows that the requested shared lock on the table must wait until the exclusive lock further down the resource chain is released.  This removes the need for SQL Server to scan every page and row to check for locks.

The second four rows in sys.dm_tran_locks show the locks taken out for SPID 52 which is the hanging SELECT statement.  You can see there is a shared lock (request_mode = S) at the KEY level that has a request_status of WAIT.  This means this lock is waiting for the resource to become available.  You can also see the shared and intent shared locks on the DATABASE, PAGE and OBJECT resources.

There are also several other ways to identify and monitor locks and blocks but these you usually enough for me to work out what is going on.

Now back to the isolation levels…

If we now go and commit the open transaction in the first connection the new value is committed to the database, the exclusive lock is released and the shared lock from the second connection is allowed in and the newly committed data is read.

As mentioned above, in the default READ COMMITTED level any shared locks are released as soon as the read has completed and does not care if there is still an open transaction for that batch.  Say, for example, you have a stored procedure that is run inside a transaction.  At the beginning you run a SELECT statement to check the state of some piece of data.  A shared lock is requested, the data is read and then the lock is released.  The stored proc may then do some processing and then re-run the original select statement to confirm the state of the data.  Because the shared lock is not held on the original SELECT statement, another transaction could have updated the data while the other statements in the stored proc were running.  When the second SELECT happens the updated data will be returned and this could cause problems with data integrity if further processing, this time with the different data, is required. Here’s a simple demo…

From one connection set the level to READ COMMITTED, begin a transaction and select the data from dbo.IsolationLevelTesting.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT *
FROM dbo.IsolationLevelTesting;

The transaction is left open but the shared locks are released and this returns the following data…

If we check in sys.dm_tran_locks there are no open locks (except the ones at the database level – which is because we are connected to the database).  Now from a second connection run the following to update the data in the table…

UPDATE dbo.IsolationLevelTesting
SET SomeData = 'Updated'
WHERE Id = 1;

Now back in the first connection with the open transaction re-run the SELECT statement and commit the transaction.

SELECT *
FROM dbo.IsolationLevelTesting;
COMMIT TRAN

The following is returned..

The means that the same query executed within a single transaction can return different results.  This is called a non-repeatable read and can be prevented by using the next level up which is aptly named…

REPEATABLE READ

This level prevents non-repeatable reads and offers more consistency than READ COMMITTED by not releasing the shared locks until any open transactions are committed.  We’ll repeat the last demo…

Set the isolation level to REPEATABLE READ, begin a transaction and then select from the table.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT *
FROM dbo.IsolationLevelTesting;

This returns the following…

Now if we query sys.dm_tran_locks as follows…

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id IN (51, 52)
ORDER BY request_session_id

we get the following returned…

We can now see the shared lock on the KEY that is still being held as the transaction is still open.

Now from a second connection if we try to update the data in that row as follows the query will just hang…

UPDATE dbo.IsolationLevelTesting
SET SomeData = 'Repeatable'
WHERE Id = 1;

If we again query sys.dm_tran_locks we get the following…

Now we can see the exclusive lock at the KEY level waiting for the resource to become available.  If we go back to the first connection and re-run the SELECT statement we will still see the original value of Updated returned.  If we now commit the transaction the exclusive lock in the second connection is granted, the data will be updated and if we re-query from the first connection the updated value of Repeatable will be returned.

Obviously as locks are held for a longer time there is going to be more blocking on other requests and this could reduce concurrency.  However, please note, it is only incompatible locks that are blocked.

If we consider the previous example about a stored proc that opens a transaction, reads some data, does some processing and then re-read the same data, we’ve seen how to prevent dirty reads and non-repeatable reads.  But this only takes into consideration existing data.  What if another row is added after the first read that satisfies the criteria of the SELECT statement in the open transaction.  Even though the shared lock still exists in the open transaction a new row can be added and a second read of the same data will result in a different data set begin returned.  This can be demonstrated as follows.  First we need to add some more test data…

INSERT INTO dbo.IsolationLevelTesting (Id, SomeData)
VALUES (2, 'Transaction'),
(4, 'Level');

Now if we use the REPEATABLE READ level, begin a transaction and SELECT from the table as follows…

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT *
FROM dbo.IsolationLevelTesting;

This returns the following results…

The transaction is still open and the shared locks still exist on these three rows.  This means these rows cannot be modified as an exclusive lock would be blocked.

However, from a second connection we can open another transaction which inserts a row into the table as follows…

BEGIN TRAN
INSERT INTO dbo.IsolationLevelTesting (Id, SomeData)
VALUES (3, 'Isolation');

Now if we re-run the SELECT statement from the first transaction we get the following results returned…

If the transaction that did the insert of the row with Id = 3 is rolled back and the SELECT  statement in the first transaction is re-run then only the original three rows are returned.

This is a phantom read and is where over the course of a transaction, two or more identical queries are run and the data sets returned are different.

To combat this we can use the serializable level.

SERIALIZABLE

This is the last of the four levels and is the strongest as far a consistency is concerned but will also cause the most blocking and is more likely to reduce concurrency.  In this isolation level key-range locks are used which stop data being added that satisfies the conditions of a SELECT statement in an open transaction.

We can demonstrate this as follows (roll back any open transaction from previous examples)…

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT *
FROM dbo.IsolationLevelTesting;

If we again query sys.dm_tran_locks we can see the range locks that have been granted…

Now if we try to add a new row to the table (which would satisfy the conditions of the SELECT statement in the open transaction as we are just returning all rows) the query will just hang…

BEGIN TRAN
INSERT INTO dbo.IsolationLevelTesting (Id, SomeData)
VALUES (5, 'Locking');

The data in sys.dm_tran_locks for the INSERT statement is as follows…

If we commit the first transaction the waiting lock will be granted and the insert will happen.

More details on key-range locks can be found here.

Lock Modes and Compatibility

You can browse with Microsoft documentation to view all the different types of locks that SQL Server can take as well as what resources they can be taken on.

There is also a matrix that shows lock compatibility.

Row-Version Transaction Isolation Levels

In addition to the four transaction isolation levels that use locking there are also the SNAPSHOT and READ COMMITTED SNAPSHOT levels.  Rather than placing locks on resources, previous versions of data are stored in tempdb.  I hope to talk about these in a separate post at some point in the future but for further reading please see the MS documentation.

Summary

In most cases the default READ COMMITTED transaction isolation level is the one to use.  It’s always good practise to keep you transaction as short as possible as this will prevent locks being held for a long time.

• 70-762
• Blocking
• Exam 70-762
• Isolation Levels
• Lock Compatibility
• Locking