Dan Guzman Blog

Blocking is not Deadlocking

After interviewing several candidates for a database position, I was surprised to find that many didn’t know the difference between a block and deadlock.  Many used the terms interchangeably because they thought both were synonymous.  Although missing this interview question didn’t necessarily disqualify those candidates, it certainly didn’t help their chances.  I decided I’d contrast blocking and deadlocking here to help those who might not grasp the difference.

Blocking

Blocking is a necessary side effect of using locks to control concurrent resource access.  A lock is either compatible or incompatible with other locks depending on the lock mode.  Before a lock is granted, SQL Server first checks to see if an incompatible lock on the same resource exists.  If not, the lock request is granted and execution proceeds.  When the requested lock mode is incompatible with an existing lock mode on the same resource, the requesting session is blocked and waits until:

1)      The blocking session releases the lock

2)      The LOCK_TIMEOUT threshold of the blocked session is reached (indefinite by default)

3)      The blocked client cancels the query

4)      The blocked client times out (30-second default in ADO/ADO.NET, technically the same as #3)

The SQL Server Books online is an excellent resource for a more detailed description of lock modes, compatibility and related information so I won’t repeat that information here.  I strongly recommend that serious database professionals peruse the Locking in the Database Engine topic of the SQL Server Books Online, especially the following subtopics:

·         SQL Server 2005 Books Online: Lock Modes

·         SQL Server 2005 Books Online: Lock Compatibility

·         Lock Granularity and Hierarchies

Deadlocking

A deadlock is basically a special blocking scenario where 2 sessions are waiting on each other (directly or indirectly).  Neither can proceed so both will wait indefinitely unless a timeout or intervention occurs.  Unlike a normal blocking scenario, SQL Server will intervene when a deadlock situation is detected and cancel one of the transactions involved.  The locks of the cancelled transaction are then released so the other blocked session can proceed.  SQL Server chooses the transaction that is the least expensive to rollback as the deadlock victim by default.  If SET DEADLOCK_PRIORITY has been issued, SQL Server chooses the one with the lowest priority as the victim.

A deadlock always starts as a normal block with one session waiting while the other continues.  It is only when the running session is later blocked by the waiting session that the deadlock occurs.  The scenario is easily illustrated by executing queries from 2 different SQL Server Management Studio query windows.  First, create a table with insert test data:

USE tempdb;

CREATE TABLE dbo.DeadlockExample

(

      DeadlockExampleKey int NOT NULL

            CONSTRAINT PKDeadlockExample PRIMARY KEY CLUSTERED

);

INSERT INTO dbo.DeadlockExample (DeadlockExampleKey) VALUES(1);

INSERT INTO dbo.DeadlockExample (DeadlockExampleKey) VALUES(2);

 

Run the following from window 1:

USE tempdb;

 

BEGIN TRAN;

 

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 3

WHERE DeadlockExampleKey = 1;

 

Then run this script from window 2:

USE tempdb;

 

BEGIN TRAN;

 

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 4

WHERE DeadlockExampleKey = 2;

 

--this UPDATE will be blocked by session 1

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 5

WHERE DeadlockExampleKey = 1;

 

You can view session and lock info during this blocking episode by executing sp_who2 and sp_lock from a new SSMS window or you can use the Activity Monitor from the SSMS Object Explorer GUI.  A query against the sys.dm_tran_locks DMV and sys.partitions catalog view will also show the table involved in this blocking episode:

SELECT

      OBJECT_NAME(p.object_id), *

FROM sys.dm_tran_locks AS dtl

JOIN sys.partitions AS p ON

      dtl.resource_associated_entity_id = p.hobt_id;

 

Finally, run the UPDATE below from window 1:

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 6

WHERE DeadlockExampleKey = 2;

 

This will cause a deadlock between the sessions because session 1 is waiting for key 2 (exclusively by session 2) and session 2 is waiting for key 1 (exclusively locked by session 1).  SQL Server will choose one of the sessions as a victim and that session will get rolled back and receive error 1205:

Msg 1205, Level 13, State 51, Line 1

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

 

See the Deadlocking topic Books Online for more information on deadlocks as well as information on diagnostic tools. 

Legacy Comments


Jason
2008-07-26
re: Blocking is not Deadlocking
You are much kinder then me. Anytime I hire a mid to upper level DBA I expect them to know this question and will not recommending hiring them if they don't. I understand a Junior DBA might not know the answer, so for them it is usually bonus points if they do.

Lou
2008-08-05
re: Blocking is not Deadlocking
I'm a newbie so forgive my ignorance, but I don't understand why there's a lockup after running

UPDATE dbo.DeadlockExample
SET DeadlockExampleKey = 5
WHERE DeadlockExampleKey = 1;

why wouldn't it simply do nothing say "0 rows affected" at that point?

Lou

Dan Guzman
2008-08-05
re: Blocking is not Deadlocking
> WHERE DeadlockExampleKey = 1;
> why wouldn't it simply do nothing say
> "0 rows affected" at that point?

Hi, Lou.

The reason that this UPDATE is blocked is because the first window has not yet commited the outstanding UPDATE to the same row. If the first window committed the transaction before this UPDATE, you would instead. get the "0 rows affected" message.

--
Dan

Terry Winkle
2010-11-11
re: Blocking is not Deadlocking
This is nice post which I was awaiting for such an article and I have gained some useful information from this site. Thanks for sharing this information.

treatment of acne | home remedy acne | home remedies acne treatment | natural remedies for acne | get rid of acne

Sushant Sybase DBA
2012-04-24
re: Blocking is not Deadlocking
Thanxxx it helped me to clear my concept ;)

RajaV
2012-09-27
re: Blocking is not Deadlocking
Many many special thanks to Mr. Dan Guzman for this simple and wonderful explanation of Blocking and Deadlocks!