Database deadlocks: find the cycle and retry the transaction

Two booking requests need the same pair of seats. Each reserves its first seat, then waits for the other one. Neither can finish because each is holding the resource the other needs. Eventually the database aborts one request, and the application reports a failure that disappears on refresh.

A database deadlock is a cycle of dependencies between transactions, not just a slow query. A transaction waiting for a busy row can make progress when the holder commits. In a deadlock, the holders themselves are waiting on one another. Giving them a longer timeout does not remove the cycle.

The useful response has two parts: change the acquisition pattern that creates the cycle, and make the application recover correctly when a transaction is still selected as the victim.

Use a disposable PostgreSQL database and create a shared table. This deliberately simplified example demonstrates locking; a real booking system also needs availability rules and ownership checks.

CREATE TABLE demo_seats (
    id integer PRIMARY KEY,
    holder text NULL
);
INSERT INTO demo_seats VALUES (10, NULL), (20, NULL);

Open two database sessions. Run these statements in the numbered order, stopping after each one so the other session can acquire its first lock:

-- 1. Session A
BEGIN;
UPDATE demo_seats SET holder = 'A' WHERE id = 10;

-- 2. Session B
BEGIN;
UPDATE demo_seats SET holder = 'B' WHERE id = 20;

-- 3. Session A: waits for B's lock on seat 20
UPDATE demo_seats SET holder = 'A' WHERE id = 20;

-- 4. Session B: completes the cycle, waiting for A's seat 10
UPDATE demo_seats SET holder = 'B' WHERE id = 10;

PostgreSQL detects the cycle and aborts one participant. Do not build the test around which participant loses. Roll back both sessions after inspecting the result so the surviving open transaction does not keep holding locks. The explicit locking documentation describes deadlock detection, victim selection, and consistent acquisition order.

The SQL statements are individually ordinary. Neither contains a special deadlock command. Concurrency supplies the failure: the same statements can pass thousands of sequential tests without reproducing it. A useful regression test therefore controls interleaving, not merely input values.

A deadlock victim lost a transaction, so recovery must replay a transaction.

For this two-seat operation, sort the seat IDs and lock them in ascending order before changing either reservation. Both callers now need seat 10 first. One waits before holding seat 20, leaving the winner free to finish.

BEGIN;

-- Both booking paths acquire these rows in the same order.
SELECT id, holder FROM demo_seats WHERE id = 10 FOR UPDATE;
SELECT id, holder FROM demo_seats WHERE id = 20 FOR UPDATE;

-- Validate availability after acquiring both locks.
-- Apply the reservation only if both seats satisfy the business rule.

COMMIT;

For a variable-size request, sort and deduplicate the IDs before acquiring locks. The important part is that every competing code path follows the same ordering rule. Fixing the public booking endpoint while leaving an administrative reassignment endpoint in reverse order leaves the cycle possible. Include background jobs in that audit.

The example uses explicit single-row statements to make the order visible. Do not infer a database's lock acquisition order from the order of values in an IN clause. Execution plans determine how multi-row statements find their rows. Establish the behavior for your engine and query shape.

This ordering rule prevents the demonstrated cycle; it is not proof that the whole application cannot deadlock. Foreign keys, triggers, additional tables, and lock upgrades can introduce other resources. Keep a consistent order across those resources too, and inspect the actual deadlock evidence when the symptom persists.

Keep transactions short for another reason: holding locks while waiting for an external API gives other requests more time to collide with them. Perform independent input validation before opening the transaction. Keep the reads that decide database correctness inside it. Moving every read outside simply exchanges blocking for stale decisions.

The failing statement is where the database reported the conflict. It is not necessarily the right recovery boundary. Earlier reads may have informed which seats to reserve, and earlier writes belonged to the aborted attempt. Reissuing only the last update cannot reconstruct that decision safely.

PostgreSQL assigns deadlocks SQLSTATE 40P01. Its retry guidance requires replaying the complete transaction, including logic that decides which statements and values to use. Handle the structured code rather than matching localized exception text.

