How to Handle SQL Server Deadlocks in Applications Without Blindly Retrying

A SQL Server application can work perfectly during development and still fail with deadlock errors when multiple users access the same data at the same time. One transaction updates an order, another updates its inventory record, and both need resources held by the other transaction. Neither can continue.

SQL Server detects this situation and chooses one transaction as the deadlock victim. The victim receives error 1205, while SQL Server rolls back its transaction so the other transaction can proceed.

A common response is to catch the exception and retry the operation. Retrying can help, but doing it without understanding the deadlock may only hide the underlying problem. It can also increase database load or cause duplicate side effects if the application is not designed carefully.

In this article, we will look at why SQL Server deadlocks happen, how to investigate them, and how to handle them safely in application code.

What Is a SQL Server Deadlock?

A deadlock occurs when two or more transactions hold resources that the others need, creating a circular wait.

Consider two transactions that update the same two tables in a different order.

Transaction A updates the Orders table first and then needs the Inventory table. Transaction B updates Inventory first and then needs Orders.

<CodeBlock language="mermaid" editable={false}>flowchart TD
A["Transaction A holds Orders lock"] --> B["Transaction A waits for Inventory"]
C["Transaction B holds Inventory lock"] --> D["Transaction B waits for Orders"]
B --> E["Circular wait: deadlock"]
D --> E
E --> F["SQL Server selects a victim"]
F --> G["Victim transaction is rolled back"]
F --> H["Other transaction continues"]</CodeBlock>

Neither transaction can proceed while the other holds the required lock. SQL Server detects the cycle and terminates one transaction.

This is different from a normal blocking situation. Blocking occurs when one transaction waits for another to release a resource. A deadlock requires a circular dependency that prevents the involved transactions from making progress.

Deadlocks are not necessarily evidence of a SQL Server bug. They can occur in correctly functioning applications when concurrent transactions acquire locks in conflicting patterns.

A Simple Example of How Deadlocks Happen

Suppose an application transfers stock between two warehouses. Each warehouse has a row in an Inventory table.

One transaction locks warehouse A and then attempts to lock warehouse B. Another transaction does the opposite.

-- Transaction A
BEGIN TRANSACTION;

UPDATE dbo.Inventory
SET Quantity = Quantity - 1
WHERE WarehouseId = 1;

WAITFOR DELAY '00:00:05';

UPDATE dbo.Inventory
SET Quantity = Quantity + 1
WHERE WarehouseId = 2;

COMMIT TRANSACTION;

A second transaction might perform the updates in reverse order:

-- Transaction B
BEGIN TRANSACTION;

UPDATE dbo.Inventory
SET Quantity = Quantity - 1
WHERE WarehouseId = 2;

WAITFOR DELAY '00:00:05';

UPDATE dbo.Inventory
SET Quantity = Quantity + 1
WHERE WarehouseId = 1;

COMMIT TRANSACTION;

The WAITFOR statement makes the timing easier to demonstrate. It should not be added to production code.

If the transactions overlap, each can hold a lock on one row while waiting for the other row. SQL Server may detect the deadlock and roll back one transaction.

The same pattern can occur in real applications when different code paths update related records in different orders.

How to Find the Cause of a Deadlock

Before changing application code, find out which resources were involved and why the transactions needed them.

1. Capture the deadlock graph

SQL Server provides deadlock information through Extended Events. The built-in system_health session commonly captures xml_deadlock_report events, making it a useful starting point.

You can inspect the session's event file with a query like this:

SELECT
    XEvent.value(
        '(event/@timestamp)[1]',
        'datetime2'
    ) AS DeadlockTime,
    XEvent.query(
        '(event/data/value/deadlock)[1]'
    ) AS DeadlockGraph
FROM
(
    SELECT CAST(event_data AS XML) AS XEvent
    FROM sys.fn_xe_file_target_read_file(
        'system_health*.xel',
        NULL,
        NULL,
        NULL
    )
    WHERE object_name = 'xml_deadlock_report'
) AS Deadlocks
ORDER BY DeadlockTime DESC;

The exact file location and permissions depend on your SQL Server installation. On some systems, the wildcard path may need to be replaced with the actual Extended Events file path.

The deadlock graph shows the participating processes, locked resources, requested locks, and the transaction SQL involved. Use it to identify the actual conflict instead of guessing from the application exception alone.

