SQL Deadlock and Timeout: Find the Contention Instead of Extending the Wait

Error 1205 is a deadlock: the engine detected a cycle of locks and picked your transaction as the victim. Error 1222 is a lock wait that expired. Error -2 comes from the client, not the server, and means your application gave up. All three surface in a log as "a database error", and only one of them is actually a cycle.

Three failures that look identical in an application log

What separates them is who gave up first: the engine, the lock manager, or your own code.

The exact text of 1205 is Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The text of 1222 is far shorter: Lock request time out period exceeded.

Why does raising the timeout make things worse?

Because it does not touch the cause. A -2 is the symptom of an operation taking longer than it was allotted, whether from blocking or from a poor execution plan. Going from 30 seconds to 300 changes exactly one thing: how long the user stares at the screen before receiving the same outcome.

• A waiting connection occupies a slot in the connection pool. When the pool is exhausted, healthy requests start failing too.
• A waiting transaction still holds the locks it already acquired, so a longer block builds a longer queue behind it.
• SET LOCK_TIMEOUT defaults to -1, meaning wait indefinitely. A value of 0 means do not wait at all.
• In .NET, CommandTimeout defaults to 30 seconds and a value of 0 removes the limit entirely. Never set 0 on a code path a user is waiting on.

The practical rule: a short timeout is a measuring instrument. It is what tells you contention exists. Raising it switches the instrument off.

Where the deadlock graph already lives

SQL Server runs an Extended Events session called system_health by default, and it collects xml_deadlock_report events. If a deadlock happened in the last few days, its graph is very likely already stored on the instance - you do not need to enable new tracing and wait for a recurrence.

The graph has three parts: victim-list with the identifier of the chosen victim, process-list with every process involved, and resource-list with the resources they contended over. Saving the XML with an .xdl extension and opening it in SSMS renders it as a diagram.

Be aware that the event file target is bounded in size and file count, so deep history is not retained indefinitely. If your deadlock happens once a fortnight, export the graph the moment it is captured.

How do you find contention while it is happening?

A recurring fault is solved far faster when you catch it live:

1. Query sys.dm_exec_requests for rows where blocking_session_id is not zero. That identifies who is blocking whom.
2. Cross-reference sys.dm_os_waiting_tasks to see precisely what the wait is on.
3. Query sys.dm_tran_locks filtered to request_status of WAIT. Those are the lock requests that have not been granted.
4. Read resource_type and request_mode on the same row: together they tell you whether a single row, a page or an entire table is locked.
5. Find the blocking session and check what it is running and when its transaction opened.
6. Record the session ids and the statements before you release anything. After a KILL, that evidence is gone.

What actually fixes contention

Microsoft's own documentation states that deadlocks are in most cases application-related and require code changes. In order of payoff:

• Access tables in the same order in every transaction. If everyone touches Table1 then Table2, blocking may still occur but a cycle cannot form. This is Microsoft's first recommendation and the cheapest to implement.
• Shorter transactions. Do not open a transaction before calling an external service, and never hold one open while a user is thinking.
• A suitable index. Scanning a whole table locks far more than it needs to. An index that narrows the scan directly narrows the locked surface.
• Readers that do not block writers. ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON moves READ COMMITTED transactions onto row versions instead of locks. Note that enabling it requires no active connections to the database other than the one running the statement, so it belongs in a maintenance window.
• A controlled retry, on 1205 only. A deadlock victim has been rolled back, so a rerun usually succeeds. Add a short pause before retrying and cap the number of attempts.

When is SET DEADLOCK_PRIORITY the right tool?

SET DEADLOCK_PRIORITY does not prevent deadlocks. It only decides who gets terminated when one occurs. The values are LOW, NORMAL and HIGH, mapping to -5, 0 and 5 on a range from -10 to 10. NORMAL is the default.

When both sessions carry the same priority, SQL Server chooses whichever is cheaper to roll back, measured by the number of log bytes written to that point. That figure appears in the deadlock graph as Log Used.

The one legitimate use: a background job that can be rerun quietly is given LOW so it never kills a real user's operation. Setting HIGH so "my code stops failing" simply moves the pain onto somebody else.

What to log so the next occurrence is diagnosable

• The exact error number, not just "database error".
• A timestamp with seconds, so you can line it up against the deadlock graph.
• The business operation and its parameters, not only the SQL text.
• The actual wait duration before the error was raised.
• The session id, where the client can see it.

Without the error number and a timestamp, every after-the-fact diagnosis is a guess. It is the same conclusion you reach with any integration fault that was swallowed without a log entry.

Related: common Priority API errors, a recovery plan for a corrupted MDB file, what a database is.

Sources

• Microsoft Learn - MSSQLSERVER_1205
• Microsoft Learn - MSSQLSERVER_1222
• Microsoft Learn - Troubleshoot Timeout Expired errors