Experiments and implementations · Intermediate

Reproducing write skew across isolation levels

Schedule two on-call updates in separate sessions and distinguish same-row conflicts from write skew.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. State the invariant first
  2. Prepare two connections and an isolated table
  3. Ensure both reads precede the updates
  4. Explain why no serial order matches
  5. Repeat the history under Serializable
  6. Review alternative enforcement mechanisms
  7. Ask Logeia about this articleCheck your understanding

State the invariant first

The invariant is that at least one doctor remains on call. With two available doctors, either may leave if acting alone. If two transactions make that decision concurrently, neither may remain. Compare the same transaction pattern under PostgreSQL 18 Repeatable Read and Serializable. This is a two-session reproduction procedure, not an observed database transcript. Use a disposable local database rather than production scheduling information or shared application tables. Both transactions inspect the on-call population, including the other doctor, before changing their own row. The experiment concerns that cross-row decision, not merely two writes happening at similar times.

Prepare two connections and an isolated table

Open terminals A and B against the same experimental database. Perform setup once in a new lab_skew schema. CREATE SCHEMA intentionally has no IF NOT EXISTS, so an existing name stops setup instead of silently reusing unknown data. Choose another unused name if necessary rather than overwriting an existing schema. Labels A and B in the code indicate different connections. Pasting the entire block into one connection does not create the intended concurrent history. Keep a record of session identity and statement order, because reproducing an isolation anomaly requires the specified interleaving as well as the specified SQL statements.

Ensure both reads precede the updates

Begin both sessions in Repeatable Read and let each observe two on-call doctors before either update. A changes row one and commits; B then changes row two and commits. PostgreSQL Repeatable Read can permit a serialization anomaly of this form because different rows are modified. Protection against concurrent updates to the same row does not enforce this population-wide invariant. Finally inspect the count with a fresh query, distinguishing that result from each transaction’s earlier snapshot. If the interleaving is changed so B first reads only after A commits, the intended history has changed and its result cannot be used as the same experiment.

PostgreSQL 18 SQL — two-session procedure
-- Setup once in a disposable local database, outside transactions.
-- CREATE SCHEMA deliberately fails if this name already exists.
CREATE SCHEMA lab_skew;
CREATE TABLE lab_skew.doctors(id integer PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO lab_skew.doctors VALUES (1,true),(2,true);
-- A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lab_skew.doctors WHERE on_call;
-- B, before A changes a row:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lab_skew.doctors WHERE on_call;
-- A:
UPDATE lab_skew.doctors SET on_call=false WHERE id=1 AND (SELECT count(*) FROM lab_skew.doctors WHERE on_call)>1;
COMMIT;
-- B:
UPDATE lab_skew.doctors SET on_call=false WHERE id=2 AND (SELECT count(*) FROM lab_skew.doctors WHERE on_call)>1;
COMMIT;
-- Either session, outside the preceding transaction:
SELECT count(*) FROM lab_skew.doctors WHERE on_call;
-- Reset only after both sessions finish, then repeat using SERIALIZABLE:
UPDATE lab_skew.doctors SET on_call=true;

Explain why no serial order matches

If A ran completely first, B would observe only one remaining doctor and decline to leave. Reversing the serial order would make A decline instead. A result where both observe two and both leave matches neither serial execution of that decision-making application. This is the write-skew issue. The application must actually contain the rule that it declines when only one doctor remains; two unconditional updates alone do not explain that business operation. Trace read dependencies and the affected updates together. The inconsistency concerns the joint decision and invariant rather than simply the presence of two committed false values.

Repeat the history under Serializable

After both earlier transactions have finished, reset the rows and repeat the read-write interleaving with both sessions in Serializable. A conflicting dependency pattern can cause one transaction to abort with a serialization failure. Do not require the same connection or statement to fail in every environment. The application must retry the whole transaction, including the original read and decision, rather than resubmit only the last UPDATE with stale reasoning. External effects such as sent messages are not automatically undone by a database rollback. Their coordination requires another design in addition to choosing a stronger isolation level.

Review alternative enforcement mechanisms

Possible alternatives include a consistent locking protocol over the relevant rows or a data-model change that makes the invariant directly enforceable. A simple per-row CHECK constraint does not by itself count other on-call rows. If rows can be inserted or removed, reevaluate whether the proposed row locks cover the decision. For any selected approach, test concurrent execution, retry handling and changes to membership. When cleaning up the reproduction environment, target only the dedicated schema created for this experiment. The observation should inform an explicit invariant-enforcement design rather than encourage blanket assumptions that one keyword or one row lock fixes every cross-row anomaly.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path