Even if each of the SQL queries involved is legitimate on its own, an application request may fail due to a SQL Server deadlock. Each of the two transactions may have a lock that the other transaction requires, preventing either from proceeding. In order to allow the other transaction to continue, SQL Server recognizes this circular dependency and selects one transaction as the deadlock victim.

Determining the cause of the stalemate is sometimes the most challenging aspect for developers. Error 1205 may appear in an application log, but it seldom specifies which statements were vying for locks, what resources were used, or how the transactions created a cycle.

SQL Server deadlock graphs provide this information. They identify the participating processes, their statements, the resources they own or request, and the transaction SQL Server selected as the victim.

This article explains how to capture deadlock graphs, interpret their components, identify the root cause, and apply practical fixes without relying on guesswork.

What Is a SQL Server Deadlock?
A deadlock occurs when two or more transactions wait for resources held by one another in a way that prevents all participating transactions from progressing.
Consider two transactions that update the same two accounts in opposite order.
Transaction A updates Account 1 and then Account 2. Transaction B updates Account 2 and then Account 1.
The transactions can reach a state where each holds a lock needed by the other. SQL Server detects the cycle and terminates one transaction to break it.

A simplified sequence looks like this:

• Transaction A acquires a lock on Account 1.
• Transaction B acquires a lock on Account 2.
• Transaction A requests a lock on Account 2 and waits.
• Transaction B requests a lock on Account 1 and waits.
• SQL Server detects the circular wait and chooses a victim.

The application receives error 1205 for the selected victim transaction. SQL Server rolls back that transaction, allowing the remaining transaction to continue.

A deadlock is different from ordinary blocking. Blocking occurs when one transaction waits for another to release a resource. A deadlock involves a cycle of dependencies that cannot resolve without intervention.

Capture Deadlock Graphs with Extended Events
SQL Server provides the xml_deadlock_report event through Extended Events. SQL Server's default system_health session normally captures this event, making it a useful starting point when investigating an existing deadlock.

The following query retrieves deadlock reports retained in the system_health event file on a SQL Server instance:
;WITH DeadlockEvents AS
(
    SELECT
        event_data =
            CONVERT(xml, event_data)
    FROM sys.fn_xe_file_target_read_file
    (
        (
            SELECT
                CAST(target_data AS xml)
                    .value(
                        '(EventFileTarget/File/@name)[1]',
                        'nvarchar(4000)'
                    )
            FROM sys.dm_xe_sessions AS sessions
            INNER JOIN sys.dm_xe_session_targets AS targets
                ON sessions.address = targets.event_session_address
            WHERE sessions.name = N'system_health'
              AND targets.target_name = N'event_file'
        ),
        NULL,
        NULL,
        NULL
    )
)
SELECT
    event_data.value(
        '(event/@timestamp)[1]',
        'datetime2'
    ) AS deadlock_time_utc,
    event_data.query(
        '(event/data/value/deadlock)[1]'
    ) AS deadlock_graph
FROM DeadlockEvents
WHERE event_data.value(
    '(event/@name)[1]',
    'nvarchar(100)'
) = N'xml_deadlock_report'
ORDER BY deadlock_time_utc DESC;

This example reads the active event-file path exposed by the running system_health session. The required permissions and the availability of the event file depend on the SQL Server environment.

The session may have rotated older event files, so it is not a permanent deadlock archive. For long-term monitoring, consider a dedicated Extended Events session with an appropriate retention policy.

Open the graph in SQL Server Management Studio
The XML returned by the query can be saved or opened in SQL Server Management Studio for graphical inspection. When the XML is recognized as a deadlock report, SSMS can display the graphical deadlock diagram.

The diagram is often easier to interpret than raw XML because it shows the relationship between the participating processes and resources.

Focus on the processes that participate in the cycle, their requested and held locks, and the arrows that represent waiting relationships.

Understand the Main Parts of a Deadlock Graph

