Articles

SQL Server Deadlock Interview Questions: Read the Graph and Choose a Fix

Practice SQL Server deadlock interview questions with XML graph analysis, lock ordering, victim selection, row-versioning limits, and safe retry scope.

A SQL Server deadlock interview can turn a definition into a diagnosis exercise. A transaction receives error 1205, the application team proposes retries, and someone suggests adding NOLOCK. Your job is to explain the dependency cycle, choose a change that addresses it, and preserve the business operation when an attempt is rolled back.

Identify what each participant owns, what it requests, and why that request cannot proceed. The victim is the transaction SQL Server sacrificed to restore progress; it is not automatically the transaction that needs rewriting.

This article concentrates on SQL Server evidence and repair decisions.

Evidence boundary: Microsoft documentation supports official engine behavior. The XML, inventory example, and repair reasoning are original teaching material. The graph's references, wait edges, and retry arithmetic are checked locally, but the proposed T-SQL lab has not been executed on SQL Server. The XML is synthetic, not a captured production report. No candidate report is used to predict employer questions. Sources were checked on September 15, 2026.

Blocking, deadlock, and timeout are different observations

Official engine fact: ordinary blocking means a request waits for another task's resource. A deadlock requires a cycle of dependencies. The engine detects the cycle and selects a victim so other work can proceed. Resources can include locks, worker threads, memory, and parallel-query resources. SQL Server deadlocks guide.

A long-running blocker can cause severe latency without forming a cycle. Likewise, an application timeout only says its deadline expired; it does not establish that the database detected a deadlock. Ask for the actual error and graph rather than treating every stalled request as the same failure.

Official error behavior: error 1205 means the victim's transaction was rolled back. MSSQLSERVER_1205. That rollback boundary determines what a retry must repeat.

Where would you obtain the graph?

Official collection path: on SQL Server and Azure SQL Managed Instance, the built-in system_health Extended Events session records detected deadlocks, including their graphs. Its storage has bounded retention, so preserve the relevant event promptly. Azure SQL Database does not have that same built-in session. Using system_health.

In SSMS, inspect the session's event-file target through the Extended Events viewer, filter for the relevant deadlock event, and correlate its timestamp with the application failure. Preserve the XML, not only a cropped screenshot. For Azure SQL Database, use its documented database-scoped collection procedure rather than assuming an instance-level session exists. Azure SQL deadlock collection.

Collect the application operation, transaction boundaries, relevant parameters, and execution plans alongside the event. The graph describes the deadlock moment, not a complete history of everything each transaction did.

Read this small graph before suggesting a fix

Our synthetic example has two sessions operating on inventory items 10 and 20. The short resource identifiers below are teaching labels. Real key resources use engine identifiers and hashes; do not assume a real hash directly reveals an application row ID.

<deadlock>
  <victim-list>
    <victimProcess id="processB" />
  </victim-list>
  <process-list>
    <process id="processA" spid="51"
             priority="0" logused="600" />
    <process id="processB" spid="52"
             priority="0" logused="400" />
  </process-list>
  <resource-list>
    <keylock id="item10" mode="X">
      <owner-list>
        <owner id="processA" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processB" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock id="item20" mode="X">
      <owner-list>
        <owner id="processB" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processA" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

This abbreviated XML omits object metadata, execution stacks, timestamps, and other attributes. It is a reading exercise, not a complete SSMS export or a promised rendering fixture.

Read it in three passes. First, map processA to session 51 and processB to session 52. Second, inspect each resource's owners and waiters. Third, draw an arrow from a blocked waiter to the incompatible owner.

For item 10, B requests an update lock while A owns an exclusive lock: B waits for A. For item 20, A requests an update lock while B owns an exclusive lock: A waits for B. Those two edges close the cycle.

Official lock fact: an update lock is not an exclusive lock. Update locks can coexist with shared locks, but only one update-lock holder is allowed on a resource; an update eventually needs an exclusive lock to modify the row. The exclusive owners in this example block the other sessions' requests. Locking and row versioning.

Our local checks confirm the two edges. Removing either wait breaks this two-node cycle; changing a conflicting owner/request pair to a compatible pair also removes that dependency. These checks validate the teaching graph, not every SQL Server resource or lock-conversion scenario.

Why was B chosen, and does that identify the bug?

Official selection rule: SQL Server considers deadlock priority first. With equal priority, it prefers the transaction that is cheaper to roll back, using logged work in the comparison. SET DEADLOCK_PRIORITY.

In our synthetic fields, both priorities are zero and B has less logged work: 400 versus 600. That makes B the expected victim under the stated rule. The numbers were chosen to illustrate the decision; they were not measured from the inventory lab.

Changing B's priority can change which operation loses an attempt. It does not remove either wait edge. Priority may express an intentional preference between workloads, but calling it the root-cause fix would leave the same cycle available to recur.

Also resist blaming the statement visible in the victim's input buffer. Official diagnostic limitation: an earlier statement in a multi-statement transaction may have acquired a contributing lock without appearing in the captured input buffer. Inspect the procedure or application transaction as well. Graph and input-buffer analysis.

