You have a payment system. Two users try to spend from the same wallet balance at the same time.
Both read $200. Both want to spend $150. Both see enough balance. Both write the deduction.
The wallet is now at -$100. How do you stop this?
A) Pessimistic locking — SELECT FOR UPDATE on the wallet row. One transaction blocks until the other commits.
B) Optimistic locking — read the row with a version number, only write if version hasn't changed since you read it. Retry on conflict.
C) MVCC — let both reads see a consistent snapshot, rely on the database to detect write conflicts at commit time.
D) Serializable isolation — set the transaction isolation level to SERIALIZABLE and let the database handle it.
All four are real production strategies. One of them will silently allow double-spend under concurrent load. One will destroy your throughput on a high-write table. One is almost always the wrong default choice despite being the "safe" option.
Pick one — and tell me which one you'd actually ship in a payment system processing 10K transactions/second.
Full breakdown in the comments.
If you're building anything with concurrent writes — share this. It's the class of bug that costs money before you catch it.
Drop your answer 👇
Top comments (4)
Why B wins (optimistic locking):
You read the wallet row, grab the version number. When you write, you include a WHERE version = :read_version condition. If another transaction already updated the row, your WHERE matches zero rows — conflict detected, retry.
UPDATE wallets
SET balance = balance - 150, version = version + 1
WHERE id = :wallet_id AND version = :read_version;
If rows_affected = 0 → conflict → retry. No locks held during the read. At 10K TPS with low conflict rates, this is significantly faster than pessimistic locking — you're only paying for retry cost on actual conflicts, not lock acquisition on every read.
The catch: under HIGH contention (same hot wallet hit repeatedly), retry storms eat you alive. Hot wallets need Redis INCRBY with atomic decrement, or a queue in front of the wallet update.
Why A is the trap answer (pessimistic locking):
SELECT FOR UPDATE works. It's correct. But it holds a row-level lock for the entire transaction — every concurrent write queues up.
At 10K TPS on a popular wallet, you've serialized all writes to a single queue. Throughput collapses. Lock wait timeouts cascade — failed transactions retry, adding more contention. You've built a self-reinforcing bottleneck.
Use pessimistic locking for low-concurrency paths (admin ops, batch jobs) where conflict probability is near 100%. Not for high-throughput payment writes.
Why C is the silent failure (MVCC + default isolation):
This is the dangerous one. PostgreSQL defaults to READ COMMITTED. MVCC gives each transaction a consistent snapshot — but at READ COMMITTED, that snapshot refreshes per statement, not per transaction.
Two concurrent transactions both read $200, both pass the "sufficient funds" check, both commit. No conflict detected. Double spend in production.
MVCC prevents dirty reads. It does NOT prevent lost updates at READ COMMITTED. Most engineers assume MVCC = safe from concurrent writes. It doesn't.
Why D is almost always the wrong default (SERIALIZABLE):
Theoretically correct — the database guarantees transactions behave as if they ran serially. No anomalies.
The problem: PostgreSQL's SSI tracks read-write dependencies across transactions. Under high concurrency, serialization failures spike — transactions abort and retry even when there was no actual conflict. Real throughput penalty at scale.
Right for complex financial workflows where correctness > throughput (end-of-day reconciliation). Wrong default for a high-throughput per-transaction API.