SQL Server Locking & Blocking

I’m sure all of you who work with SQL Server systems will have experienced the frustration of trying to retrieve a set of records only to find that someone is updating/deleting/inserting one or more records that are matched by your query, and locks prevent you from accessing part or all of your record set.

The default behaviour for SQL Server is that as soon as the Storage Engine encounters an Exclusive Lock  it blocks other sessions that try to retrieve the record affected and those sessions are left waiting for the locks to be released. Eventually the Exclusive Locks will be released and the blocked sessions will move on and retrieve the records. I say that this is the default behaviour, and that is because there are a number of factors that may alter this default behaviour, for example:

• The session’s Isolation Level

• Query Hints

• Transaction Duration

There are ways of ignoring locks and returning either all records including the locked ones or all records except the locked ones.

In this article we meet the READPAST hint which enables all records except the locked records to be returned to a querying session.

Let’s take a look at an example. We will update a single record in the Production.Product table in the AdventureWorks2014 database.

• SQLQuery1 (session 54 on the right) has updated a single record within a transaction and the transaction has not yet been committed.

• SQLQuery2 (session 55 on the left) is attempting to query all 504 records from the Production.Product table, but has been blocked. You can see that the right hand query has a green tick in the bottom left hand corner indicating it completed successfully, but the blocked query has a spinning circle indicating that the query is still executing even though no rows have been returned.

The blocked query will remain like this until the updating session  completes the active transaction thus releasing the exclusive locks it currently has. Here is the locking information for the two sessions:

The sp_lock output in the above screen shot shows that:

• Session 54 has an exclusive lock (X) on an index key (KEY) which is actually the product record as the index affected is a clustered index.

• Session 55 has requested a shared lock (S) on the same record (we can see this from the Resource column) and it is in a WAIT state.

We could also see this locking and blocking evidence through Activity Monitor, Dynamic Management Views (DMVs), a SQL Profiler trace or Extended Events Session. 

If we rollback or commit the transaction in session 54 then session 55 will proceed and receive all 504 product records:

The READPAST Query Hint

The READPAST hint can be placed on the SELECT statement in session 55 to tell the session to simply skipmover any locked records encountered.

We can see from the screen shot that 503 rows have been returned rather than the full 504. We can also see that the record with ProductID 4 is missing from session 55’s result set.

The session has read past the locked record.

Here is the lock information:

The exclusive lock is held by session 54, but there is no key level shared lock request for session 55, hence no blocking occurs and the query execution completes.

If you would like to find out more about SQL query tuning and SQL Server performance why not take a look at the course outline for our SQL Server Performance & Tuning course.

Or if you have any questions email us at info@ptr.co.uk.