Investigating Locking and Blocking in SQL Server

Locking (and therefore blocking) is a fundamental part of how SQL Server works, by protecting the data integrity of resources and objects as it queries and modifies them. When SQL Server locks an object – be it a table, a page, a row or even a single key – it will block other processes from accessing it if it feels data integrity may be compromised.

That being said, locking can be problematic if a resource is blocked for an extended period of time. This causes query timeouts, system slow-down and often presents itself to users as application performance issues or even system crashes. Excessive blocking needs to be investigated, and this article will explain very simply how to do that.

Setting up a test table

First, we’ll create a simple two column table that will act as our locking / blocking playground.

CREATE TABLE LockDemo (
ID int IDENTITY(1,1) NOT NULL,
Value varchar(50) NOT NULL
)
GO

INSERT INTO LockDemo (Value) VALUES ('Some value')
GO 1000

The system procs we’ll use

To understand what’s locked and what’s blocked, we’ll use two system stored procedures. These procedures are:

Sp_who2 – Lists all active sessions, and crucially tells us if a sessions is being blocked, and if so which SPID is blocking it.

Sp_lock – Again, this gives us lock information as well as important information about the granularity of the lock, i.e. which part of the lock hierarchy is affected.

Some fundamentals

Before we look at locks and blocks in action, it’s important to understand some basic fundamentals of SQL Server locking.

In really basic terms, there are two types of lock.

Shared Locks, and Exclusive Locks.

Shared Locks are generally acquired by reader sessions – such as SELECT statements. SELECT statements are willing to share their ‘locked’ resource with other shared lock requests.

Exclusive Locks are generally acquired by writer sessions, also known as INSERT, UPDATE and DELETE statements. Exclusive locks need exclusive access to the object, and will not share it once locked.

As stated above, shared locks, such as those acquired by SELECT statements are compatible with each other. They don’t block each other. Shared locks are incompatible with exclusive locks which modification statements seek to acquire. They block these statements, and make them wait until the lock is released.

Exclusive locks are not really compatible with any other lock, unless the isolation level of the session is set to read uncommitted data.

For more information on Lock Compatibility, see the following excellent SQL Hint article:

SQLHint Lock Matrix

Lock hierarchy

Locks take place in the following hierarchy:

  • Database
    • Table
      • Page
        • Row
          • Key / Index

A SELECT statement will almost always try to acquire a Shared Lock at a table level. The level on which a modification statement will try to acquire an exclusive lock depends on the type of transaction involved – for example, per transaction, an update statement relating to a single row will seek an exclusive row lock, whereas an update statement relating to a whole table will seek an exclusive table lock.

In order for an update statement to be granted a row based lock, it must first have Intent Exclusive locks granted to it on the levels above row in the hierarchy – this means there is an intent to lock something lower down in the hierarchy. If an intent lock can’t be granted, the transaction will wait until it can before moving down the hierarchy with its lock requests.

Transaction Isolation Level

Isolation Levels can be set per session in SQL Server to tell SQL Server to grant/deny locks differently to reader sessions, for example, by allowing the query to read uncommitted rows which an insert statement is still inserting. This is out of scope of here but there are some great articles on MSDN relating to Transaction Isolation levels.

Locks and blocks in practice

Okay, let’s simulate a blocking scenario.

We’ll simulate a long running query that will seek and acquire a shared lock on our LockDemo table. We’ll then try to execute an update statement and monitor the behaviour using the sp_who2 and sp_lock system stored procedure.

The Long Running Query

BEGIN TRANSACTION
SELECT *
FROM LockDemo WITH (HOLDLOCK)
--COMMIT

First we open a transaction, then use the HOLDLOCK hint to ensure the lock is held on the table while we investigate. We don’t COMMIT the transaction yet because we want to simulate an open transaction that will hopefully create a blocking scenario.

If we run the system stored procedure, sp_who2, we can see our statement with SPID 55 waiting for a command – either COMMIT or ROLLBACK.

SPID

If we then run the system stored procedure, sp_lock, we can see what SPID 55 is actually doing.

splock1

We can clearly see that spid 55 (our query) has been granted shared locks on both the database and the table object.

This means that any other transactions seeking shared locks on these objects will also be granted.

But what happens if we want to run an update statement, as below, that we already know will request an exclusive lock?

The Update Statement

BEGIN TRANSACTION
UPDATE LockDemo SET Value = 'Some other value' WHERE ID = 1
--COMMIT

When we run our query in a new query window (we need to generate another SPID), we can see that it simply hangs.

So, what’s going on behind the scenes?

If we run sp_who2 again we can clearly see our UPDATE statement which is in SUSPENDED mode. In other words, it has been suspended until ‘some other’ process has finished. In the BlkBy column, it actually tells us what that process is. Yep, it’s our SELECT statement, good old SPID 55, that we previously forced to hold its lock.

SPID2

Let’s see what’s going on in the sp_lock procedure now:

splock2

The update SPID, 56 is seeking an Intent Exclusive lock on the table. Again, that means it intends to acquire an exclusive lock on some element of the hierarchy below a table level – a row – in this example.

You can see the status is WAIT, because there is a shared lock held by our SELECT statement (SPID 55) on the table, and a Shared Lock (S) is not compatible with an Intent Exclusive (IX) lock.

If we go and COMMIT our SELECT statement, it should free up the resource.

Let’s check the sp_lock procedure again.

splock3

We can now see our UPDATE statement has been granted an IX lock on the table it was waiting for earlier, and also on the page. Finally it has sought and acquired a full exclusive (X) lock on the RID (Row ID) itself.

That lock will be held until the transaction finishes. If we COMMIT that transaction, the locks will clear and the UPDATE will persist to the table.

Summary

We have demonstrated how locks work by using some dummy transactions and viewing the activity in the sp_who2 and sp_lock stored procedure. But how does this help us in real world scenarios?

Well it helps us track the transactions and understand what processes are causing blocks and therefore causing performance issues and time-outs. We can work out what are query performance issues, application issues, and bad database design just by following the transaction flow.

It also lets us catch bad transactions by identifying the SPID’s and issuing KILL commands to the database to remove locks.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s