The “Traffic Jam” That Kills Your Database (And How to Fix It)

Source: Dev.to

The Problem

You write a perfect banking API. The logic is sound, the math is flawless, and the unit tests always pass.
Then you launch.

Traffic spikes. Two users try to send money to each other simultaneously. Suddenly, your logs explode with red text:

Deadlock detected.

The database didn’t crash because of a bug in your math. It crashed because of a bug in timing.

What a Database Deadlock Looks Like

A deadlock is essentially a standoff in a western movie.

Imagine two friends, Alice and Bob.
They decide to send $10 to each other at the exact same millisecond.

What Happens Inside the DB

• Transaction A waits for Bob.
• Transaction B waits for Alice.

Neither can move → a circular wait → deadlock.

Modern DB engines detect this circle and kill one of the transactions, returning a generic 500 Internal Server Error to the user.

The “Unsafe” Transfer Endpoint

We use FastAPI + psycopg. In the unsafe version we lock the sender first, then the receiver – which feels logical but is dangerous.

# unsafe_transfer.py
@app.post("/transfer/unsafe")
def transfer_unsafe(req: TransferRequest):
    with pool.connection() as conn:
        with conn.cursor() as cur:
            # 1️⃣ Lock the sender's account
            cur.execute(
                "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                (req.from_account,)
            )
            current_balance = cur.fetchone()[0]

            if current_balance  Bob

[3] ❌ DEADLOCK: Bob -> Alice
[2] ✅ Success: Alice -> Bob
[5] ❌ DEADLOCK: Bob -> Alice
[4] ✅ Success: Alice -> Bob
...
Success:   6
Deadlocks: 14

The DB raises DeadlockDetected, rolls back the transaction, and the money never moves. The user is angry.

You cannot fix this with faster hardware.
You cannot fix this by “optimising” your SQL.
You fix this with geometry – i.e., a consistent lock order.

The Fix: Enforce a Global Lock Order

To prevent a cycle, every transaction must acquire locks in the exact same order.
The simplest rule: always lock the account with the smaller ID first.

Why It Works

Both transactions now race for the same first lock (ID 1). One wins, the other waits. No circular wait → no deadlock.

Safe Transfer Endpoint

# safe_transfer.py
@app.post("/transfer/safe")
def transfer_safe(req: TransferRequest):
    # Determine lock order: low ID first, high ID second
    first_lock_id  = min(req.from_account, req.to_account)
    second_lock_id = max(req.from_account, req.to_account)

    with pool.connection() as conn:
        with conn.cursor() as cur:
            # 1️⃣ Lock accounts in a fixed, consistent order
            cur.execute(
                "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                (first_lock_id,)
            )
            # Even with a delay, the second transaction is just WAITING for the first lock.
            time.sleep(0.1)

            cur.execute(
                "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                (second_lock_id,)
            )

            # 2️⃣ Now that we have both locks, we can safely check balances and transfer.
            # (Insert the same balance‑check & UPDATE logic as before)
            # …
            conn.commit()

The only change: sorting the two IDs before any SELECT … FOR UPDATE. Everything else stays the same.

Results After the Fix

Running the exact same attack.py script against /transfer/safe yields:

--- Starting Attack ---
Mode: SAFE (Fixed)
Users: 8
[1] ✅ Success: Alice -> Bob
[2] ✅ Success: Bob -> Alice
[3] ✅ Success: Alice -> Bob
[4] ✅ Success: Bob -> Alice
...
Success:  16
Deadlocks: 0

Latency is slightly higher (because the second transaction must wait for the first lock), but reliability is 100 % – no deadlocks, no lost money, no angry users.

Takeaways

1. Deadlocks are timing bugs, not logic bugs.
2. FOR UPDATE is powerful but dangerous if lock order is inconsistent.
3. The one‑line fix: sort the IDs (or any comparable resource identifier) and always lock in that order.
4. Test your concurrency! Use a script like attack.py to verify that your endpoint survives simultaneous requests.

By enforcing a deterministic lock order, you eliminate the circular dependency that causes deadlocks, and your banking API becomes robust under real‑world traffic spikes. 🚀

8

- ✅ Success: Bob → Alice  
- ✅ Success: Alice → Bob  
- ✅ Success: Bob → Alice  
- ✅ Success: Alice → Bob  
- …  

**Success:** 20  
**Deadlocks:** 0  

Zero crashes. Perfect consistency.  

Deadlocks aren’t random bad luck. They are predictable consequences of inconsistent locking.  
If you touch multiple rows in a single transaction, always touch them in the same order.  
Sort your IDs. Save your database.