Chapter 27 — Exercises
Several use two
psqlsessions to observe locking/conflicts. Wrap in transactions you canROLLBACK. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — Lost updates
A1 (27.1) Describe the lost-update (read-modify-write) race with a concrete inventory example. Why is it invisible in single-user testing? (answer in Appendix)
27.2 Show two ways to safely decrement stock: (a) arithmetic in one SQL statement; (b) SELECT ... FOR UPDATE then update. When do you need (b) over (a)?
27.3 ⭐ Simulate the race with two sessions: both read a counter, both write +1; observe an update lost. Then fix it and re-test.
Group B — Locks & FOR UPDATE
27.4 What does SELECT ... FOR UPDATE do? In two sessions, lock a row in T1 and watch T2's update wait. (answer in Appendix)
27.5 Difference between row-level and table-level locks? Which do ordinary UPDATEs take?
27.6 ⭐ Why does doing the arithmetic in SQL (SET x = x - 1) avoid the lost update without an explicit FOR UPDATE?
Group C — Optimistic concurrency
27.7 Add a version column and write a compare-and-swap UPDATE that only succeeds if the version is unchanged. (answer in Appendix)
27.8 What does the app do when the optimistic UPDATE affects 0 rows? Why is that correct?
27.9 ⭐ When is optimistic better than pessimistic, and vice versa? Tie to contention level.
Group D — Deadlocks
27.10 Create a deadlock with two sessions (T1 locks A then B; T2 locks B then A). What does PostgreSQL do? (answer in Appendix)
27.11 How does consistent lock ordering prevent that deadlock? Give the rule.
27.12 ⭐ Why should the application retry on a deadlock error rather than treat it as fatal?
Group E — Advisory locks & pooling
27.13 Use pg_advisory_lock/pg_advisory_unlock to serialize a job across sessions. When is an advisory lock the right tool? (answer in Appendix)
27.14 Why does a connection-per-request web app overwhelm the database at scale, and how does a pool fix it?
Group F — Progressive project
27.15 Identify a read-modify-write in your domain and make it safe (atomic SQL or FOR UPDATE).
27.16 Add optimistic locking (version column) to one update path.
27.17 ⭐ If your operation locks multiple rows, define a consistent lock order and note your deadlock-retry plan.
Self-check. If you can prevent a lost update two ways, cause and prevent a deadlock, and explain when to pool connections, you can build correct high-concurrency systems. Next: the internals that explain it all.