How to Handle SQL Server Deadlocks in Applications Without Blindly Retrying

A SQL Server deadlock can cause an application request to fail even when the database is healthy and the SQL statements are valid. Two transactions may each hold a lock that the other transaction needs, leaving neither able to continue.

SQL Server detects this situation and selects one transaction as the deadlock victim. It rolls back that transaction and returns error 1205 to the application. The other transaction can then continue.

A common application response is to retry the failed operation immediately. Although a retry can succeed, blindly retrying every database exception can make the problem worse. It may increase database load, hide a recurring design issue, or repeat an operation that has external side effects.

The right approach is to identify the deadlock, understand why the transactions conflict, and apply a controlled retry only when the operation is safe to repeat.

What Causes a SQL Server Deadlock?

A deadlock occurs when two or more transactions wait for resources held by one another.

Consider two transactions that update the same two accounts in opposite orders.

Transaction A:

BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = Balance - 100
WHERE AccountId = 1;

UPDATE dbo.Accounts
SET Balance = Balance + 100
WHERE AccountId = 2;

COMMIT TRANSACTION;

Transaction B:

BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = Balance - 50
WHERE AccountId = 2;

UPDATE dbo.Accounts
SET Balance = Balance + 50
WHERE AccountId = 1;

COMMIT TRANSACTION;

If transaction A locks account 1 and transaction B locks account 2, each transaction may need a resource held by the other.

The conflict can develop like this:

Step	Transaction A	Transaction B
1	Locks account 1
2		Locks account 2
3	Requests account 2 and waits
4		Requests account 1 and waits

Neither transaction can proceed. SQL Server detects the cycle in the lock-wait graph and chooses a victim.

The example illustrates the locking pattern, but a deadlock is not guaranteed to occur on every execution. The timing and execution plans determine whether the conflicting locks overlap.

Real deadlocks can also involve foreign key checks, index maintenance, range locks, triggers, and different database access paths.

How SQL Server Chooses a Deadlock Victim

When SQL Server detects a deadlock, it selects a transaction to roll back so the remaining transaction can continue.

The DEADLOCK_PRIORITY setting influences victim selection. When transactions have different priorities, SQL Server generally chooses a transaction with the lower priority. If priorities are equal, the engine considers the estimated cost of rolling back each transaction.

The application receives SQL Server error number 1205 for the victim transaction.

This is different from a general command timeout. A deadlock is a detected conflict that SQL Server resolves by aborting a transaction. A timeout may occur because a query has waited too long or taken too long to execute, and it does not necessarily indicate a deadlock.

Treating these errors as equivalent can lead to incorrect retry behavior.

Capture Deadlock Information Before Changing the Code

The first step in fixing a recurring deadlock is to capture evidence of the conflict.

SQL Server provides the xml_deadlock_report Extended Event. The default system_health Extended Events session normally captures deadlock reports, so it is often the first place to investigate.

You can inspect the session's event files with a query similar to this:

SELECT
    XEventData.XEvent.value(
        '(event/@timestamp)[1]',
        'datetime2'
    ) AS EventTime,
    XEventData.XEvent.value(
        '(event/data/value/deadlock)[1]',
        'xml'
    ) AS DeadlockGraph
FROM
(
    SELECT CAST(event_data AS xml) AS EventData
    FROM sys.fn_xe_file_target_read_file(
        'system_health*.xel',
        NULL,
        NULL,
        NULL
    )
) AS XEventData
WHERE XEventData.EventData.exist(
    '/event[@name="xml_deadlock_report"]'
) = 1
ORDER BY EventTime DESC;

The exact file path and access permissions depend on the SQL Server installation and deployment environment. On managed database services, the method for retrieving Extended Events may differ.

The deadlock graph contains information about the participating processes, resources, SQL statements, and selected victim. Use it to determine which statements were running and which locks created the cycle.

Look for:

The SQL statements involved in the deadlock.
The indexes and objects associated with the locked resources.
The order in which transactions acquired locks.
The transaction isolation level.
The execution plans and predicates used by the statements.
Whether triggers, foreign key checks, or additional queries participated.

Do not assume that the statement reported as the victim is the only statement that needs attention. Both sides of the deadlock contribute to the conflict.

Fix the Locking Pattern Before Adding Retries

A retry can help an operation recover from an occasional deadlock, but it does not remove the underlying conflict. If the same transactions continue to acquire locks in incompatible orders, deadlocks can keep recurring.

Access resources in a consistent order

One common fix is to make transactions update resources in the same order.

