Bank Ledger Design: Concurrency, Locks, Race Conditions

In this chapter

Concurrency, locks and race conditions: build a bank ledger

Ledger concurrency: common questions

Does a database transaction prevent a lost update?

Not at READ COMMITTED. Two transactions can both read the same balance and both write. Use an atomic UPDATE (balance = balance - x) or lock the row with SELECT … FOR UPDATE.

How do you prevent deadlocks when transferring money?

Lock the two accounts in the same order every time, for example smallest id first. A deadlock needs a circle of waiting, and a single global order makes that circle impossible.

How do you stop a retried payment from charging twice?

Send an idempotency key and store it with a unique constraint per client. A retry hits the constraint, and you return the original result instead of creating a second transfer.

More on Transactions: locks and production

• Optimistic vs Pessimistic Locking in Databases
• Database Locks Explained: Shared, Exclusive, FOR UPDATE
• Distributed Transactions: Two-Phase Commit and Sagas
• Database Transaction Mistakes and How to Avoid Them

Keep learning

• Previous: Database Locks Explained: Shared, Exclusive, FOR UPDATE
• Next: Distributed Transactions: Two-Phase Commit and Sagas
• All SQL tutorial chapters

Overview

1 · Lost update Alice has $500. Two withdrawals of $100 arrive at the same moment. Withdrawal T1 Withdrawal T2 SELECT balance … → 500 T1 reads 500 T2 also reads 500. Nothing stops it. Also 500 − 100. It overwrites T1’s result. Alice took out $200 but still has $400. The bank just made up $100, and no error was logged. A timing problem. Fixed by making read-and-write one step, or by a lock. 2 · Deadlock You added locks. Now Alice pays Bob while Bob pays Alice. T1: Alice → Bob T2: Bob → Alice lock Alice ✓ T1 holds Alice lock Bob ✓ T2 holds Bob lock Bob … waiting Bob is held by T2 lock Alice … waiting Alice is held by T1. Each waits for the other forever. ERROR: deadlock detected After about 1 s (Postgres) the database kills one of them. Both transactions were written correctly. The problem is the order they took the locks in. Under load this becomes a stream of 1-second stalls and errors. An ordering problem. Fixed by always taking locks in the same order. 3 · Double spend Alice’s app sends “pay Bob $100”. The reply is lost on the network, so the app sends it again. Request 1 Request 2 (retry) POST /transfers $100 → Bob Locks taken correctly, commits ✓ Bob +100 … reply times out The app never hears back Bob +200 ✕ Each request was valid and locked correctly. The database just could not tell that they were the same payment. An identity problem. No lock can fix it. Only an idempotency key can. ← Back Next step → Number type FLOAT dollars BIGINT cents Whole numbers are always exact. Show $0.30 only when you display it. A float cannot hold 0.1 exactly. Add it up a million times and the books stop matching. 1. Read, then write read → compute → write The app reads, does the maths, then writes. The gap between read and write is where money disappears. Wrapping it in BEGIN … COMMIT does not close the gap: at READ COMMITTED (the default in Postgres) both transactions are allowed to read 500. A transaction gives you all-or-nothing, not “one at a time”. 2. One atomic UPDATE 3. SELECT … FOR UPDATE pessimistic lock, any order Locks both accounts until COMMIT, so you can run any business logic in the middle. The hidden bug: IN (:from, :to) does not say which row is locked first. The query plan decides, so 7→3 and 3→7 can grab locks in opposite orders and deadlock. 4. Lock in id order same order everywhere A deadlock needs a cycle: “I hold the bigger id and wait for the smaller one” on one side, the reverse on the other. If everyone locks smallest id first, that cycle cannot exist. It is a proof, not a “makes it rarer”. It only holds if every path obeys it: batch jobs, admin tools, the 2 am migration script. 5. Optimistic version check detect clashes, retry No lock while you think. Great when clashes are rare (a person’s own account). Terrible on a hot row: everyone reads the same version, one wins, all others retry and clash again. The queue becomes a retry storm that burns CPU and connections. 6. Never update a balance 7. Snapshot + recent tail fast derived balance Summing 10 million rows is slow, so store “balance as of entry N” and add only newer entries. Writes are still plain inserts. If the snapshot is wrong or lost, rebuild it from the entries: it is a speed-up, not the truth. Two $100 withdrawals at once Alice → Bob and Bob → Alice at once 10,000 people pay one shop in a sale Check limits and fraud before moving money Balance must never go below zero Show the balance fast (10 M entries) Both read 500, both write 400. $100 appears from nowhere. No deadlock, but both balances can be overwritten: wrong money instead of an error. Lost updates on almost every pair of payments. You can run rules, but on a balance that may already be stale. One column read. Fast, but often wrong. The second UPDATE waits, then re-checks: 500 → 400 → 300. Correct. Two statements per transfer. If the two transfers update rows in opposite orders, they still deadlock. Correct, but every payment waits its turn on the shop’s row. Keep the transaction tiny. Only rules you can write in WHERE. No room for fraud checks in between. AND balance_minor >= :amount: 0 rows changed means “not enough money”. One column read. The second transaction waits for the lock, then reads the new balance. IN (7, 3) may lock 7 first in one transaction and 3 first in the other. Deadlock, 1 s stall, one error. Everyone queues single file on the shop’s row, for the whole transaction. This is the reason to pay for it: any logic can run while you hold the lock. Read under lock, then decide. Same as FOR UPDATE: the second waits. Both lock Alice (smaller id) first. One waits before holding anything, so no cycle can form. Any logic, under lock. The second writer finds the version changed, re-reads 400, retries: 300. No locks are held while waiting, so no deadlock. Clashes become retries. Retry storm: 10,000 writers read the same version, one wins, 9,999 retry, again and again. Nothing is updated, so nothing can be lost. Two entries of −100: balance 300. Inserts do not wait for each other. No locks to order, no cycle. Each payment is a new row. 10,000 inserts run side by side. Rules that need the current balance need a consistent read: use holds (below). Two inserts can both pass a balance check. Use holds, or serialise only accounts that must never go negative. SUM over 10 M rows on every page view is slow. Next rung. Still append-only underneath. Still inserts only. Same as append-only: use holds for rules that need the live balance. Same as append-only: holds. Snapshot + a few hundred new rows. Fast, and rebuildable from entries if it breaks. Lock order Order chosen by the query plan Always smallest id first T1 holds account 7 and waits for 3. T2 holds 3 and waits for 7. The arrows form a circle: deadlock. T1 holds 3 and wants 7. T2 waits for 3 and holds nothing. No circle: T2 simply waits, then runs. pays 7 → 3 pays 3 → 7 account 3 account 7 holds 7 waits for 3 holds 3 waits for 7 next: takes 7 waits for 3, holds nothing A circle of waiting: deadlock. No circle: T2 just waits a few milliseconds. T2 asks for account 3 first, which T1 holds, so T2 waits before holding anything. T1 finishes, T2 runs. A circle would need someone to hold a bigger id while waiting for a smaller one, which the rule forbids. Pessimistic (FOR UPDATE): everyone waits in line, each runs once Optimistic: each round all retry, only one wins Few writers: optimistic wins. Nobody waits, and a rare retry is cheap. Some contention: optimistic starts wasting work on retries. Pessimistic is steadier. A hot row: optimistic turns into a retry storm. Pessimistic is a long queue. The real fix is to stop updating one row: append entries. Start: deposit $100 Card swipe $30: post a HOLD entry Swipe card for $30 Shop settles: HOLD becomes a posted −30 Shop settles Hold expires or is cancelled: release entry Hold expires Try to swipe $80 posted balance (settled entries) active holds Every step is an insert, so the ledger stays append-only. This is the “pending” charge you see on your card. No key Two transfers, Bob gets $200 The retry looks like a brand-new payment. Bigger locks do not help: both payments are valid on their own. Unique key, retry later One transfer, the retry gets the original answer Unique key, both at once The second request waits, then gets the first one’s answer First one still PENDING 202 Accepted: still in progress, poll here Retry case Retry (same key) INSERT transfer … → commit Bob +200 INSERT … key='pay-81' → commit INSERT … key='pay-81' → unique violation INSERT … key='pay-81' (not committed yet) INSERT … key='pay-81' … waits on the index INSERT … state='PENDING' → commit INSERT … → unique violation, row is PENDING Step 1 on shard 3: Alice −100, Clearing +100 Step 2 on shard 8: Clearing −100, Bob +100 Step 2 fails: Bob’s account is closed Compensate on shard 3: Clearing −100, Alice +100 Both steps work Step 2 fails Next → Shard 3 Shard 8 The $100 in flight sits in a real account you can see, query and alert on. Clearing is back to $0: nothing is stuck. You wrap “read balance, subtract, write balance” in BEGIN … COMMIT at READ COMMITTED. Two withdrawals run at once. What happens? Safe: the transaction makes them run one at a time A transaction is all-or-nothing, not a lock. Both are allowed to read the same committed 500. The second one gets a serialization error That can happen at Postgres REPEATABLE READ or SERIALIZABLE, not at READ COMMITTED. Both read 500, both write 400: one update is lost Nothing stops the second read, so the second write overwrites the first. It deadlocks Only one row is involved and the reads take no lock, so there is nothing to wait for in a circle. A client retries a transfer after a timeout and is charged twice. What fixes it? Lock both accounts with FOR UPDATE Both requests already locked correctly. Locks stop overlap, not repeats. A Redis distributed lock Same problem: it serialises the two requests, then both still run. SERIALIZABLE isolation Two separate valid payments are serializable. The database does not know they are the same. A unique idempotency key per client, returning the first result Follow-up: why scope the key to the client? Two customers both sending “order-1001” is normal. UNIQUE(api_client_id, idempotency_key). Why does locking accounts in id order prevent deadlock completely? It makes lock waits shorter Wait time does not matter. A deadlock is about the shape of waiting, not its length. A wait cycle would need someone holding a bigger id while waiting for a smaller one, which the rule forbids With one global order, the wait-for graph cannot form a circle. The database detects the deadlock faster Detection is the fallback. Ordering means there is nothing to detect. It only makes deadlocks rarer It is a proof, not a mitigation, as long as every code path follows it. Follow-up: one batch job locks in a different order. What happens? The proof breaks, and deadlocks come back only under load, so tests rarely catch it. 10,000 people pay one shop during a sale. Which design copes best? Optimistic locking with a version column SELECT … FOR UPDATE on the shop’s row Correct, but all 10,000 queue single file on one lock. Each payment is a new row. Inserts do not wait for each other. Read, then write, but faster servers Follow-up: how do you keep the balance read fast? Snapshot plus the recent tail, rebuilt by a background job. In an append-only ledger, how do you stop an account going negative? Run SUM, then insert if it is enough Two requests can both see enough money and both insert: the race is back. The reservation is an insert too, so the ledger stays append-only. For accounts that must never dip, serialise just that account. Edit the old entry to reduce it Entries are never edited. Corrections are new reversing entries. Holds solve it. This is how card authorisations work. Follow-up: a hold is never settled. What now? Give holds an expiry and post a release entry when it passes. Sender is on shard 3, receiver on shard 8. What is usually the best choice? Two-phase commit Works, but holds locks across the network and can block rows if the coordinator dies. Debit on shard 3, credit on shard 8, and hope If step 2 fails, $100 is simply gone. A saga through a clearing account, with a compensating entry Every step balances, in-flight money is visible in Clearing, and a failure is undone by a new entry. A distributed lock over both shards Follow-up: how would you notice a stuck transfer? Alert when the clearing account is not near zero for more than a few minutes. Not quite. Try another. chapters · transactions: locks and production Concurrency, locks and race conditions: build a bank ledger Interview question (asked at Coinbase): design a bank account ledger. It looks like simple CRUD for about 90 seconds, until someone asks what happens when two transfers touch the same account at once. We climb a ladder of seven designs. Each fixes the last one and creates a new problem, which is exactly how the interview goes.

