Why Snapshot Isolation Still Lets Business Rules Break

Problem 1: a lost update replaces another change

A lost update is the simplest race. Two requests read the same value, calculate independently, and one later write overwrites the other. The database ends at 6 even though two likes were recorded.

Unsafe read, calculate, write

-- Request A and request B both read 5
SELECT likes FROM posts WHERE id = 42;

-- both application processes calculate 6
UPDATE posts SET likes = 6 WHERE id = 42;

-- final value is 6, not 7

Best fix: make one database operation express the change

UPDATE posts
SET likes = likes + 1
WHERE id = 42
RETURNING likes;

Atomic updates are the best option when the rule fits in one operation. They also work for local JSON-document changes and Redis data-structure operations. The database can protect the object while applying the change, rather than asking application code to coordinate a read-modify-write cycle. Be careful with an ORM: loading a record, changing it in memory, and saving a whole replacement quietly brings the race back.

For a one-row limit, make the condition part of the update:

UPDATE events
SET seats_remaining = seats_remaining - 1
WHERE id = :event_id
  AND seats_remaining > 0
RETURNING seats_remaining;

No returned row means the seat was already taken or the request lost the race.

When the rule needs application logic

Lock the existing object, validate while the lock is held, then update. A game move is a good example: the database cannot know every game rule, but it can stop two players moving the same piece at once.

BEGIN;
SELECT * FROM figures
WHERE name = 'robot' AND game_id = 222
FOR UPDATE;

-- validate the move in application code
UPDATE figures SET position = 'c4' WHERE id = 1234;
COMMIT;

FOR UPDATE locks every returned row. It works only if every competing transaction takes the same relevant locks in a consistent order before it validates. One forgotten path restores the race.

Automatic lost-update detection and repeatable read

Another approach lets the transactions run in parallel, detects the same-row conflict, and aborts one. The application must then retry the whole transaction.

PostgreSQL REPEATABLE READ
  same row changed concurrently
    database aborts one transaction
    application retries whole transaction

  shared rule read, different rows changed
    both may commit
    rule can still be broken

In PostgreSQL, REPEATABLE READ gives a transaction a stable view and detects a concurrent update to the same row. It is not a promise that every business rule survives concurrent requests.

💡 Do not treat the name REPEATABLE READ as portable. The book specifically contrasts PostgreSQL, which detects same-row lost updates, with MySQL/InnoDB repeatable read, which does not provide that protection.

Compare-and-set

UPDATE wiki_pages
SET content = :new_content,
    version = version + 1
WHERE id = :id
  AND version = :version_seen
RETURNING version;

Zero returned rows means reload and retry or show a conflict. Verify that a database evaluates compare-and-set against current state. A predicate evaluated from an old snapshot may not protect the update. It also does not magically merge two arbitrary text edits. That can be modeled as a stream of mutations, but it is much harder than a counter increment.

Replication changes the problem

Locks and compare-and-set assume one current copy of a value. Asynchronous multi-leader and leaderless replication can accept concurrent writes on different replicas, so those techniques do not directly apply.

Replica A: tags = [green]
Replica B: tags = [blue]

Last-write-wins: one legitimate update disappears

Such systems may retain conflicting versions, called siblings, for application code or a special data type to merge later. Commutative operations, such as incrementing a counter or adding an item to a set, are easier because applying them in a different order reaches the same result. Last-write-wins is convenient but loses legitimate data and is a default in many replicated databases.

Problem 2: write skew breaks a shared rule

Now the requests update different rows, so neither overwrites the other. Alice and Bob are the only doctors on call. Each checks that two doctors are available, then each takes themselves off call.

BEGIN;
SELECT count(*) FROM doctors
WHERE shift_id = :shift AND on_call = true;  -- both see 2

UPDATE doctors
SET on_call = false
WHERE id = :my_id;                           -- different rows
COMMIT;

flowchart TD
  A[Alice reads 2 on call] --> C[Alice sets Alice off call]
  B[Bob reads 2 on call] --> D[Bob sets Bob off call]
  C --> E[Both commit]
  D --> E
  E --> F[Zero doctors remain on call]

This is write skew. It is not a lost update or dirty write: both writes succeed, but their combined effect violates the invariant. Run serially, the second request would have seen only one doctor and stopped.

Correct fix

If every row used by the decision already exists, lock all of them before deciding:

BEGIN;
SELECT id FROM doctors
WHERE shift_id = :shift AND on_call = true
FOR UPDATE;

-- verify coverage, then update one doctor
COMMIT;

The general answer is true serializable isolation. It must reject an execution whose result could not occur in some serial order. The application retries the whole transaction after an abort.

💡 REPEATABLE READ handles a same-row collision, not this cross-row rule. PostgreSQL can allow the doctor race under repeatable read; SERIALIZABLE is the stronger isolation level for this kind of invariant.

Problem 3: a phantom leaves nothing to lock

Meeting-room booking has the same shape, but the dangerous row does not exist yet. Two transactions both find no overlapping booking, then each inserts one.

BEGIN;
SELECT count(*) FROM bookings
WHERE room_id = :room
  AND end_time > :start
  AND start_time < :end;   -- both see 0

INSERT INTO bookings(room_id, start_time, end_time)
VALUES (:room, :start, :end);
COMMIT;

flowchart TD
  A[Booking A finds no overlap] --> C[Booking A inserts]
  B[Booking B finds no overlap] --> D[Booking B inserts]
  C --> E[Overlapping bookings exist]
  D --> E

A phantom is a write that changes which rows match another transaction's search condition. FOR UPDATE cannot lock an empty result, so it cannot protect this booking query. Where supported, serializable isolation plus a whole-transaction retry is the general fix.

Use a direct constraint when the database can express the rule. PostgreSQL range types make this booking rule concrete:

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
ADD CONSTRAINT no_overlapping_bookings
EXCLUDE USING gist (
  room_id WITH =,
  tstzrange(start_time, end_time, '[)') WITH &&
);

The [) range includes its start and excludes its end, so adjacent bookings may meet without overlapping. The book also gives smaller versions of this pattern: use a UNIQUE constraint for usernames or a board square, and do not use a check-then-insert flow.

Last resort: materialize a conflict

When an important predicate has no row to lock, create a separate lock target. A booking system can pre-create room and time-slot rows, lock every needed slot, then validate and insert the actual booking.

SELECT * FROM room_slots
WHERE room_id = :room
  AND slot_start >= :start
  AND slot_start < :end
FOR UPDATE;

Those rows are not booking data. They only turn an absent-row phantom into a concrete lock conflict. It is awkward and easy to get wrong, so prefer a direct constraint or serializable isolation when either fits.

Choose the mechanism from the invariant

flowchart TD
  A[Name the invariant] --> B{One current row}
  B -->|yes| C[Atomic UPDATE or CAS then handle no row]
  B -->|no| D{Known existing rows}
  D -->|yes| E[Lock rows then validate]
  D -->|no| F{Constraint can express rule}
  F -->|yes| G[Use database constraint]
  F -->|no| H[Serializable transaction and retry]
  H --> I[Materialize locks only if needed]

Ask what the bad concurrent outcome is before choosing an isolation label. One value, known rows, an absent matching row, and concurrent replicas are different shapes of conflict. The smallest mechanism that makes the invalid state impossible is usually the clearest design.

These are my personal learning notes from Designing Data-Intensive Applications by Martin Kleppmann.