PostgreSQL REPEATABLE READ serialization limits on concurrent updates

Write Skew in REPEATABLE READ

PostgreSQL's REPEATABLE READ isolation level prevents phantom reads, but it does not prevent write skew. Write skew occurs when two concurrent transactions read the same set of data, but update different, disjoint rows in a way that violates a business constraint that depends on the combined state of those rows.

Example Scenario

Imagine a requirement that at least one doctor must be on call. Two doctors, Alice and Bob, are both currently on call. Both start transactions in REPEATABLE READ mode:

• Transaction A: Reads the table, sees two doctors on call, and updates Alice's status to 'off call'.
• Transaction B: Reads the table, sees two doctors on call, and updates Bob's status to 'off call'.

Because they updated different rows, no row-level lock conflict occurs. Both transactions commit successfully, leaving zero doctors on call. The SERIALIZABLE level would prevent this by tracking read dependencies (SIREAD locks) and aborting one of the transactions with a serialization failure.

Serialization Errors vs. Duplicate Key Errors

While both result in a failed operation, they trigger under different MVCC and indexing mechanisms.

Serialization Error (SQLSTATE 40001)

This occurs when a transaction attempts to UPDATE or DELETE a row that was modified and committed by another transaction after the current transaction's snapshot was taken. In REPEATABLE READ, the engine cannot "merge" the changes into the existing snapshot, so it aborts the transaction to maintain consistency.

Duplicate Key Error (SQLSTATE 23505)

This occurs when a transaction attempts to INSERT or UPDATE a value that violates a UNIQUE index. Unlike serialization errors, unique constraints are enforced regardless of the snapshot time.

Feature	Serialization Error (40001)	Duplicate Key Error (23505)
Trigger	Concurrent update to the same row	Violation of unique index
Behavior	Immediate abort of transaction	Wait for conflicting txn to commit/rollback
Cause	MVCC snapshot conflict	Physical index constraint

Verification Commands

To observe the serialization error, run these in two concurrent sessions (assuming PostgreSQL 12+):

-- Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- (Wait for Session 2 to commit)
COMMIT;

-- Session 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
COMMIT;

Session 1 will fail with could not serialize access due to concurrent update upon attempting to commit or update if Session 2 has already committed.