Under PostgreSQL's REPEATABLE READ, what happens when an UPDATE targets a row changed and committed after the snapshot, and what must the application do?
answer
- one snapshot, frozen at the first query
- could not serialize access
- SQLSTATE class 40
- rerun from the top, not the statement
basics
~20 sIt waits for the other writer; if that transaction commits an update or delete of the row, the UPDATE fails with could not serialize access due to concurrent update, SQLSTATE 40001. The application must roll back and rerun the whole transaction.
solid answer
~50 s`REPEATABLE READ` in PostgreSQL is snapshot isolation: the snapshot is frozen at the transaction's first query or data-modifying statement, not at `BEGIN`. `UPDATE`, `DELETE`, `MERGE` and `SELECT ... FOR UPDATE/SHARE` find rows by that snapshot. If a target row is being changed by another transaction, the statement waits; on rollback it proceeds, but if the other transaction commits an actual update or delete (not just a lock), the statement fails with `ERROR: could not serialize access due to concurrent update` and SQLSTATE `40001`. The transaction is now aborted. The client must `ROLLBACK` and rerun the entire transaction — its reads and the logic that computed the values — so the retry gets a new snapshot that includes the other change. PostgreSQL never retries for you, and a read-only REPEATABLE READ transaction never meets this conflict.
code
sql · 10 lines-- Booking A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT seats_left FROM events WHERE event_id = 42; -- snapshot frozen: 10
-- Booking B (autocommit) runs and commits meanwhile:
-- UPDATE events SET seats_left = seats_left - 2 WHERE event_id = 42;
UPDATE events SET seats_left = seats_left - 2 WHERE event_id = 42;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK; -- then rerun the whole transaction from BEGINgo deeper
Recall that REPEATABLE READ can abort a transaction with SQLSTATE 40001 and that the fix is to roll back and run the whole transaction again.
Explain when the snapshot freezes, the wait-then-fail rule for a committed update or delete, and why a row that was only locked does not fail.
Design the retry wrapper: detect by SQLSTATE, rerun the whole unit of work, bound attempts with back-off, keep external side effects out, and decide whether 40P01 and 23505 qualify.
Decide which code paths deserve REPEATABLE READ and its retries versus READ COMMITTED with guarded updates, and how one shared retry helper becomes a team standard.
## Snapshot isolation, PostgreSQL's way PostgreSQL implements **REPEATABLE READ** as **snapshot isolation**. A transaction at this level reads from one **snapshot** — the set of transactions that had committed — for its whole life, so repeated queries return the same data and rows committed later never appear. The snapshot is not taken at `BEGIN`: it is frozen at the **first query or data-modification statement** (`SELECT`, `INSERT`, `UPDATE`, `DELETE` or `MERGE`), so anything committed between `BEGIN` and that statement is still visible. Before PostgreSQL 9.1 this behaviour was what a request for SERIALIZABLE produced; it is now requested as REPEATABLE READ. Reading from an old snapshot is harmless. Writing is not: if the row you want to change has been replaced by a version your snapshot cannot see, updating your stale copy would silently throw away the other transaction's work. ## The first-updater-wins rule When a writing command reaches its target row, PostgreSQL checks whether a concurrent transaction has changed it: 1. If the other transaction is **still running**, the command waits for it to finish. 2. If it **rolls back**, the command proceeds with the row it found. 3. If it **commits** and it actually **updated or deleted** the row, the command fails with `ERROR: could not serialize access due to concurrent update` (or `... concurrent delete`) and SQLSTATE **`40001`** (`serialization_failure`). 4. If the other transaction only **locked** the row and committed, there is no newer version, and the command proceeds. After the error the transaction is aborted; every further statement fails until `ROLLBACK`. ## Commands that raise it - `UPDATE`, `DELETE` and `MERGE` on a row changed after the snapshot. - `SELECT ... FOR UPDATE` or `FOR SHARE` (and the other row-locking clauses) on a row changed after the snapshot. - `INSERT ... ON CONFLICT` when the conflicting row was inserted by a transaction the snapshot cannot see — the command will not act on a row it is not allowed to know about. - A read-only REPEATABLE READ transaction never meets this conflict: only writers and lockers do. ## One statement under two levels In the ticketing service, two bookings for the same event both run `UPDATE events SET seats_left = seats_left - 2 WHERE event_id = 42`: | | READ COMMITTED | REPEATABLE READ | |---|---|---| | Second booking while the first is open | waits | waits | | First commits | second re-checks the row and decrements the new value | second fails with `40001` | | First rolls back | second proceeds | second proceeds | | Work left for the application | check the affected-row count | roll back and rerun the transaction | READ COMMITTED can quietly continue because each statement may see newer data. REPEATABLE READ promised a stable view, so it refuses to act on data it cannot see. ## Retrying correctly PostgreSQL has no automatic retry, because only the application knows what it decided from the data it read. A correct retry wrapper: 1. Detects the error by **SQLSTATE**, not message text — the message varies, the code `40001` does not. 2. Issues `ROLLBACK`, then reruns the **whole transaction** — every read, every computation and every write — in a new transaction, which gets a new snapshot. 3. Bounds the number of attempts and adds a short randomised back-off; under heavy contention several attempts may be needed. 4. Keeps side effects outside the database, such as emails or payment calls, out of the retried block, or makes them idempotent. The manual's guidance on which codes to retry: | SQLSTATE | Name | Retry? | |---|---|---| | `40001` | `serialization_failure` | yes, unconditionally | | `40P01` | `deadlock_detected` | usually advisable | | `23505` | `unique_violation` | in some cases, with care | | `23P01` | `exclusion_violation` | in some cases, with care | The last two can be a serialization problem the server cannot recognise, such as two sessions choosing the same new key after inspecting the existing ones, but they can also be permanent, so retry them only where the code knows the conflict is a race. ## Locks and the frozen snapshot If you protect a REPEATABLE READ transaction with an explicit lock, take the lock **before** the first query. A lock acquired afterwards guarantees nobody else is modifying the table now, but the snapshot already predates changes committed while you waited, so the transaction would act on stale data. Which lock to take is PostgreSQL's locking subject rather than its isolation one.
- Why must the retry rerun the transaction's reads too, not just the failed UPDATE?The values the transaction wrote were derived from what it read in the old snapshot. Replaying only the `UPDATE` in a new transaction would apply decisions made on stale data — the lost change the error exists to prevent. Rerunning from `BEGIN` gives a new snapshot that includes the other commit, so the logic decides again on current data.
- A REPEATABLE READ transaction runs SELECT ... FOR UPDATE on an event row another session had only locked FOR SHARE and then committed; does it fail?No. The `40001` error fires only when the concurrent transaction actually updated or deleted the row. A row that was merely locked and released has no newer version, so the REPEATABLE READ transaction waits for the lock if it must and then proceeds.
- Which SQLSTATEs besides 40001 does the PostgreSQL manual suggest retrying, and why carefully?It calls retrying `40P01` (`deadlock_detected`) advisable, and in some cases `23505` (`unique_violation`) and `23P01` (`exclusion_violation`), for example when two sessions pick the same new key after reading the existing ones. Those two can also mean a genuine, permanent duplicate, so retry them only where the code knows the conflict is a race, with a bounded number of attempts.
- REPEATABLE READ takes its snapshot when BEGIN is executed.
- After a 40001, re-issuing just the failed statement is enough.
- PostgreSQL retries serialization failures automatically under REPEATABLE READ.
- A read-only REPEATABLE READ report can fail with could not serialize access due to concurrent update.
- Matching the error message text is a reliable way to detect serialization failures.