1. Three different races, not one

Most answers treat “concurrency” as one problem with one fix. It is three problems with three different fixes. Step through each one: Lost update Fixed by an atomic UPDATE or a lock. Fixed by taking locks in one fixed order. Double spend Fixed only by an idempotency key. ! A transaction is not a lock BEGIN … COMMIT makes the steps all-or-nothing. At READ COMMITTED it does not stop a second transaction from reading the same row at the same time. More in isolation levels and wallet balance is wrong under load.

Money is a whole number

Store cents (or paise, or satoshis) in a BIGINT. A float cannot hold 0.10 exactly, and a ledger’s whole job is to be exact.

Entries come in pairs that add up to zero

A transfer writes two rows: −100 on the sender, +100 on the receiver. This is double-entry bookkeeping, about 600 years old. It turns a whole class of bugs into one query you can run all day:

The idempotency key is unique

It looks like a small detail. In section 7 it stops double charges without any lock at all.

3. The ladder: seven designs, from broken to solid

Pick a rung. Read the SQL, then see how it handles six real situations.

4. Why lock order kills deadlocks

Draw who waits for whom. A deadlock is a circle in that drawing. Switch the rule and watch the circle disappear. Guarantee vs habit. ORDER BY id FOR UPDATE locks in sorted order in practice, but that follows the query plan. For a real guarantee, sort the ids in your code and lock one row per statement. Everyone must obey. Every path that takes two locks, including batch jobs and admin tools. One exception brings deadlocks back, and only under load. Still keep a retry on deadlock errors as a safety net. See checkout fails with “Deadlock found” and locks.