A deadlock graph contains three important groups of information.

These components provide different pieces of the same incident. The victim is the transaction SQL Server chose to terminate, but it is not necessarily the transaction that caused the underlying design problem.

The root cause usually lies in the sequence of operations, lock acquisition order, transaction duration, or access pattern shared by the participants.

Read the process information
A process entry may include details such as:

• The process identifier used within the deadlock report.
• The transaction ID.
• The database and execution context.
• The isolation level.
• The current statement and input buffer.
• The lock mode being requested or held.

The statement text is especially useful. It helps connect the database event to an application operation, stored procedure, or background job.

A process may be executing one statement while holding locks acquired by earlier statements in the same transaction. Do not assume that the statement shown is the only statement responsible for the locks it holds.

Read the resource information
The resource list describes the objects involved in the deadlock. Depending on the incident, these may include key locks, page locks, object locks, or other lockable resources.
For a lock resource, inspect the owners and waiters. The owner has a lock that is currently granted, while the waiter is requesting a conflicting lock.
A deadlock is established by following these dependencies until they form a cycle. Looking at only one process or one lock does not reveal the full relationship.

Follow the Deadlock Cycle
Suppose one transaction updates a customer row and then updates an order, while another transaction updates the same resources in reverse order.

The first transaction might execute:
BEGIN TRANSACTION;

UPDATE dbo.Customers
SET LastUpdated = SYSUTCDATETIME()
WHERE CustomerId = 100;

UPDATE dbo.Orders
SET Status = N'Processing'
WHERE CustomerId = 100;

COMMIT TRANSACTION;

The second transaction might execute:
BEGIN TRANSACTION;

UPDATE dbo.Orders
SET Status = N'Processing'
WHERE CustomerId = 100;

UPDATE dbo.Customers
SET LastUpdated = SYSUTCDATETIME()
WHERE CustomerId = 100;

COMMIT TRANSACTION;

These examples illustrate a potential deadlock pattern, not a guarantee that a deadlock will occur. The outcome depends on timing, indexes, lock granularity, isolation settings, and the rows affected.

If the first transaction holds a lock on a customer row and waits for an order resource, while the second holds the order resource and waits for the customer row, a circular dependency can form.

The deadlock graph provides evidence of the actual cycle. Use it to verify whether the competing statements and resources match this pattern rather than assuming that opposite statement order is always the cause.

Find the Root Cause
A useful deadlock investigation should answer three questions:

1. Which operations were competing?
2. Which resources created the circular wait?
3. What change would prevent the same cycle from occurring again?

Start with the deadlock timestamp and the SQL statements captured in the process list. Map those statements to application code, stored procedures, scheduled jobs, or background workers.

Next, inspect the resources and lock modes. A key-lock deadlock may point to competing row updates, while a more complex graph can involve index access, foreign-key checks, or multiple tables.

Then review the transaction boundaries. A transaction that remains open while the application performs unrelated work may hold locks longer than necessary.

Finally, reproduce the workload in a controlled environment if possible. Reproduction helps verify whether a proposed change actually removes the dependency cycle.

Common Ways to Prevent Deadlocks
Access resources in a consistent order
If multiple operations update the same set of resources, make them acquire locks in a consistent order whenever practical.

For example, transactions that modify customers and orders should follow the same logical ordering rather than accessing those resources in opposite sequences.

Consistent ordering reduces opportunities for circular waits, although it does not eliminate every possible deadlock.

Keep transactions short
Perform only the work that needs to be part of the database transaction while locks are held.

Avoid making external HTTP calls, waiting for user input, or performing lengthy calculations inside an open transaction. Shorter transactions generally reduce the period during which other operations can be blocked.
Review indexes and query plans

Poor access paths can cause statements to examine more rows than necessary and acquire or retain more locks.

Review the execution plans for the statements involved. Suitable indexes and more selective predicates can reduce unnecessary data access, but adding an index is not a universal deadlock fix.

