Chapter 27 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. The lost-update problem occurs when: - A) Two transactions read the same value, each computes a new one, and one write overwrites the other - B) A transaction reads uncommitted data - C) A query is slow - D) An index is missing

Q2. SELECT ... FOR UPDATE: - A) Reads without locking - B) Reads and locks the row so others must wait to modify it - C) Deletes the row - D) Creates an index

Q3. Pessimistic concurrency control: - A) Assumes conflicts are rare and retries - B) Locks up front, assuming conflicts will happen - C) Never uses locks - D) Only works for reads

Q4. Optimistic concurrency control typically uses: - A) Table locks - B) A version column + compare-and-swap, retrying on conflict - C) Advisory locks - D) No transactions

Q5. A deadlock is: - A) A slow query - B) A cycle of transactions each waiting for a lock the other holds - C) A corrupted index - D) A crash

Q6. PostgreSQL handles a deadlock by: - A) Hanging forever - B) Detecting it and aborting one transaction (which should retry) - C) Ignoring it - D) Restarting the server

Q7. The main way to prevent deadlocks is: - A) More indexes - B) Acquiring locks in a consistent order across transactions - C) Bigger hardware - D) Read Uncommitted

Q8. Doing UPDATE x SET n = n - 1 WHERE ... in one statement: - A) Risks a lost update - B) Is atomic on the row, avoiding the read-modify-write race - C) Requires FOR UPDATE - D) Causes deadlocks

Q9. An advisory lock is for: - A) Locking a row - B) Application-defined coordination of non-row resources (a named mutex) - C) Indexing - D) Backups

Q10. Connection pooling solves: - A) Slow queries - B) Connection exhaustion from too many (often idle) connections at scale - C) Deadlocks - D) Missing indexes

True/False

Q11. Optimistic concurrency is best when contention on the same row is low. (True / False)

Q12. Deadlocks are catastrophic and should crash the application. (True / False)

Short answer

Q13. Two concurrent "buy the last item" requests both read stock = 1 and both succeed, overselling. Explain the bug and two ways to fix it.

---

Answer key

Q1 — A. Read-modify-write race; one increment/decrement is lost.

Q2 — B. Locks the row; others wait to modify it (pessimistic).

Q3 — B. Lock up front, assume conflict.

Q4 — B. Version column + compare-and-swap + retry.

Q5 — B. Cyclic lock wait.

Q6 — B. Detected; one transaction aborted; retry.

Q7 — B. Consistent lock ordering breaks the cycle.

Q8 — B. Single-statement arithmetic is atomic on the row.

Q9 — B. App-defined named lock for non-row coordination.

Q10 — B. Caps/reuses connections so the server isn't overwhelmed.

Q11 — True. Low contention → conflicts (and retries) are rare → optimistic wins.

Q12 — False. Deadlocks are recoverable; catch the error and retry the transaction.

Q13. It's a lost-update / read-modify-write race: both requests read stock = 1 before either decrements, so both pass the check and both sell. Fix 1 (pessimistic): SELECT quantity ... FOR UPDATE to lock the row, so the second request waits and sees stock = 0. Fix 2 (atomic SQL): UPDATE inventory SET quantity = quantity - 1 WHERE product_id = ... AND quantity > 0 in one statement and check that 1 row was affected — only one decrement can succeed when stock is 1. (Optimistic with a version column also works.)

Scoring: 11–13 you can build correct concurrent systems; 8–10 review lost updates and deadlocks; below 8, do the two-session exercises.