Transactions & Concurrency

Read Committed and Repeatable Read

Read Committed normally chooses a fresh committed view per statement; Repeatable Read aims for stability across a transaction, but locking, snapshot timing, predicate handling, and even supported names differ materially across engines.

Read Committed prevents dirty reads, not stale decisions.
Repeatable Read is not one algorithm.
Plain and locking reads differ.
Snapshot scope matters.
Writes still need a concurrency strategy.

Fuzzy read under Read Committed

Start: order 42 status=PENDING; invariant: one transaction’s approval decision uses one stated order state.
T1 reads PENDING. T2 writes CANCELLED and commits. T1 reads CANCELLED.
Both values were committed, but T1 did not observe a stable premise.

Current vendor behavior—verify configuration and access path
Engine / level	Typical read view	Important qualification
PostgreSQL 18 RC	Statement snapshot	Each command may see a newer commit
PostgreSQL 18 RR	Transaction snapshot	Snapshot isolation; serialization-style update conflicts, no phantoms in docs table
MySQL 8.4 InnoDB RC	Fresh snapshot per consistent read	Gap locking reduced; locking reads differ
MySQL 8.4 InnoDB RR	Snapshot from first consistent read	Default; next-key locks for locking/index scans
SQL Server 2025 RC	Locking RC or statement row versions	`READ_COMMITTED_SNAPSHOT` changes mechanism
SQL Server 2025 RR	Shared locks retained to commit	New predicate rows can still appear
Oracle 26ai RC	Statement-level read consistency	Default; no named RR level
SQLite	Serialized writes; snapshot isolation in WAL	`read_uncommitted` matters only with shared cache; one writer

The table deliberately does not collapse equal names. PostgreSQL Repeatable Read is snapshot-based; SQL Server Repeatable Read is lock-based and does not protect new qualifying rows; Oracle exposes Read Committed and Serializable rather than a separately named Repeatable Read.