Reads come from a photocopy. Writes hit the live row.
When the copy is stale, PostgreSQL’s REPEATABLE READ refuses the write with 40001. InnoDB’s applies it, silently.
Fixing the wallet race
Bad: Raise the isolation level
InnoDB’s default REPEATABLE READ still loses the debit, silently.
Good: One conditional UPDATE
balance = balance - 300 WHERE … balance >= 300, then check one row changed.
Let the database do the read-modify-write
T2 waits for T1’s row lock, then re-checks the newest balance, ₹200. Correct at READ COMMITTED, with no retries.
Max two tatkal holds. How does user 7 get three?
Each request counts one hold, inserts a different row and commits. Snapshot isolation only stops two writes to the same row.
Different rows, so neither engine’s REPEATABLE READ sees a conflict.
The fix
Make them fight over one row: lock the user row FOR UPDATE first, or bump a per-user counter row. Or run PostgreSQL’s SERIALIZABLE.
40001 is normal. Retry the whole transaction
- Restart from BEGIN and re-read everything
- No emails or payments inside; use an outbox
- Idempotency key for a COMMIT with no reply
- Bound it: 5 tries, jittered backoff
3.6M
old row versions one two-hour report pins
At 500 updates a second, VACUUM or InnoDB purge must keep every old version until the report commits. Time-limit it, or run it on a replica.
Remember
Level names mislead, so name the engine and the anomaly, then the fix: one conditional UPDATE for lost updates; a locked parent row or SERIALIZABLE with retries for rules spanning rows.