Transaction Isolation in PostgreSQL and InnoDB: Read Rules and Anomalies
Compare engine-specific snapshots and locks instead of relying on isolation-level names alone. A write-skew example shows what to verify when choosing a level and retry policy.
Publication & corrections: infostoa · Editorial standards
Individual author and specialist reviewer information is not provided for this article.
ACID does not encode every business invariant
ACID describes atomicity, consistency, isolation, and durability. Consistency does not mean the database infers every business rule; constraints and transaction design must express those rules. Isolation-level names also do not guarantee identical behavior across engines.
A dirty read observes an uncommitted value; a non-repeatable read returns a changed value for a row read earlier; a phantom changes the set of rows matching a predicate. These are not the only concern: successfully committed transactions can still produce a result that is inconsistent with every serial ordering, a serialization anomaly.
Compare the engine’s read rules
| Setting/engine | Snapshot for ordinary reads | Important caveat | What to verify |
|---|---|---|---|
| PostgreSQL Read Committed (default) | A snapshot at each statement start | Two SELECTs in one transaction can see different committed states | Do not treat a multi-statement check as one stable snapshot |
| PostgreSQL Repeatable Read | Transaction snapshot from the first ordinary statement | Prevents dirty, non-repeatable, and phantom reads, but serialization anomalies remain; conflicting updates can abort | Check write-skew exposure and whole-transaction retry handling |
| PostgreSQL Serializable | Conflict monitoring to ensure committed results are serial-equivalent | A transaction may fail with a serialization error rather than wait | Can the application retry the whole transaction on SQLSTATE 40001? |
| MySQL InnoDB Repeatable Read (default) | Ordinary consistent reads use a snapshot established by the first read | Locking reads can see a different state; next-key/gap locks may block inserts depending on predicate and index | Check SELECT ... FOR UPDATE/SHARE and the scanned index range |
PostgreSQL maps requested Read Uncommitted to Read Committed. Its Repeatable Read is stronger than the standard minimum because it prevents phantoms, but it does not prevent every serialization anomaly. InnoDB ordinary SELECTs are consistent snapshot reads; locking reads and UPDATE/DELETE follow separate lock rules. Do not generalize this as “reads never block writes.”
Hypothetical example: two on-call staff both sign off
Assume two staff are on call and at least one must remain active. Transactions A and B each read that two people are active, then deactivate a different staff row. Because each changes a different row, both may commit while leaving zero active staff under snapshot-based Repeatable Read. This illustrates possible write skew; it is not a claim that every engine and configuration behaves this way.
To protect the invariant, verify whether Serializable plus whole-transaction retry is suitable for the actual engine/version, or consider a shared guard row or constraint design. Do not infer safety merely because each transaction updates one row.
A practical review sequence
- Record the product, storage engine, version, and configured isolation level.
- Identify ordinary versus locking reads and whether snapshots are statement- or transaction-scoped.
- Express the invariant as a concurrent schedule and classify the risk: lost update, write skew, or phantom.
- Choose a constraint, lock, or stronger level, then retry the whole transaction on deadlock or serialization failure.
- Reproduce concurrent sessions in a test environment and measure latency, errors, and retry cost.
The scenario is hypothetical and was not run against a database. Use the versioned vendor documentation below for implementation details.
Sources: PostgreSQL 18 Transaction Isolation, MySQL 8.4 InnoDB Transaction Isolation, MySQL 8.4 Consistent Nonlocking Reads, MySQL 8.4 Locking Reads.