Two ₹100 top-ups to ₹500. Why ₹600, not ₹700?
Each read 500 before the other wrote, so the conflict graph has a cycle. No one-at-a-time order gives ₹600; the schedule isn’t serializable.
Lock each row as you go?
Bad: Unlock when done with it
T2 slips between your locks; a cycle still forms.
Good: Two-phase locking
Never lock again after a release. Strict 2PL holds every lock until commit.
Two transfers lock the same accounts in opposite order
Each waits for the other, forever. The database finds the cycle in its wait-for graph and rolls one back; the victim retries.
No locks: save only if version 17 is unchanged
Two requests read stock 8 at version 17. The first update wins and bumps it to 18. The second matches 0 rows, re-reads and retries.
Meera and Kabir both see “2 on shift” and sign off. Different rows, so snapshot isolation commits both.
The fix
SSI also tracks reads. Two consecutive rw edges, and PostgreSQL aborts one: 40001, retry.
10
tries per commit when 90% of attempts conflict
Optimism needs 1 / (1 − p) attempts, 1.1 at p = 0.1 and 2 at 0.5. Every failed try adds load, so the next conflict gets likelier.
A 5 ms lock caps a hot row at 200/s
- Shrink the lock to one conditional UPDATE
- Shard the counter across 10 rows
- Queue purchases through one owner
- Reserve with a TTL, confirm later
Remember
Concurrency control makes transactions look serial. Choose by contention, version checks when conflicts are rare, locks for short contended updates, a single writer for hot rows, and always a retry loop.