The index itself can also introduce additional maintenance work and lock interactions. Measure the effect of each change.
Use isolation levels deliberately

Isolation settings influence locking and concurrency behavior. Read committed snapshot isolation can reduce some reader-writer blocking by using row versions, but it does not eliminate deadlocks involving conflicting writes.

Changing the isolation level without understanding the application's consistency requirements can introduce incorrect assumptions about the data being read.

Test the effect of isolation changes against the actual workload.

Handle Deadlock Error 1205 in the Application
Even well-designed systems can experience deadlocks. Applications should be prepared to handle error 1205 when retrying the operation is safe.

A simple retry loop for a database operation can use a bounded number of attempts:
using Microsoft.Data.SqlClient;

public static async Task ExecuteWithDeadlockRetryAsync(
    Func<CancellationToken, Task> operation,
    CancellationToken cancellationToken = default)
{
    const int maxAttempts = 3;

    for (var attempt = 1; ; attempt++)
    {
        try
        {
            await operation(cancellationToken);
            return;
        }
        catch (SqlException ex)
            when (ex.Number == 1205 &&
                  attempt < maxAttempts)
        {
            var delay = TimeSpan.FromMilliseconds(
                100 * Math.Pow(2, attempt - 1));

            await Task.Delay(delay, cancellationToken);
        }
    }
}

This example performs at most three attempts, including the original attempt, with a short exponential delay between retries. The operation passed to the helper must represent a complete, safely repeatable unit of work. If it uses EF Core, create or obtain a fresh DbContext for each attempt when necessary, especially after a failed transaction.

Do not retry every SQL exception. Restrict this behavior to the deadlock error and use a broader resilience policy only when the additional failure types are understood.

Also consider the operation's external side effects. SQL Server rolling back a transaction does not undo a message already published or an external payment already submitted. Use idempotency or an outbox pattern when the operation crosses system boundaries.

Common Mistakes
Treating the victim as the root cause
SQL Server selects a victim to break the cycle. The selected transaction is not automatically responsible for the design problem. Examine every participating process and resource.

Capturing only the application error
Error 1205 identifies a deadlock, but the error message alone does not show the complete dependency cycle. Capture and inspect the deadlock graph.

Adding indexes without examining the plan
An index can improve a query's access path, but it also changes the database's locking and maintenance behavior. Identify the expensive operation first.

Increasing the deadlock priority without fixing the cause
SET DEADLOCK_PRIORITY influences which transaction SQL Server chooses as the victim. It does not remove the circular dependency.

Use it only when the application has a deliberate reason to prefer one transaction over another.

Retrying indefinitely
Unbounded retries can increase load and hide a persistent concurrency defect. Use a bounded retry policy and retain enough diagnostics to investigate recurring incidents.

Best Practices for Production Databases

• Capture deadlock graphs through Extended Events and retain sufficient history for investigation.
• Correlate deadlock timestamps with application logs and deployment events.
• Standardize resource access order across related transactions where possible.
• Keep transactions short and avoid unrelated work while holding database locks.
• Review execution plans and indexes before changing isolation settings.
• Retry deadlock victims only when the complete operation can safely run again.
• Monitor deadlock frequency and recurring query patterns rather than treating each incident as unrelated.
• Test concurrency changes under realistic load before deploying them.

Advantages and Disadvantages

Summary
SQL Server deadlock graphs provide the evidence needed to investigate concurrency failures. They show the participating processes, locked resources, waiting relationships, and the transaction selected as the victim.

Start by capturing the xml_deadlock_report event, following the circular dependency, and mapping the involved statements to application operations. Then investigate transaction order, transaction duration, execution plans, and index usage before applying a fix.

Use bounded retries for safe operations, but do not treat retries as a replacement for correcting recurring deadlock patterns. The most reliable solution combines deadlock evidence, consistent transaction design, efficient database access, and application-level recovery.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.