Transfer locking and final balance

The idea

Without locking, two transfers that both read an account's balance can each write back a value computed from the same stale number — the second write silently overwrites the first. That is the lost update anomaly, and money simply vanishes from the ledger.

With row locks acquired in a fixed order, transfers that touch a shared account serialize: one runs to completion before the other reads. Each transfer is balanced (one debit, one matching credit), so the total across all accounts is a conserved invariant — it is the same before and after, no matter how the work interleaves.

How it works

Each transfer opens a transaction, then locks the rows it will touch in account-id order. Acquiring locks in the same global order on every code path is what stops two transfers from grabbing the same pair in opposite orders and deadlocking. Inside the lock it reads, checks funds, debits and credits, then commits and releases.

# Transfer money safely under row locks.
def transfer(conn, src, dst, amount):
    # Lock both accounts in a FIXED order (ascending id),
    # so concurrent transfers can never deadlock on the pair.
    first, second = sorted((src, dst))

    conn.execute("BEGIN")
    # SELECT ... FOR UPDATE takes a row lock and blocks
    # any other transfer touching the same account.
    lock_row(conn, first)
    lock_row(conn, second)

    bal_src = read_balance(conn, src)
    if bal_src < amount:
        conn.execute("ROLLBACK")        # check funds INSIDE the lock
        return "insufficient funds"

    write_balance(conn, src, bal_src - amount)          # debit
    write_balance(conn, dst, read_balance(conn, dst) + amount)  # credit

    conn.execute("COMMIT")              # releases both locks
    return "ok"

Trade-offs

Watch out for

• Lost update without locking. Two transfers read the same balance, both compute from the stale value, and the second write erases the first. Money disappears from the ledger.
• Deadlock from inconsistent lock order. If one path locks A then B and another locks B then A, they can wait on each other forever. Always sort by account id.
• Holding locks too long. Network calls, user think-time, or extra queries inside the lock widen the window for contention. Lock late, commit early.
• Forgetting the funds check inside the lock. Validate the balance after the lock is held; a check made before locking can be stale by commit time and lets an account go negative.
• Reading outside the lock. A balance read without the row lock can be overwritten before you write it back — the very anomaly the lock exists to prevent.

Worked example

Start with A = $100, B = $50, C = $80, so the total is $230. T1 transfers $30 from A to B; T2 transfers $20 from C to A. Both T1 and T2 touch account A, so they contend on it. Locking in account-id order, T1 holds A first and runs to completion: A = $70, B = $80 (total still $230). Only then does T2 read the committed A = $70, debit C to $60, and credit A to $90. Final balances are A = $90, B = $80, C = $60 — total $230, unchanged. Had T2 read A before T1 committed, its write would have lost T1's $30 debit; the lock is exactly what prevents that.

Check yourself