Transactions and consistency
Gems gone, no watering can
A player buys a golden watering can for 300 gems. The server takes the gems, then crashes before adding the can, and the player writes to Priya.
PostgreSQL's tutorial uses a bank: Alice pays Bob $100. A crash must never leave Bob paid without Alice's money taken, or Alice's money taken without Bob paid.
All or nothing
A transaction "bundles multiple steps into a single, all-or-nothing operation." If anything fails partway, none of the steps take effect.
BEGIN;UPDATE players SET gems = gems - 300 WHERE id = 42;INSERT INTO items VALUES (42, 'golden_can');COMMIT;
Quick check
The server crashes between the two steps inside a transaction. What does the player have?
- All 300 gems and no watering can: nothing happened
- No gems and no can
- The gems and the can
Show the answer
All 300 gems and no watering can: nothing happened
A transaction that never reaches COMMIT has no effect at all, so the player can simply try again.
ACID
Transactions promise four things:
- AtomicAll the steps happen, or none do.
- ConsistentThe rules hold: gems never go below zero.
- IsolatedOthers cannot see half-done work: changes are "invisible to other transactions until the transaction completes".
- DurableOnce reported done, it is saved to permanent storage and survives a crash.
Changing your mind: ROLLBACK
If a check fails partway, say the player has only 200 gems, the server sends ROLLBACK instead of COMMIT. PostgreSQL: "all our updates so far will be canceled."
Quick check
Another player can see the 300 gems leave before the watering can arrives.
- True
- False
Show the answer
False
Isolation hides a transaction's changes until it commits. Then all of them appear at once.
Bug hunt
1BEGIN;2UPDATE players3 SET gems = gems - 3004 WHERE id = 42;5COMMIT;6INSERT INTO items7 (player, item)8 VALUES (42, 'gold_can');
Players sometimes lose gems and get nothing. Which line of Remy's code is the bug?
- Line 5: COMMIT comes before the INSERT, so the two steps are not one transaction
- Line 1
- Line 8
Show the answer
Line 5: COMMIT comes before the INSERT, so the two steps are not one transaction
The INSERT runs on its own after the transaction ended. COMMIT belongs after both steps.
One database, one weak spot
Transactions keep one database correct. But if that one database server goes down, every purchase stops.
Bolt says: Next: replicas, live copies of the database.
Fact-checked 12 October 2026
- PostgreSQL: Transactions postgresql.org