How to Check and Resolve Deadlocks in SQL Server (Error 1205)

Understanding the Error: Transaction (Process ID) was deadlocked

When troubleshooting concurrency issues in Microsoft SQL Server, one of the most critical messages a Database Administrator or application developer can encounter is Error 1205:

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

A deadlock in SQL Server occurs when two or more transactions have mutually blocking dependencies on each other. Transaction A holds a lock on Resource 1 and requests a lock on Resource 2. Simultaneously, Transaction B holds a lock on Resource 2 and requests a lock on Resource 1. Neither can proceed. SQL Server's Lock Monitor background thread periodically checks for these circular dependencies. When it detects a cycle, it intervenes by choosing one transaction as the "victim" (typically the one that is least expensive to roll back), aborts it, rolls back its changes, and returns Error 1205 to the client application.

It is crucial to differentiate between blocking and deadlocking. Blocking is a standard, temporary state in a relational database where one process waits for another to finish and release a lock. A deadlock is a permanent, unresolvable circular blocking situation requiring intervention by the database engine.

Step 1: Diagnose and Check for Deadlocks in SQL Server

To resolve a deadlock, you must first capture the deadlock graph to understand the participating processes and resources.

1. The system_health Extended Event Session (Recommended)

Starting with SQL Server 2008, the system_health Extended Event session runs by default in the background. It automatically captures the xml_deadlock_report without any user configuration. You can extract this XML data directly from the ring buffer using a T-SQL query (provided in the code block below). The resulting XML contains the deadlock graph, detailing the victim, the queries running, and the specific indexes or tables locked.

2. Custom Extended Events Session

For high-transaction environments where the system_health ring buffer might roll over too quickly, creating a dedicated Extended Events session is best practice. This allows you to write deadlock reports to a persistent file.

CREATE EVENT SESSION [TrackDeadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\SQLLogs\Deadlocks.xel')
WITH (STARTUP_STATE=ON);
GO
ALTER EVENT SESSION [TrackDeadlocks] ON SERVER STATE = START;
GO

3. Trace Flag 1222

For older systems or DBAs who prefer log-based analysis, enabling Trace Flag 1222 writes a detailed, text-based breakdown of the deadlock directly into the SQL Server Error Log.

DBCC TRACEON (1222, -1);

Step 2: Analyze the Deadlock Graph (xml_deadlock_report)

Once you retrieve the XML deadlock report, you can save it with a .xdl extension and open it in SQL Server Management Studio (SSMS) for a visual graph, or read the XML directly. Focus on three core sections:

1. Victim List: Identifies the specific process id (SPID) that SQL Server chose to terminate.
2. Process List: Details all transactions involved. Pay close attention to the clientapp, loginname, isolationlevel, and crucially, the inputbuf, which reveals the exact T-SQL query being executed by each process.
3. Resource List: Shows the database objects (pages, keys, tables) the processes were fighting over. It displays the owner-list and waiter-list, indicating the lock types (e.g., Shared [S], Update [U], Exclusive [X]).

Common Deadlock Patterns:

• Reverse Order Deadlocks: Procedure A updates Table 1 then Table 2. Procedure B updates Table 2 then Table 1.
• Bookmark Lookup Deadlocks: A SELECT query uses a non-clustered index and needs a bookmark lookup to the clustered index (requesting a Shared lock), while an UPDATE modifies the clustered index (holding an Exclusive lock) and subsequently tries to update the non-clustered index.

Step 3: Fix and Prevent Deadlocks

Resolving deadlocks requires code, architectural, or indexing modifications. They cannot be resolved by simply restarting the server or tweaking a global timeout setting.

1. Enforce Consistent Access Order

If multiple procedures update the same set of tables, ensure they access them in the exact same chronological order. If all transactions lock Table A before Table B, a circular deadlock cycle cannot form.

2. Optimize Queries and Add Covering Indexes

Inefficient queries that trigger full table scans hold locks on large amounts of data for extended periods, increasing deadlock probability. For Bookmark Lookup deadlocks, create a covering index—a non-clustered index that INCLUDEs all columns required by the SELECT statement. This prevents the SELECT from needing to touch the clustered index where the UPDATE is operating.

3. Enable Read Committed Snapshot Isolation (RCSI)

If your application logic permits reading slightly older, committed versions of rows, changing the database isolation level to RCSI can eliminate most reader-writer deadlocks.

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

RCSI utilizes row versioning in tempdb. Readers do not acquire shared locks; instead, they read the last committed version of the row. This means writers no longer block readers, drastically reducing concurrency conflicts.

4. Keep Transactions Short and Concise

Minimize the duration of transactions. Never require user interaction or execute long-running remote API calls while a BEGIN TRAN is open. Commit modifications as quickly as possible to release exclusive locks.

5. Implement Retry Logic in the Application

Because deadlocks are often transient and timing-dependent, robust applications should anticipate Error 1205. Data access layers should catch the exception, apply a brief, randomized backoff (e.g., 100-300ms), and automatically resubmit the transaction.

6. Utilizing DEADLOCK_PRIORITY

If you have a batch process that constantly deadlocks with a critical OLTP transaction, and you cannot fix the underlying indexing, you can instruct SQL Server to sacrifice the batch process. By executing SET DEADLOCK_PRIORITY LOW; at the start of your batch script, you ensure that if a deadlock cycle occurs, the batch process will always be chosen as the victim, protecting the critical user-facing transaction.

7. Avoiding UI Interactions in Transactions

A classic architectural mistake that leads to prolonged blocking and eventual deadlocks is wrapping user input within a transaction block. Never execute BEGIN TRAN, send a message to a client application requesting confirmation, and wait for the response before executing COMMIT TRAN. Transactions must be completely self-contained within the database engine and execute as rapidly as physical I/O allows.