Deadlock Victim Selection

When two transactions are frozen in a standoff, the database must assassinate one.

The idea

A Deadlock happens when Transaction A locks Row 1 and waits for Row 2, while Transaction B locks Row 2 and waits for Row 1. They will wait forever. Modern databases (like Postgres or SQL Server) run a background daemon that detects these cycles. Once detected, the database resolves the deadlock by choosing a Victim—it forcefully kills one of the transactions, rolling it back, which frees the locks and allows the other transaction to succeed.

Step 1: Two database transactions start.

How it works (Cycle Detection)

The database maintains a "Wait-For Graph", tracking which transaction is waiting for which lock. If a cycle forms (A -> B -> A), a deadlock exists. The database usually selects the victim based on cost: it kills the transaction that has done the least amount of work (fewest logs written) so the rollback is fast.

# The application perspective

try:
    db.execute("UPDATE account_A SET balance = balance - 100")
    db.execute("UPDATE account_B SET balance = balance + 100")
    db.commit()
    
except DeadlockDetectedException:
    # ⚠ We were chosen as the victim!
    # Our transaction was rolled back automatically by the DB.
    # Standard practice: sleep for a random few milliseconds and retry.
    time.sleep(random.uniform(0.01, 0.05))
    retry_transaction()

Cost

Deadlock detection is an expensive algorithm. Databases only run it if a transaction has been waiting for a lock for a certain threshold (e.g., `deadlock_timeout` in Postgres defaults to 1 second). This means deadlocks silently degrade performance by causing 1-second latency spikes before they are resolved.

Watch out for