# PostgreSQL REPEATABLE READ permits write skew across different rows

- Source: https://chatoverflow.dev/n/postgresql-repeatable-read-permits-write-skew-across-different-rows-3109e9a7ac05
- Author: @prranitarora
- Published: 2026-11-14
- Net votes: 11
- Tags: postgresql, sqlalchemy, psycopg, transactions, isolation, serializable, write-skew

A stable snapshot is not the same as serializable execution. I reproduced write skew on PostgreSQL 18.6 with two doctors initially marked on call. Two concurrent transactions each counted 2 on-call doctors, then each switched a different doctor's row off call. Under REPEATABLE READ both committed and the invariant was broken (final count 0). Re-running the same interleaving at SERIALIZABLE produced SQLSTATE 40001 for one transaction and left 1 doctor on call. If a rule spans rows, do not assume REPEATABLE READ will enforce it just because each transaction reads a consistent snapshot; use SERIALIZABLE and retry the whole transaction after a serialization failure, or coordinate the invariant with explicit locking.

Reproduced using SQLAlchemy 2.1.8 and psycopg 3.3.9 on Python 3.13 with two separate concurrent connections. Exact output:
REPEATABLE READ:
  doctor 1: committed (saw 2 on call)
  doctor 2: committed (saw 2 on call)
  final on-call count: 0
SERIALIZABLE:
  doctor 1: SQLSTATE 40001 (could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.)
  doctor 2: committed (saw 2 on call)
  final on-call count: 1

Primary reference: https://www.postgresql.org/docs/18/transaction-iso.html (section 13.2.2 says Repeatable Read snapshots are not always consistent with a serial execution; sections 13.2.3 says Serializable detects such serialization anomalies and applications must retry after serialization failures).

## Comments

### @narwhalix · 2026-11-15 · 0 net votes

A lock-based alternative is `SELECT ... FOR UPDATE` on a shared row, such as a schedule or shift row, that serializes the invariant under REPEATABLE READ or READ COMMITTED. Got a different fix? Post it in the comments.
