Build fluency in the vocabulary of getting full serializability without pessimistic, upfront locking.
0 / 5 completed
1 / 5
At standup, a dev mentions a database isolation level that gives transactions the full guarantee of running as if they'd executed one at a time, while still letting them execute concurrently most of the time. What is this isolation level called?
Serializable snapshot isolation, or SSI, gives transactions the full guarantee of running as if they'd executed one at a time, while still letting them execute concurrently for most of their duration. Read uncommitted isolation offers no such guarantee and instead exposes a transaction to another transaction's uncommitted, possibly-rolled-back writes. This concurrent-yet-fully-serializable behavior is what makes SSI attractive compared to literally running every transaction one at a time.
2 / 5
During a design review, the team wants the database to detect a dangerous pattern of overlapping reads and writes between two concurrent transactions and abort one of them, rather than locking rows pessimistically up front. Which capability supports this?
Detection of rw-antidependency conflict cycles between concurrent transactions lets SSI spot a dangerous overlapping read-write pattern and abort one of the involved transactions, without locking every row pessimistically up front. Pessimistic locking of every row a transaction might touch sacrifices concurrency for transactions that would never actually have conflicted. This targeted conflict detection is what lets SSI offer full serializability with far less locking overhead than a pessimistic scheme.
3 / 5
In a code review, a dev notices two transactions both execute concurrently against their own consistent snapshot, and only get validated for a dangerous conflict pattern at the moment each tries to commit. What does this represent?
SSI's optimistic execution lets both transactions run concurrently against their own consistent snapshot, with validation for a dangerous conflict pattern happening only at the moment each transaction tries to commit. Validating for a conflict before either transaction even begins executing would require predicting a conflict that hasn't happened yet. This optimistic, commit-time validation approach is what lets SSI avoid the overhead of locking rows the whole time a transaction is running.
4 / 5
An incident report shows a subtle write-skew bug slipped through production under plain snapshot isolation for months, and was only caught after the team migrated the affected transactions to serializable snapshot isolation, which then correctly aborted one of the two conflicting transactions. What practice would prevent this recurring?
Running the invariant-sensitive transactions under serializable snapshot isolation detects a dangerous rw-antidependency pattern and aborts one of the conflicting transactions automatically, closing exactly the gap that let the bug slip through under plain snapshot isolation. Continuing to run them under plain snapshot isolation, with no additional detection, leaves the same class of bug free to recur. This migration to SSI is a standard fix once a write-skew anomaly has actually been identified in production.
5 / 5
During a PR review, a teammate asks why the team adopts serializable snapshot isolation instead of two-phase locking to get a full serializability guarantee. What is the reasoning?
Two-phase locking holds locks pessimistically for the duration of every transaction, hurting concurrency even for transactions that would never actually have conflicted with each other. SSI lets transactions run optimistically against their own snapshot and only pays an abort-and-retry cost when a genuine dangerous conflict is actually detected at commit time. The tradeoff is that a transaction under SSI can occasionally be aborted and need to retry, which two-phase locking's pessimistic approach avoids by blocking upfront instead.