Deadlocks — Detection, Prevention and Resolution

A deadlock occurs when two or more transactions each hold a lock that the other needs, creating a circular wait that can never resolve. SQL Server’s lock monitor detects deadlock cycles and terminates one transaction (the “victim”) with error 1205. The victim receives a SqlException in the application. Properly handled deadlocks require the application to catch error 1205 and retry the transaction — correctly implementing retry logic is as important as understanding why deadlocks occur.

Deadlock Causes and Resolution #

-- ── Classic deadlock — access order ───────────────────────────────────────
-- Session A: UPDATE Posts SET ... WHERE Id = 1  (holds X on Posts row 1)
--            UPDATE Users SET ... WHERE Id = 'user-1'  (waits for X on Users row)
-- Session B: UPDATE Users SET ... WHERE Id = 'user-1'  (holds X on Users row)
--            UPDATE Posts SET ... WHERE Id = 1  (waits for X on Posts row 1)
-- → Deadlock: each holds what the other needs

-- Prevention: always access tables in the same order across all transactions
-- All procedures: first update Posts, then update Users (never reverse)

-- ── Capture deadlock information ──────────────────────────────────────────
-- Enable trace flag 1222 for detailed deadlock info in the error log:
DBCC TRACEON(1222, -1);

-- Or use Extended Events (recommended for production):
-- SQL Server Management Studio → Management → Extended Events → Sessions
-- Create a session capturing deadlock_graph event

-- ── System health session (always on) captures last deadlocks ─────────────
SELECT
    xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
    xdr.query('.') AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions        s ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') x(xdr)
ORDER BY DeadlockTime DESC;

-- ── Set deadlock priority ─────────────────────────────────────────────────
-- Make a session the preferred victim (sacrificed in a deadlock):
SET DEADLOCK_PRIORITY LOW;     -- this session becomes the victim
SET DEADLOCK_PRIORITY NORMAL;  -- default
SET DEADLOCK_PRIORITY HIGH;    -- other session becomes the victim

Retry Logic in ASP.NET Core #

// ── EF Core execution strategy handles deadlock retries automatically ─────
// Program.cs:
builder.Services.AddDbContext<AppDbContext>(opts =>
    opts.UseSqlServer(connStr, sqlOpts =>
        sqlOpts.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(5),
            errorNumbersToAdd: null   // includes 1205 (deadlock) by default
        )));

// ── Manual retry for explicit transactions (not covered by EF Core strategy) ─
public async Task<bool> PublishPostWithRetryAsync(int postId, string userId,
    CancellationToken ct)
{
    const int maxAttempts = 3;
    for (int attempt = 1; attempt <= maxAttempts; attempt++)
    {
        try
        {
            using var tx = await _db.Database.BeginTransactionAsync(ct);
            // ... perform the multi-step publish operation
            await tx.CommitAsync(ct);
            return true;
        }
        catch (SqlException ex) when (ex.Number == 1205 && attempt < maxAttempts)
        {
            // Deadlock victim — wait briefly then retry
            _logger.LogWarning("Deadlock on attempt {Attempt}. Retrying...", attempt);
            await Task.Delay(TimeSpan.FromMilliseconds(100 * attempt), ct);
        }
    }
    return false;
}

// SqlException.Number == 1205 is the deadlock victim error code

Common Mistakes #

Mistake 1 — No retry logic for deadlock errors (user sees unhandled exception) #

❌ Wrong — deadlock causes SqlException 1205; application returns 500 error to user; operation lost.

✅ Correct — catch SqlException 1205, wait briefly, retry up to 3 times; use EF Core’s EnableRetryOnFailure for automatic handling.

Mistake 2 — Inconsistent table access order across procedures (systematic deadlocks) #

❌ Wrong — Procedure A: Posts → Users; Procedure B: Users → Posts; deadlock whenever both run concurrently.

✅ Correct — enforce canonical access order: all transactions access Posts, then Comments, then Users, consistently.