Transactions, isolation and locking interview questions
ACID, isolation levels as PostgreSQL implements them, lost updates and write skew, row locks, MVCC, deadlocks and retries.
– interview-ready · 14 questions in this topic
– of 14 tried
Saved on this device. Interview-ready means you got it right on two different days. Guesses and hints count for less.
Questions
- Write skew under REPEATABLE READPredict
- What ACID really promisesPick the fix
- Isolation levels in PostgreSQLPick the fix
- The lost update problemFill the blank
- SELECT FOR UPDATE and SKIP LOCKEDFill the blank
- Optimistic vs pessimistic lockingPick the fix
- Retrying serialization failuresSpot the bug
- MVCC, dead tuples and VACUUMPredict
Practise this topic
Flashcards · 19
Flip, then say if you knew it. Needs JavaScript; every answer is also on its question page.
Quick quiz · 14 questions
One exercise per question, checked as you go. Needs JavaScript.
Mock interview · 2 sets
Timed, out loud, then tick what you covered. Needs JavaScript.
Fix this PR · 2 reviews
Review a diff like a senior: flag the lines you'd comment on. Needs JavaScript.
Quick facts
Recall the answer first, then tap Show to check.
Default isolation in PostgreSQL
READ COMMITTED. Each statement sees a fresh snapshot of committed data.
SQLSTATE 40001
serialization_failure: retry the whole transaction. 40P01 is deadlock_detected, also retryable.
SQLSTATE 25P02
in_failed_sql_transaction: an earlier statement failed, so everything fails until ROLLBACK.
deadlock_timeout
Default 1 s. PostgreSQL only checks for a deadlock cycle after a lock wait lasts this long.
Transaction ids
PostgreSQL transaction ids are 32-bit, so VACUUM must freeze old rows before about 2 billion transactions pass.
Printable cheat sheet · Review your weak spots in this topic
Share your progress
Sources: Topic outline informed by System Design Primer by Donne Martin and the Zalando RESTful API Guidelines, both licensed under CC BY 4.0. Every question and answer is written by Best Latest Apps. Credits and license