In the earlier account example, both transactions should update the account with the lower identifier first, then the account with the higher identifier.

For more complex operations, define a consistent ordering rule for the affected resources and apply it throughout the application.

This reduces the chance that transactions will form a circular wait. It does not eliminate every possible deadlock, because other statements, indexes, and lock types may still participate.

Keep transactions short

A transaction holds locks until it commits or rolls back. The longer a transaction remains open, the more opportunity there is for another transaction to conflict with it.

Avoid performing unrelated work inside a database transaction.

For example, do not hold database locks while waiting for an HTTP request to an external service, processing a large file, or asking a user to confirm an operation.

Prepare any necessary data before starting the transaction, perform the required database work, and commit as soon as the transaction is complete.

Review indexes and query plans

A query that scans a large portion of a table may touch more rows and indexes than necessary. This can increase lock contention and make deadlocks more likely.

Inspect the execution plans for the statements involved. Check whether indexes support the filtering and joining conditions, and verify that statistics and query plans are appropriate for the workload.

Adding an index is not automatically the right solution. Indexes add maintenance work to inserts, updates, and deletes, and additional indexes can change the locking behavior of a query. Test changes against realistic data and concurrency.

Review transaction isolation

Isolation settings influence which locks SQL Server takes and how long it holds them.

For workloads dominated by read-write contention, read committed snapshot isolation can reduce reader-writer blocking by allowing many reads to use row versions. However, it does not eliminate deadlocks involving writers or every other locking interaction.

Changing isolation settings should be a deliberate database-level decision. Review application assumptions, row-versioning storage, and the behavior of all relevant transactions before making the change.

Handle Error 1205 in a .NET Application

Applications using SQL Server through ADO.NET can identify a deadlock by inspecting SqlException.Number.

The following example shows a retry loop for a database operation that is safe to repeat:

using Microsoft.Data.SqlClient;

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

    for (int attempt = 1; ; attempt++)
    {
        cancellationToken.ThrowIfCancellationRequested();

        try
        {
            await operation(cancellationToken);
            return;
        }
        catch (SqlException ex)
            when (ex.Number == 1205 &&
                  attempt < maxAttempts)
        {
            var delayMilliseconds =
                Random.Shared.Next(100, 301) * attempt;

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

This example makes up to three total attempts: the initial attempt and up to two retries. It retries only SQL Server error 1205 and adds a randomized delay that increases with the attempt number.

The random delay helps prevent multiple competing requests from immediately retrying in lockstep. The cancellation token also allows the caller to stop waiting between attempts.

The operation passed to this method must be safe to execute again. The retry loop does not make an unsafe operation safe merely by catching error 1205.

The example also assumes that operation opens and manages its own database connection and transaction appropriately. If the operation has already performed external side effects, or if it reuses a transaction that SQL Server rolled back, it needs a different design.

In production, log the attempt count, elapsed time, operation name, and relevant correlation identifier. Avoid logging credentials, sensitive parameter values, or full customer records.

Retry the Entire Transaction, Not Just the Failed Statement

When SQL Server selects a deadlock victim, it rolls back the victim transaction. The application should generally restart the complete logical transaction rather than retrying only the statement that happened to fail.

Consider a transaction that:

Reads an account balance.
Checks a business rule.
Updates the balance.
Writes a transaction record.

If the update fails with error 1205, the previous reads and decisions may no longer be valid. Retrying only the update could use stale data or skip part of the intended business operation.

A safer pattern is to place the full unit of database work inside the retry delegate:

using Microsoft.Data.SqlClient;

public static async Task TransferAsync(
    string connectionString,
    int sourceAccountId,
    int destinationAccountId,
    decimal amount,
    CancellationToken cancellationToken)
{
    if (amount <= 0)
    {
        throw new ArgumentOutOfRangeException(
            nameof(amount));
    }

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

            await connection.OpenAsync(token);

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

            try
            {
                await TransferWithinTransactionAsync(
                    connection,
                    (SqlTransaction)transaction,
                    sourceAccountId,
                    destinationAccountId,
                    amount,
                    token);

                await transaction.CommitAsync(token);
            }
            catch
            {
                try
                {
                    await transaction.RollbackAsync(
                        CancellationToken.None);
                }
                catch
                {
                    // Preserve the original exception.
                    // Connection disposal remains a final
                    // cleanup mechanism.
                }

                throw;
            }
        },
        cancellationToken);
}

