Locks, blocking, and deadlocks

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Request an update lock in a short transaction and reason about other sessions.

Run the example

BEGIN TRANSACTION;
SELECT CustomerID, FirstName FROM dbo.Customers WITH (UPDLOCK)
WHERE CustomerID = 1;
ROLLBACK TRANSACTION;

Read it step by step

1. UPDLOCK requests an update lock while reading the selected customer.
2. The explicit transaction holds the lock until rollback.
3. A second session may wait for conflicting locks; a deadlock requires a cycle of waits and one victim, neither of which this one-session result demonstrates.

Check the result

ROLLBACK releases the lock; no row is changed.

Compare the behavior

Blocking is a one-way wait for another transaction to release a conflicting lock. A deadlock is a cycle: for example, session A holds row 1 and waits for row 2 while session B holds row 2 and waits for row 1. SQL Server chooses a victim and rolls back that victim transaction; the application must handle the error and may retry the complete unit of work. The example below demonstrates blocking, not a deadlock.

Explore it further

Two-session blocking check on a disposable instance: in session A, run BEGIN TRANSACTION; SELECT CustomerID FROM dbo.Customers WITH (UPDLOCK) WHERE CustomerID = 1; and leave A open briefly. In session B, run the same SELECT with UPDLOCK on CustomerID 1. B should wait for the incompatible update lock. Run ROLLBACK TRANSACTION in A to release it, then confirm B finishes. A deadlock needs a cycle of waits involving at least two resources; this check demonstrates blocking only. Do not leave either session inside an open transaction.

Practice

Briefly request an update lock on CustomerID 2 and then release it.

BEGIN TRANSACTION;
SELECT CustomerID, FirstName FROM dbo.Customers WITH (UPDLOCK)
WHERE CustomerID = 2;
ROLLBACK TRANSACTION;