repeatable-read-isolation · also Repeatable Read, REPEATABLE READ

Repeatable Read isolation

Repeatable Read is a transaction isolation level whose SQL minimum forbids dirty and nonrepeatable reads without requiring phantom-free or serializable execution.

Related Observatory article: Repeatable Read isolation on hesela.com

Implementations may be stronger than the SQL minimum; the name is not a portable synonym for snapshot isolation. PostgreSQL 18 prevents phantoms and uses the snapshot from the first non-transaction-control statement, plus the transaction's own writes, but serialization anomalies remain possible. [1]

MySQL 8.4 InnoDB normally establishes the snapshot at the first consistent nonlocking read. Locking reads and UPDATE or DELETE use a different visibility boundary; a write can affect rows hidden from an earlier ordinary SELECT. Own writes remain visible. [3] [4]

START TRANSACTION WITH CONSISTENT SNAPSHOT can establish the InnoDB snapshot at transaction start; the modifier does not change the isolation level and is effective only at Repeatable Read. [5]

To recover from a PostgreSQL serialization failure with SQLSTATE 40001, retry the complete transaction, including its decisions, and another attempt can fail. Repeatable Read does not guarantee serializability or specify crash durability. [2] [1]

A snapshot does not freeze table storage. PostgreSQL TRUNCATE and table-rewriting ALTER TABLE can leave a table empty to an older snapshot that had not accessed it; prior access holds an ACCESS SHARE lock that blocks the DDL. [6]

InnoDB consistent reads do not span DROP TABLE or copy-based ALTER TABLE that replaces the original table; the latter can return ER_TABLE_DEF_CHANGED and require a transaction retry. [3]

PostgreSQL 16.15 COPY FREEZE exposed later rows to an old snapshot after loader commit; same-host tests, not independent replication. [8] [9]

Relations

related
Snapshot isolation, Read Committed isolation, Serializability, Serializable Snapshot Isolation, Write skew

Citations

  1. PostgreSQL 18: Transaction Isolation, Table 13.1 and section 13.2.2. (accessed 2026-11-13)
  2. PostgreSQL 18: Serialization Failure Handling, section 13.5. (accessed 2026-11-13)
  3. MySQL 8.4: Consistent Nonlocking Reads, section 17.7.2.3. (accessed 2026-11-13)
  4. MySQL 8.4: InnoDB Transaction Isolation Levels, section 17.7.2.1. (accessed 2026-11-13)
  5. MySQL 8.4: START TRANSACTION, COMMIT, and ROLLBACK Statements, section 15.3.1. (accessed 2026-11-13)
  6. PostgreSQL 18: MVCC Caveats, section 13.6. (accessed 2026-11-13)
  7. PostgreSQL 16: COPY, FREEZE option. (accessed 2026-11-13)
  8. Hesela: PostgreSQL 16.15 heap replacement and frozen import, method and reproduction commands. (accessed 2026-11-13)

Further reading

  1. Hesela: Repeatable Read needs an engine and a statement type. (accessed 2026-11-13)
Sources & caveats
  • Primary-documentation synthesis accessed 2026-11-13; not a native-engine experiment or an original measurement. SQL minimum is reported via PostgreSQL Table 13.1; the ISO standard was not independently inspected.
  • PostgreSQL 18 and MySQL 8.4 InnoDB behavior is scoped separately to ordinary transactional-table reads and writes. No database-wide frozen-state or backup guarantee is claimed.
  • Separate native supplement: twelve PostgreSQL 16.15 cases; synthetic inputs, native results and same-host replay, not independent replication or PostgreSQL 18 execution. All target reads follow writer completion. Earlier PostgreSQL 18/MySQL 8.4 comparison remains documentation synthesis.

JSON: /data/terms/repeatable-read-isolation.json

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

Cite this page
Hesela team. Repeatable Read isolation. Hesela. 2026-11-13. https://hesela.dev/terms/repeatable-read-isolation/