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.