2. Identify the victim and participating statements

In the deadlock graph, look for:

The victim process selected by SQL Server.
The statements executed by each participating transaction.
The tables, indexes, keys, or other resources involved.
The lock modes held and requested.
The order in which each transaction accessed those resources.

For example, if two transactions consistently update the same tables in opposite orders, standardizing the access order may remove the circular wait.

If the graph shows scans or a large number of locked rows, investigate whether the query needs a better index or a more selective predicate.

3. Check whether the problem is recurring

One deadlock during an unusual workload may require a different response from hundreds of deadlocks every minute.

Track the frequency of error 1205, the affected operations, transaction duration, and the impact on users. Compare deadlock occurrences with deployment changes, scheduled jobs, and traffic spikes.

This information helps prioritize the fixes that will produce the largest improvement.

Reduce Deadlocks Before Adding Retries

The best deadlock strategy is to reduce the likelihood of conflicting transactions.

Keep transactions short

Long-running transactions hold locks for longer periods and give other transactions more opportunities to conflict.

Avoid unnecessary network calls, user interaction, or lengthy calculations while a database transaction is open.

Perform independent work before starting the transaction when possible, and commit as soon as the required database changes are complete.

Access resources in a consistent order

If several operations update the same set of tables, make them acquire resources in a consistent order.

For example, if one operation updates Orders before Inventory, another operation that touches both should follow the same logical order where possible.

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

Review indexes and query plans

Poorly indexed queries can scan more rows than necessary. Depending on the query plan, isolation level, and access pattern, this can increase locking and contention.

Review the execution plan and verify that indexes support the actual predicates and joins. Do not add indexes blindly: every additional index also increases the cost of data modifications.

Use an appropriate isolation level

Isolation levels influence concurrency behavior, but changing them without understanding the application can introduce different consistency problems.

For example, enabling READ_COMMITTED_SNAPSHOT can reduce certain reader-writer blocking patterns by using row versions. It does not eliminate all deadlocks, especially those involving writers.

Choose an isolation strategy based on the application's consistency requirements and test its effects under realistic concurrent workloads.

Retry Deadlock Victims Safely in C#

After fixing the most obvious causes, a limited retry can improve resilience to occasional deadlocks.

SQL Server reports a deadlock victim through error number 1205. With Microsoft.Data.SqlClient, you can check the error number using SqlException.Number.

The important requirements are:

Retry only errors that are considered transient and safe to retry.
Place the retry around the complete transaction, not just the failed statement.
Use a small retry limit and increasing delays.
Ensure that external side effects are not repeated accidentally.
Log the retry attempts and final outcome.

Here is a simple example using Microsoft.Data.SqlClient.

