Deadlock vs Blocking

Sometimes our end users may not know the difference between a deadlock and blocking. In fact, there are some that may use these two interchangeably, thinking they are the same thing. A quick demonstration is often helpful to show the difference. Here's a simple example that uses two global temp tables.

NOTE: I want to stress that this is merely a simple example, and not a deep dive or exhaustive article. Books have been written on concurrency, including one by Kalen Delaney (b|t). It's free, and highly recommended.

To start, create the temp tables and add some sample data.

CREATE TABLE ##Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

INSERT INTO ##Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO

CREATE TABLE ##Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO ##Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

Now open two empty query windows in SSMS. Place the code for session 1 in one query window and the code for session 2 in the other query window. Then execute each of the two sessions step by step, going back and forth between the two query windows as required.

BEGIN TRAN;

UPDATE ##Employees
SET EmpName = 'Mary'
WHERE empid = 1;

BEGIN TRAN;

UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1;

UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1;
--<blocked>

After step five, we see the UPDATE never completes. This is an example of blocking. The dynamic management view dm_exec_requests confirms this:

SELECT r.session_id, r.blocking_session_id, r.command, 
  r.wait_type, r.wait_time, r.open_transaction_count
FROM sys.dm_exec_requests r
WHERE r.blocking_session_id > 0

Don't cancel the query from step five. Let it continue running. Now let's proceed to step 6: execute the UPDATE statement in the second SSMS session window.

UPDATE ##Employees
SET phone = N'555-9999'
WHERE empid = 1;
--<blocked>

A deadlock results; one transaction finishes and the other transaction is aborted and error message 1205 is sent to client. It should look similar to this:

Msg 1205, Level 13, State 45, Line 7
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Depending on your audience (and how talkative you're feeling), you may want to have a conversation about concurrency and transactions. Absent that, this quick demo and the "show me, don't tell me" approach can be very effective in showing the difference between deadlocks and blocking.