This example assumes that ExecuteWithDeadlockRetryAsync is the helper shown earlier and that TransferWithinTransactionAsync executes the required SQL commands using the supplied connection and transaction.

The transaction is created inside the retry delegate. Each attempt therefore uses a new connection and transaction rather than trying to continue with the transaction that SQL Server rolled back.

The rollback uses CancellationToken.None so that cancellation of the original request does not automatically prevent the rollback attempt. If the connection is already broken or SQL Server has already rolled back the transaction, rollback may still fail; the connection is disposed when the scope exits.

In a real transfer implementation, the database logic must also validate that the source account has sufficient funds, update both accounts atomically, and record the transfer in the same transaction. The database operations should use parameterized SQL or stored procedures, and the implementation should handle business-rule failures separately from deadlocks.

Do Not Retry Every SQL Exception

A database exception does not automatically mean the operation should be retried.

For example:

Error condition	Typical response
Deadlock victim, error 1205	Consider retrying the complete transaction
Constraint violation	Correct the data or report the business error
Invalid SQL or missing object	Fix the query or deployment
Authentication failure	Correct credentials or permissions
Command timeout	Investigate query duration and blocking; retry only under a deliberate policy
Connection failure	Apply a separate connection-recovery policy where appropriate

Retry policies should be specific to the failure being handled. Retrying an invalid query three times adds delay without improving the outcome.

A timeout also requires care. The client may not know whether the server completed the operation before the connection was interrupted. Repeating a write without a clear idempotency strategy can cause unintended results.

For SQL Server deadlock error 1205, SQL Server has already rolled back the victim transaction. That makes a complete transaction retry more straightforward than retrying after an ambiguous network failure, provided the operation does not include non-transactional external side effects.

Avoid Duplicate Side Effects

A database transaction can roll back database changes, but it cannot automatically undo an email, an HTTP request, a payment processed by another service, or a message already published to a broker.

Suppose a transaction sends a confirmation email and then encounters a deadlock before committing. Retrying the entire operation may send the email twice.

A better design is to keep external side effects outside the retryable transaction or record the intent to perform them in the database. An outbox process can publish the recorded intent after the database transaction commits.

For payments and other important commands, use an idempotency key or a unique operation identifier so that repeated attempts do not create duplicate business operations.

The exact strategy depends on the external service and the guarantees it provides. A retry policy should never assume that a database rollback reverses work performed by another system.

Add Backoff, Limits, and Observability

Retries should be bounded. An unlimited retry loop can keep a request alive indefinitely and consume database connections, application threads, and other resources.

A practical policy includes:

A small maximum number of attempts.
A randomized delay between retries.
A cancellation token or overall request deadline.
Logging and metrics for each deadlock and retry.
Alerts when deadlock frequency increases.
A clear decision about which operations are safe to repeat.

Avoid retrying immediately without a delay. If several requests encounter the same deadlock at once, they may all retry simultaneously and recreate the same contention.

Also avoid stacking multiple retry policies without understanding how they interact. If the database helper retries three times and an outer HTTP policy retries the entire request three times, one user action could trigger substantially more database attempts than expected.

Use a consistent retry budget across application layers, and measure both the original failures and the outcomes of retries.

Test Deadlock Handling Under Concurrency

A retry implementation should be tested with more than a simulated exception.

Create a controlled test environment in which concurrent transactions execute the relevant database operations. Verify that the application detects error 1205, rolls back the failed transaction, and starts a fresh transaction for the retry.

Check that:

The maximum attempt count is respected.
Cancellation stops additional attempts.
Failed transactions do not leave connections or locks open.
Business rules remain valid after a retry.
Database changes are not duplicated.
External side effects are not repeated unexpectedly.
Deadlock metrics and logs contain enough information to investigate failures.

Concurrency tests can be timing-sensitive, so use controlled coordination and inspect SQL Server's deadlock reports rather than assuming that a particular test execution will always produce a deadlock.

Conclusion

SQL Server deadlocks are concurrency conflicts, not ordinary query failures. SQL Server resolves them by rolling back a victim transaction and returning error 1205 to the application.

A reliable solution starts with the deadlock graph and the statements involved. Correct inconsistent resource ordering, shorten transactions, review indexes and execution plans, and evaluate isolation settings before relying on retries.

When retries are appropriate, retry the entire logical transaction with a new transaction, a bounded attempt count, and a delay between attempts. Make sure the operation is safe to repeat, and keep external side effects from being duplicated.

This approach improves recovery from occasional deadlocks while preserving data integrity and making recurring concurrency problems easier to diagnose.