Here is a bounded helper for direct Npgsql use. The callback owns all database reads, validation, and writes for one attempt, using the supplied connection and transaction:

using Npgsql;

static async Task<T> RunWithDeadlockRetryAsync<T>(
    NpgsqlDataSource dataSource,
    Func<NpgsqlConnection, NpgsqlTransaction, CancellationToken, Task<T>> operation,
    CancellationToken cancellationToken)
{
    const int maxAttempts = 3;

    for (var attempt = 1; ; attempt++)
    {
        try
        {
            return await RunAttemptAsync();
        }
        catch (PostgresException exception) when (
            exception.SqlState == "40P01" && attempt < maxAttempts)
        {
            var delay = TimeSpan.FromMilliseconds(
                Random.Shared.Next(25, 100) * attempt);
            await Task.Delay(delay, cancellationToken);
        }
    }

    async Task<T> RunAttemptAsync()
    {
        await using var connection = await dataSource
            .OpenConnectionAsync(cancellationToken);
        await using var transaction = await connection
            .BeginTransactionAsync(cancellationToken);

        var result = await operation(connection, transaction, cancellationToken);
        await transaction.CommitAsync(cancellationToken);
        return result;
    }
}

Each failed attempt leaves the local function and disposes its transaction and connection before the delay. Npgsql's transaction examples show the same explicit connection and transaction ownership. Opening another logical connection can reuse a pooled physical connection; the requirement is fresh transaction state.

Three attempts and the small randomized delay are illustrative bounds. The caller should pass a token that enforces the operation's overall deadline. The final deadlock propagates, and unrelated errors do not enter this retry loop. Add an attempt counter to operational logging so a successful response does not hide rising contention.

The callback must not send a booking email before commit. If it does, a deadlock can roll back the reservation after the email has already escaped. Repeating the callback can then send it again. Database rollback cannot retract an HTTP request, message, or email.

Write an outbox record in the same transaction when external publication must follow a successful reservation. The transactional outbox guide explains the delivery boundary. This does not make publication exactly once; consumers still need appropriate duplicate handling.

Also distinguish a confirmed deadlock rollback from an unknown commit outcome. A connection loss while awaiting commit might leave the caller unsure whether the database committed. The narrow helper above does not retry that condition. Recovery from ambiguity needs an operation identifier and a way to determine or safely repeat the intended effect. Broadening the catch to every database exception would quietly change that contract.

If an ORM or resilience layer already retries transactions, make one layer own the policy. Stacking three outer attempts around three inner attempts can permit nine executions. With EF Core, replay through its supported execution-strategy boundary and use fresh attempt state; do not treat a previously mutated change tracker as an automatic reset button.

The exception identifies the transaction that lost. Diagnosis needs the other participants and the resources each held or requested. Preserve the database's deadlock report and correlate it with application operation IDs. Record transaction boundaries, because an innocent-looking update may be waiting on locks acquired much earlier in its transaction.

PostgreSQL exposes current lock information through pg_locks, described in its lock monitoring documentation. Current snapshots may miss a deadlock already resolved by the server. SQL Server provides deadlock graphs through Extended Events, documented in its deadlocks guide. Use the evidence appropriate to the engine instead of translating one vendor's lock vocabulary into another by guesswork.

For regression coverage, coordinate two real transactions with barriers: both acquire the first resource before either requests the second. Assert the retry boundary and the final business invariant, such as either both seats belonging to one successful reservation or neither changing after failure. Then test the consistent-order version under contention. A sleep-based test that usually overlaps the operations is less useful than one that establishes the dependency explicitly.

Isolation levels explain which concurrent histories an engine permits. Deadlock analysis asks a narrower operational question: which transaction holds the resource that another needs right now? Drawing those arrows often reveals the fix before any configuration change does.

Katabench's database track documentation and guided labs describe different ways to practice data-access decisions and longer-running workflows. Bring a two-session experiment to that practice: state the invariant, force the dangerous interleaving, and show that recovery replays the complete decision. A disappearing exception is weaker evidence than a correct final state under the concurrency that caused it.