Chapter 26 — Exercises
Some exercises use two
psqlsessions side by side to observe concurrency. Wrap changes in transactions you canROLLBACK. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — ACID
A1 (26.1) Define each of A, C, I, D in one sentence with a Mercado example. (answer in Appendix)
26.2 Explain why a money transfer must be atomic. What can go wrong without a transaction?
26.3 Which ACID property does the write-ahead log primarily guarantee, and how? (answer in Appendix)
Group B — Transactions & savepoints
26.4 Write a transaction that places an order: insert into orders, insert into order_items, all-or-nothing. Test that a deliberate error rolls back everything. (answer in Appendix)
26.5 Use a SAVEPOINT to undo one failed step while keeping the rest of a transaction.
26.6 ⭐ Demonstrate atomicity: start a transaction, make two changes, force an error, and confirm neither change persisted.
Group C — Isolation phenomena (two sessions)
26.7 Demonstrate a non-repeatable read under Read Committed: in T1 select a row twice; between the selects, have T2 update and commit it. Observe the change. (answer in Appendix)
26.8 Repeat 26.7 under REPEATABLE READ and explain why the second read is now stable.
26.9 ⭐ Demonstrate a phantom: T1 counts rows matching a condition twice; T2 inserts a matching row and commits between. Then show REPEATABLE READ prevents it in PostgreSQL.
26.10 Why does PostgreSQL not really provide "dirty reads" even at Read Uncommitted?
Group D — Isolation levels
26.11 Name PostgreSQL's default isolation level and what it does (and doesn't) prevent. (answer in Appendix)
26.12 For each scenario, pick an isolation level: a simple lookup; a report computing several totals that must agree; a complex invariant across rows that two transactions could violate.
26.13 ⭐ Write the application retry loop pattern for SERIALIZABLE, and explain why retries are expected.
Group E — MVCC
26.14 Explain how MVCC lets readers and writers not block each other. (answer in Appendix)
26.15 ⭐ What are "dead tuples," why does MVCC create them, and what cleans them up? (Forward to Chapter 28.)
Group F — Progressive project
26.16 Wrap a multi-step operation in your domain in a transaction; test atomicity with a deliberate failure.
26.17 Identify a read that needs a stable snapshot and choose an isolation level.
26.18 ⭐ Find an invariant two concurrent transactions could break and reason about Serializable + retry.
Self-check. If you can wrap multi-step work atomically, demonstrate a non-repeatable read and prevent it with a higher level, and explain MVCC in a sentence, you understand transactional correctness. Next: the locking mechanics underneath.