Distinguish deadlock 1205 from lock timeout

Understand the likely causes

• Deadlocks involve cyclic ownership and requests, often from inconsistent resource access order or broader access paths. Ordinary blocking has a waiter whose owner may still continue.
• Long transactions, missing selective access, and transactions left open after failures can extend blocking. A timeout setting limits patience; it does not remove the ownership cause or make the transaction clean.

Record the exact failure boundary

Capture error number, UTC time, operation key, command identity, and whether the driver confirmed rollback. Keep parameter values and query text in restricted diagnostics when necessary. Do not retry every timeout with the deadlock policy. A cancelled command can leave transaction work requiring explicit cleanup, while a connection lost near COMMIT can leave the business outcome uncertain.

Inspect current waits without intervention

An authorized diagnostic session can read the current requests below. The output is a changing snapshot, not a history of the failure. A head blocker may be sleeping with an open transaction and therefore absent from active requests. Investigate session and transaction context through the approved runbook. The current session's LOCK_TIMEOUT value does not reveal another session's setting.

SELECT @@LOCK_TIMEOUT AS this_session_lock_timeout_ms;
SELECT session_id,blocking_session_id,wait_type,wait_time,wait_resource
FROM sys.dm_exec_requests
WHERE session_id<>@@SPID
ORDER BY blocking_session_id,session_id;

Use a graph to prove the cycle

For 1205, ask an authorized administrator for existing xml_deadlock_report evidence, normally captured by system_health. Trace each owner, requested resource, and lock mode until the cycle closes. Restrict captured SQL text appropriately. Finite retention means missing evidence is not proof of no deadlock. Do not create a broad high-volume trace or reproduce a deadlock on a configured application server.

Apply a transaction-aware remedy

For an evidenced access-order cycle, align resource order and shorten the transaction; inspect indexes if unnecessary rows are touched. After confirmed 1205 rollback, retry the complete eligible business transaction with bounded attempts and backoff. For 1222 or cancellation, let the owning boundary inspect state and clean up before reuse. For uncertain commit, reconcile by the original durable operation key. Never repeat an external payment merely because a SQL command failed.

Keep it from recurring

• Document transaction ownership, consistent access order, timeout cleanup, bounded retries, and operation idempotency. Keep existing diagnostic capture healthy and assign an incident owner who can review graphs and protect sensitive evidence.