Blocking and deadlocking are not things you typically want to do deliberately. But sometimes you need to test error handling or prove a point and then you need to do the unusual.

Blocking and Deadlocking are based on lock requests and locking is handled differently depending on the TRANSACTION ISOLATION LEVEL. Because of this the transaction isolation level is going to have a major effect on blocking and deadlocking. The famous (infamous) query hint NOLOCK for example forces a READ UNCOMMITTED transaction isolation level so it doesn’t take or honor certain types of locks, making it much less likely to be blocked (or deadlocked). This does not mean you should use it to avoid blocking! For all of these examples I’m going to assume the default transaction isolation level READ COMMITTED.

Block

Blocking occurs when a process requests a lock on a resource and has to wait because that resource is already being locked by another process.

Creating a block is pretty simple. All you have to do is lock a resource that your process is going to want. Since we aren’t trying to optimize anything here we can just use a query hint to force an exclusive table lock and an explicit transaction to force the lock to be held until we release it.

-- In session 1
BEGIN TRANSACTION
-- Top 1 because I don't want to get much back (or do much work) 
-- and since I'm using WITH (TABLOCKX) I'm going to get an 
-- exclusive table lock anyway. 
SELECT TOP (1) * FROM Table1 WITH (TABLOCKX)

So far all we have is a lock. In order to have blocking we need another process trying to access the same resource.

-- Run in session 2
-- Again a TOP 1 because I don't really care about what I'm 
-- getting back.
SELECT TOP (1) * FROM Table1

At this point session 2 should appear hung and is in fact blocked. We can prove this by looking at the blocking_session_id column of sys.dm_exec_requests.

SELECT blocking_session_id, * FROM sys.dm_exec_requests
WHERE session_id > 50

You can see here that session 53 (session 2 from above) is blocked by session 51 (session 1 from above). You will also notice that session 51 is not on the list from sys.dm_exec_requests. This is simply because the request has ended even though the transaction is still open.

We can take a look at the what’s going on under the hood by doing this:

SELECT request_session_id, request_mode, request_status, 
-- Unfortunately using the object_name function here  
-- causes this query to be blocked too
-- object_name(resource_associated_entity_id, resource_database_id)
Test2012.sys.objects.name AS object_name
-- Yes I know I'm using NOLOCK but I have to or this query get's 
-- blocked as well.  In this case NOLOCK won't hurt us and it's
-- a reasonable use of it.
FROM sys.dm_tran_locks WITH (NOLOCK)
JOIN Test2012.sys.objects WITH (NOLOCK)
ON sys.dm_tran_locks.resource_associated_entity_id = 
Test2012.sys.objects.object_id
WHERE resource_type = 'OBJECT'
  AND resource_database_id = db_id('Test2012')

You can see that one of our queries is actually holding a number of system locks but the important ones for our test are the ones on Table1. Resource_session_id 51 (still session 1 from above) and you will notice that the request_status is GRANT. The request_status for resource_session_id 53 (session 2) is still WAIT and will be until the lock from session_id 51 is released or the request from session_id 53 is canceled.

Deadlock

Deadlocking is similar to blocking, but in this case two (or more) processes are holding locks and each is being blocked by the other. This makes it impossible for the situation to be resolved and SQL Server picks a victim, kills and rolls back that process, and allows the other to move forward.

Since we are using table locks to make this easy we will need more than one table to cause deadlocking. It is possible to deadlock on the same table (and even deadlock with a single session) but that’s more difficult and not important for our simple example here. Leaving the transaction open in the first session, kill the query in the second session then run the following:

USE Test2012
GO
BEGIN TRANSACTION
SELECT TOP 1 * FROM Table2 WITH (TABLOCKX)
GO
SELECT TOP 1 * FROM Table1

We now have a blocking situation again. This time however, we have an additional table involved. As it happens it’s pretty obvious in our example that it’s Table2 but let’s pretend that we don’t know this. In fact this whole example comes from a case where we had a process blocked but needed to deadlock it (we were testing some new error handling). We had no idea what tables were currently being locked beyond the one that I was using to create the block. FYI the process we wanted to deadlock was session 2, the one I was using to deadlock it was session 1.

So I ran the following script again:

SELECT request_session_id, request_mode, request_status, 
-- Unfortunately the object_name function here get's blocked too
-- object_name(resource_associated_entity_id, resource_database_id)
Test2012.sys.objects.name AS object_name
-- Yes I know I'm using NOLOCK but I have to or this query get's 
-- blocked as well.  In this case NOLOCK won't hurt us and it's
-- a reasonable use of it.
FROM sys.dm_tran_locks WITH (NOLOCK)
JOIN Test2012.sys.objects WITH (NOLOCK)
ON sys.dm_tran_locks.resource_associated_entity_id = 
Test2012.sys.objects.object_id
WHERE resource_type = 'OBJECT'
  AND resource_database_id = db_id('Test2012')

And lo and behold a lock on Table2 is also being held by session 2 (53). So now to create a deadlock all I need to do is go into session 1 (51) and try to query Table2.

SELECT TOP (1) * FROM Table2

And on session 2 I received the following error:

Msg 1205, Level 13, State 56, Line 1

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

One last important concept here. We want to be certain that session 2 is the one that get’s the error. Typically SQL makes that decision. I believe it’s whatever transaction it feels can be re-run the quickest but I’m not certain. So in order to force the issue we add a simple piece of code to session 1. SET DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY 10

By setting the deadlock_priority to the maximum (10) it forces any other session with a deadlock priority lower to be the victim. I do want to point out that like many other settings in SQL Server this should not be used to try to out think SQL. I can understand having the thought: “If I set my process to a deadlock_priority of 10 then I don’t have to worry about deadlocks”. I really do. However as soon as you start this type of behavior then you open a gate and other people are going to start doing the same thing. And very quickly you are right back where you started from. This isn’t to say that it shouldn’t ever be used. If you have a process that actually is more important than the average ad-hoc query and you are having a problem with deadlocks this might be a good solution. Of course so would speeding your process up, making shorter transactions etc (all the normal solutions to blocking and deadlocking).

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: blocking, deadlocking, microsoft sql server, Transaction Isolation Levels, transactions