Database Deadlocks

Also Known As

TL;DR

Explanation

Deadlocks occur when transaction A holds a lock on row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1. Databases detect this cycle and roll back the transaction with the smallest undo log (the 'victim'). The application must retry the victim transaction. Prevention strategies: acquire locks in a consistent order, keep transactions short, use SELECT ... FOR UPDATE to acquire all locks upfront.

Diagram

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2
    T1->>T1: LOCK account 1
    T2->>T2: LOCK account 2
    T1->>T1: Wait for account 2 - blocked
    T2->>T2: Wait for account 1 - blocked
    Note over T1,T2: DEADLOCK - circular wait
    T2-->>T2: DB kills T2 as victim
    T1->>T1: Gets account 2 lock
    T1->>T1: COMMIT

Common Misconception

Why It Matters

Common Mistakes

• Not catching and retrying deadlock exceptions (error code 1213 in MySQL, 40P01 in PostgreSQL).
• Accessing rows in different orders in different transactions — consistent lock ordering prevents deadlocks.
• Long transactions that hold locks for seconds instead of milliseconds.
• Not using SELECT ... FOR UPDATE when a read-then-write sequence must be atomic.

Code Examples

// Inconsistent lock order — deadlock prone:
// Transaction A: lock user 1, then lock user 2
// Transaction B: lock user 2, then lock user 1 — deadlock!
$pdo->beginTransaction();
$pdo->query('SELECT ... FROM accounts WHERE id = ' . $fromId . ' FOR UPDATE');
$pdo->query('SELECT ... FROM accounts WHERE id = ' . $toId . ' FOR UPDATE');

// Consistent lock order — always lock lower ID first:
$pdo->beginTransaction();
$ids = [$fromId, $toId];
sort($ids); // Always acquire locks in ascending ID order
foreach ($ids as $id) {
    $pdo->query('SELECT ... FROM accounts WHERE id = ' . $id . ' FOR UPDATE');
}

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html
• ↗ https://www.postgresql.org/docs/current/explicit-locking.html

Tags

Related Terms