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.
postgresql