Deadlock & Lock Ordering

When two database transactions wait for each other forever.

The idea

When you transfer money, you must lock both accounts to prevent race conditions. Imagine Transaction 1 transfers $10 from Alice to Bob. It locks Alice, then locks Bob. Now imagine Transaction 2 transfers $10 from Bob to Alice at the exact same millisecond. It locks Bob, then tries to lock Alice. Transaction 1 is waiting for Bob. Transaction 2 is waiting for Alice. They are stuck in a circular dependency forever. This is a Deadlock.

Step 1: T1 wants to move money Alice -> Bob. T2 wants to move Bob -> Alice.

How it works (Global Lock Ordering)

Databases detect deadlocks and resolve them by arbitrarily killing one of the transactions. But killing transactions creates bad UX. The engineering fix is to guarantee a Global Lock Order. When writing code, you must sort the resources you want to lock before you lock them. If all transactions in the system agree to always lock Account A before Account B (e.g. by alphabetical order or ID), a cyclic deadlock is mathematically impossible.

# BAD: Vulnerable to deadlocks
def transfer(from_acc, to_acc):
    lock(from_acc)
    lock(to_acc) # Might deadlock if reversed concurrently!
    # do transfer...

# GOOD: Sorted Lock Ordering
def transfer_safe(from_acc, to_acc):
    # Always lock the lower ID first, regardless of direction!
    first, second = sorted([from_acc, to_acc], key=lambda a: a.id)
    
    lock(first)
    lock(second)
    # do transfer...

Cost

Lock ordering requires extreme discipline across a codebase. If 99 functions follow the sorting rule, but 1 junior developer writes a function that locks out of order, the entire system becomes vulnerable to deadlocks again. It also requires you to know all the resources you need upfront, which is sometimes difficult in complex logic.

Watch out for