using Microsoft.Data.SqlClient;

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

    for (int attempt = 1; attempt <= maxAttempts; attempt++)
    {
        try
        {
            await operation(cancellationToken);
            return;
        }
        catch (SqlException ex)
            when (ex.Number == 1205 && attempt < maxAttempts)
        {
            int delayMilliseconds = 200 * attempt;

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

This helper retries a deadlock up to three total attempts, with short increasing delays. The last deadlock exception is allowed to propagate to the caller.

It is intentionally simple. Production systems may need randomized jitter, structured logging, metrics, and a shared retry policy. Avoid stacking multiple retry policies across the database driver, repository, service, and job layers, as this can multiply the total number of attempts.

Retry the complete transaction

The operation passed to the helper must create and execute a fresh transaction for each attempt.

using Microsoft.Data.SqlClient;

public static async Task TransferStockAsync(
    string connectionString,
    int sourceWarehouseId,
    int targetWarehouseId,
    int quantity,
    CancellationToken cancellationToken)
{
    if (quantity <= 0)
    {
        throw new ArgumentOutOfRangeException(
            nameof(quantity));
    }

    await ExecuteWithDeadlockRetryAsync(
        async token =>
        {
            await using var connection =
                new SqlConnection(connectionString);

            await connection.OpenAsync(token);

            await using var transaction =
                await connection.BeginTransactionAsync(token);

            try
            {
                int firstWarehouseId = Math.Min(
                    sourceWarehouseId,
                    targetWarehouseId);

                int secondWarehouseId = Math.Max(
                    sourceWarehouseId,
                    targetWarehouseId);

                foreach (int warehouseId in new[]
                {
                    firstWarehouseId,
                    secondWarehouseId
                })
                {
                    await using var command =
                        new SqlCommand(
                            """
                            UPDATE dbo.Inventory
                            SET Quantity = Quantity
                            WHERE WarehouseId = @WarehouseId;
                            """,
                            connection,
                            (SqlTransaction)transaction);

                    command.Parameters.AddWithValue(
                        "@WarehouseId",
                        warehouseId);

                    await command.ExecuteNonQueryAsync(token);
                }

                // Replace the illustrative statements above
                // with the actual stock transfer logic.
                await transaction.CommitAsync(token);
            }
            catch
            {
                await transaction.RollbackAsync(
                    CancellationToken.None);

                throw;
            }
        },
        cancellationToken);
}

The transaction body above is illustrative, not a complete stock-transfer implementation. The Quantity = Quantity statements do not move stock. A real implementation must validate available stock, subtract from the source, add to the destination, and ensure both changes succeed or neither does. It should also handle missing rows and use correctly typed SQL parameters.

The example demonstrates the transaction boundary: each retry opens a new connection and transaction, and the entire operation is repeated after a deadlock. In production, use parameter types that match the schema rather than relying on AddWithValue.

Also note that ordering the warehouse IDs in this example is only useful if the actual updates acquire their locks in that same order. Merely sorting IDs in application code does not guarantee lock acquisition order if the SQL statements or query plans behave differently.

Why Blind Retries Can Make Things Worse

Retries are not a replacement for diagnosing the deadlock.

If many requests repeatedly collide, each retry creates more database work. During a traffic spike, this can increase contention and make the original problem worse.

There is also a risk of repeating operations that affect systems outside SQL Server.

For example, if an operation charges a payment provider before the database transaction finishes, retrying the entire method might charge the customer twice. Database rollback cannot undo an external payment.

For operations with external side effects, use appropriate idempotency keys, transactional outbox patterns, or other mechanisms that make repeated execution safe.

A retry should be a controlled recovery mechanism, not an excuse to ignore a persistent concurrency problem.

Common Deadlock-Handling Mistakes

Mistake	Why it is a problem	Better approach
Retrying every SQL exception	Permanent failures are not fixed by retries	Check the specific SQL error
Retrying only the failed statement	The original transaction may have been rolled back	Restart the complete transaction
Retrying indefinitely	Can increase load and delay failure reporting	Set a small attempt limit
Increasing command timeouts	A deadlock is not simply a slow query	Inspect the deadlock graph
Ignoring deadlocks that succeed on retry	Hides recurring contention	Track retry counts and error rates
Repeating external side effects	May duplicate payments, emails, or messages	Use idempotency and reliable messaging patterns
Changing isolation levels without testing	May alter application consistency	Validate the concurrency trade-offs

Best Practices for Production Applications

Use the following checklist when handling SQL Server deadlocks:

Capture deadlock graphs. Use Extended Events to identify the statements and resources involved.
Fix recurring causes first. Standardize resource access order, review indexes, and shorten transactions.
Retry only eligible errors. Handle error 1205 separately from validation, permission, and constraint errors.
Restart the complete transaction. Do not continue using a transaction that was selected as the deadlock victim.
Apply bounded backoff. Keep retries limited and consider jitter when many workers may retry together.
Make operations safe to repeat. Use idempotency controls for work that may be executed more than once.
Monitor retry rates. An increase in successful retries can still indicate a growing concurrency problem.
Test under concurrency. Simulate competing transactions before deploying a fix to production.

Conclusion

SQL Server deadlocks occur when concurrent transactions create a circular wait for resources. SQL Server resolves the conflict by selecting a victim and rolling back its transaction, but repeated deadlocks can affect application reliability and performance.

Start by capturing the deadlock graph and understanding which queries and locks caused the conflict. Reduce the likelihood of deadlocks by keeping transactions short, accessing resources consistently, and reviewing query plans and indexes.

Then add a bounded retry policy for error 1205 when the operation is safe to repeat. Restart the complete transaction, avoid duplicating external side effects, and monitor retries so that a recurring database problem does not remain hidden.

The goal is not merely to make deadlock errors disappear from application logs. It is to make concurrent operations predictable, recoverable, and safe.