When numerous users access the same data simultaneously, a SQL Server program may function flawlessly throughout development yet still encounter deadlock issues. Both transactions require resources owned by the other transaction in order to update an order and an inventory record. Neither can go on. When SQL Server notices this, it selects one transaction to be the victim of the stalemate. While SQL Server rolls back its transaction to allow the other transaction to continue, the victim receives error 1205.

Retrying the procedure after catching the exception is a typical reaction. Retrying can be beneficial, but it might merely mask the fundamental issue if the impasse is not understood. If the program is not correctly constructed, it may potentially result in redundant side effects or increase database demand. This article will examine the causes of SQL Server deadlocks, how to look into them, and how to securely handle them 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

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.

HostForLIFE.eu SQL Server 2025 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.