Reconstruct the access order with a two-session lab

Use an isolated practice database and two connections. Create dbo.SampleDeadlockLab with an integer primary key ItemId and integer Qty, containing (10,100) and (20,100). The fictional operation reserves one unit of both items; both decrements must commit together.

The proposed schedule is deliberately manual:

In A, begin a transaction and decrement item 10. Leave the transaction open.
In B, begin a transaction and decrement item 20. Leave it open.
In A, attempt to decrement item 20. This request should wait for B.
While A waits, in B attempt to decrement item 10. This completes the dependency cycle.

The update form is:

UPDATE dbo.SampleDeadlockLab
SET Qty = Qty - 1
WHERE ItemId = 10;

Use item 20 for the corresponding steps. Execute only the indicated step in each window; running one whole transaction before starting the other does not reproduce the overlap.

Predicted lab outcome: one attempt becomes the victim. Commit the survivor, then inspect both quantities from a separate connection. If only the survivor committed, both should be 99. Clean up any remaining open lab transaction before another run. Do not assume the victim or exact lock resource will match our synthetic XML.

Current engine caveat: optimized locking changes lock lifetimes and can represent dependencies through transaction-ID resources. A real report can contain xactlock elements rather than only the traditional key-lock pattern. Check the engine and database configuration before applying a memorized interpretation. Optimized locking.

Choose the repair that removes this cycle

For this two-item operation, make both code paths acquire the items in the same order: 10, then 20. If A already owns item 10, B waits before obtaining item 20. A can finish its second update and commit, allowing B to continue. There is blocking, but the illustrated reverse dependency no longer forms.

This reasoning assumes the two explicit point updates and their stated resources. A multi-row update, trigger, foreign key, or additional index maintenance can introduce other accesses. Sorting an application list is not proof that every database operator acquires every lock in that order.

If the graph instead implicates a broad scan or lookup path, inspect the execution plan and a selective index that could reduce unnecessary access. Official tuning guidance: better access paths can reduce lock contention, but index changes also have write and maintenance costs. Index design guide.

State the specific edge you expect the change to remove. Then rerun overlapping operations with representative parameters. Compare deadlock patterns, successful operations, retry attempts, latency, and correctness. A single run without error is weak evidence when the original failure required a rare interleaving.

A conversion deadlock needs a different explanation

Not every cycle requires opposite row order. Consider two transactions that both retain shared locks on one key, then each requests an exclusive lock. Each transaction's existing shared lock blocks the other's upgrade. The resource is the same; the requested lock mode changes.

Do not draw an edge for a compatible pair or treat a transaction's own existing lock as the other participant.

For a read-then-update design, an appropriate UPDLOCK read can serialize competing update intentions earlier. Official hint behavior: update locks taken through this hint are held until the transaction completes. UPDLOCK documentation. That is a targeted trade-off in concurrency, not protection against every cycle involving additional resources.

Why NOLOCK and row versioning are not blanket fixes

Official hint behavior: NOLOCK permits dirty reads and can expose missing or duplicate rows. It also does not mean “no locks”: schema-stability locks still apply. Table hints. It cannot make this pair of conflicting inventory writes safe.

Official isolation distinction: row-versioned reads can reduce reader/writer blocking, but writes still require concurrency control. RCSI and snapshot isolation also provide different read-consistency boundaries. Transaction locking and row versioning.

For our write/write cycle, proposing RCSI without explaining the remaining write dependencies is incomplete. For another workload, versioned reads may be a useful design choice, provided the application still preserves its invariants. Do not weaken a required consistency guarantee solely to make a deadlock counter smaller.

Retry the complete business attempt

Suppose A commits its paired decrement and B was rolled back. Both quantities are now 99. A correct retry of B performs both decrements in a new transaction, producing 98 and 98. If B retries only its final item-10 statement, the result becomes 98 and 99: the reservation is incomplete.

That arithmetic is an original counterexample to statement-only retry. Earlier reads and business decisions from an aborted attempt may also need to be repeated against current data.

A practical retry policy recognizes error 1205, bounds attempts and total time, introduces jittered delay, and reruns the transaction as a unit. Record exhaustion as a failure instead of hiding it in an endless loop. Keep external side effects outside the retried unit or protect them with an explicit idempotency design.

Official error-handling detail: T-SQL TRY...CATCH supports structured error handling, and XACT_STATE() distinguishes no active transaction, a committable transaction, and an uncommittable one. An uncommittable transaction cannot be repaired by committing or rolling back to a savepoint. TRY...CATCH and XACT_STATE.

Use those observations in the actual driver and transaction framework. Do not assume every caught database exception leaves the same state or merits the same retry.

Five related questions to practice

Practice question	Focus
Identify and fix deadlock in locked code	Wait cycles
Explain deadlock cases and how to prevent them	Lock order
Explain ACID and isolation levels	Data rules
Reason About Indexes, Kafka, and Database Locking	Retry scope
Optimize a SQL query plan tree	Access paths

Explain the cycle, defend one targeted change, and name the evidence that would show the change failed.