6. The big move: stop updating balances

Every rung so far was a different way to safely change one number. Rung 6 removes the number. The balance is the sum of the entries, and entries are only ever inserted. That deletes race 1 (nothing is overwritten) and race 2 (inserts do not lock each other) by design.

But what about “never below zero”?

Checking the balance and then inserting is a read-then-write again. The answer is a hold:…

More courses

• Jobs, linked to lessons — Browse jobs where every requirement links to the chapter that teaches it. See how ready you are, how long the gap takes to close, and which listings look stale.
• Match a job post to courses — Paste a job post. Each skill is matched to a track here and put in learning order, foundations first.
• React from open source with patterns — Patterns extracted from Material UI, Radix, React Aria, React Hook Form, Redux Toolkit, TanStack, and Next.js — not slides about hooks.
• System Design — Core concepts first (SQL vs NoSQL, CAP, indexing, replication, sharding), then designs grouped by what carries them: the database, Redis, Kafka, or many services.
• Build a real API with NestJS — One ticket-booking API, built in order: a plain Node server first, then NestJS modules, validation, a real database, and the features real clients lean on.
• Microservices — Microservice patterns one diagram at a time: how to split a monolith, how services talk, and how data stays consistent without distributed transactions.
• Kafka — A beginner-to-senior path through event logs, reliable delivery, production operations, and disaster recovery.
• AI engineering for software engineers — Eight free reads, in order, for software engineers moving into AI: when to build an agent, how to build one, how to test it, and how the model works underneath.
• Frontend system design — How a UI is sliced when it is a product, not a page: ownership of state, cache, routes, and what happens when the network lies.
• TypeScript in depth — The type system as a design tool. Inference, narrowing, generics, and the errors that mean the model is wrong — not a catalog of utility types.
• CSS in depth — See the box, then the page. Visual chapters from the box model through Flexbox surprises, Grid, selectors, and units, then backgrounds, shapes, and visual effects, each with a playground.
• Accessibility — Foundations first (WCAG in plain words, semantic HTML, lists, alt text), then hands-on HTML labs, a full pass of WAI-ARIA 1.3, and a final fix-the-page challenge.
• Docker — What an image is, why layers cache, and how containers find each other on a network.
• Redis — Fifteen Redis patterns, one picture each: cache, session, lock, counter, rate limiter, queue, and when Redis is the wrong tool.
• Kubernetes — Kubernetes from zero to operators: why it exists, Pods, Deployments and Services, then configuration, networking, storage, scheduling, scaling and security.