Transactions and Isolation Levels

Understand what transactions guarantee, how isolation levels differ, and how lost updates happen — through two live sessions running against a real PostgreSQL database.

Most backend bugs involving double-charges, negative stock, or vanishing writes trace back to a misunderstood isolation level. This course fixes that from the ground up. We start by showing exactly what happens when a money transfer runs without a transaction — and what autocommit leaves behind when the process dies mid-flight. Then we wrap the same transfer in BEGIN/COMMIT and watch Postgres make it atomic. From there we compare the three isolation levels side by side on two live sessions: Read Committed shows non-repeatable reads in action; Repeatable Read holds a consistent snapshot; Serializable catches the write-skew cycle and aborts with a real error you can reproduce. We close with the lost-update problem on an inventory table — two sessions both reading stock=50 and both writing 49 — and show the two standard fixes: an atomic UPDATE that never reads first, and SELECT FOR UPDATE that serialises access at the row level.

What you'll build

• Open a transaction with BEGIN, commit it, and roll it back
• Explain why partial changes without a transaction are permanent
• Observe non-repeatable reads at Read Committed with two live sessions
• See Repeatable Read hold a stable snapshot across concurrent commits
• Reproduce a real serialization failure and understand the retry contract
• Demonstrate lost update on an inventory table and explain why it happens
• Fix lost update with an atomic UPDATE expression
• Fix lost update with SELECT FOR UPDATE and understand the trade-off

Contents

1. A database for concurrent sessions
2. Start it
3. Connect with psql
4. The accounts table
5. The transfers table
6. The inventory table
7. Create the tables
8. The seed data
9. Load the data
10. Confirm the starting balances
11. Three statements, no transaction
12. It works — until it doesn't
13. Simulate a crash mid-transfer
14. The money is gone
15. Reset to clean state
16. Wrap it in BEGIN and COMMIT
17. The atomic transfer
18. The transfer landed correctly
19. The audit log was written
20. Reset before the rollback demo
21. What happens when something goes wrong inside a transaction
22. The error and the rollback
23. The balance is intact
24. Reset before the isolation demos
25. Two sessions, two terminals
26. Session A opens a Read Committed transaction
27. Session A reads 1000.00
28. Session B commits a change while A is still open
29. Session B updated and committed
30. Session A reads again — sees the new value
31. Reset for the Repeatable Read demo
32. Session A opens a Repeatable Read transaction
33. Session A reads 1000.00 at Repeatable Read
34. Session B commits the same update
35. Session A reads again — still 1000.00
36. Back to the default after COMMIT
37. Reset for the Serializable demo
38. Session A opens a Serializable transaction and reads
39. Session A reads the total balance
40. Session B also runs at Serializable and writes
41. Session B committed successfully
42. Session A tries to write and commit — but Postgres says no
43. Reset for the lost-update demo
44. The read-modify-write race condition
45. Session A reads stock and holds the transaction open
46. Session A sees stock = 50
47. Session B reads the same 50, writes 49, and commits
48. Session B committed — stock is now 49
49. Session A writes its own 49 — one decrement is lost
50. Reset before the fix
51. Fix 1: never read first — keep the logic inside the UPDATE
52. Atomic update returns the new stock
53. Reset for the SELECT FOR UPDATE demo
54. Fix 2: lock the row at read time with FOR UPDATE
55. Session A holds the lock on row 1
56. Session B tries to lock the same row — and blocks
57. Session B is waiting
58. Session A decrements and releases the lock
59. Session B unblocks and sees the current value
60. The stand is still here
61. Choosing the right isolation level
62. Atomic update versus SELECT FOR UPDATE