Anomalies by engine
The same isolation name does not imply the same operation contract. This comparison concerns PostgreSQL 18 and MySQL 8.4 InnoDB transactional tables, under the transaction boundaries in the linked lessons. Own writes, sequences, stale values read outside the protected transaction, and external effects require separate scope.
D is a demonstrated asserted schedule, not all executions. M is a Documented contract in the linked engine catalog/manual. † is an Entailed guarantee without a direct Scenario at that level. Its derivation is in the linked catalog's “How to use this table” section. The comparison preserves every reference topic, including unexecuted predicate and read-only variants.
Each row states the weakest relevant exclusion for its specified operation, not one universal ladder for all SQL. PostgreSQL accepts READ UNCOMMITTED as READ COMMITTED; InnoDB has a distinct READ UNCOMMITTED implementation.
| Code | Anomaly or operation | PostgreSQL | MySQL InnoDB |
|---|---|---|---|
| G0 | Overwrite an uncommitted target | M: target row locks at every level; D: RC wait | † target locks at every level; D: RU wait |
| G1a | Read concurrent uncommitted table changes | M: excluded at every level; D: RU alias | M: excluded from RC; D: dirty RU value |
| G1b | Intermediate draft read | † excluded at every level; D: RC example | † excluded from RC; D: RU/RC examples |
| G1c | Cycle through dirty cross-reads | † excluded at every level; D: RC example | † excluded from RC; D: RU/RC examples |
| OTV | Committed rows hidden by an uncommitted overwrite | † excluded at every level; D: RC example | † excluded from RC; D: RU/RC examples |
| P2 | Repeated plain consistent SELECT after a concurrent commit | M: excluded at RR; D: stable rows | M: excluded for consistent SELECTs at RR; D: stable rows |
| G-single | Read skew | † excluded between RR plain reads without own modifications; D: RC/RR totals | † excluded between RR consistent reads without own modifications; D: totals; mixed DELETE/SELECT differs; † SERIALIZABLE participating locks |
| PMP | Changed matching set after a concurrent commit | M: excluded for RR plain reads; D: new row excluded | M: excluded for RR consistent SELECTs; current DML can reach post-snapshot rows; † SERIALIZABLE retained range locks, derivation |
| P4 | Read and stale literal write inside the shown transaction | M: RR changed-target rejection; D: 40001 and fresh retry | D: loss at RC/RR; † SERIALIZABLE retained read locks, derivation, no direct P4 execution at that level |
| G2-item | On-call count and different-row writes | M: SERIALIZABLE serial equivalence; D: RR loss and B's rejection | † SERIALIZABLE rule protection; D: RR loss and B's 1213 |
| G2 | Predicate variant beyond the on-call schedule | M: SERIALIZABLE serial equivalence; no predicate Scenario here, catalog | † SERIALIZABLE participating range locks; no predicate Scenario here, derivation |
| Fekete example | Read-only report anomaly | M: SERIALIZABLE serial equivalence; D: RR results and SERIALIZABLE cashier rejection | Not catalogued or executed here |
† Every derived cell above lacks a direct execution of its universal exclusion. Exclusive target locks exclude dirty overwrites; excluding concurrent uncommitted versions excludes intermediate drafts and dirty cross-reads/OTV. A stable consistent snapshot excludes intervening commits between those reads, provided own changes do not alter the observed rule. Retained SERIALIZABLE read/range locks prevent competing writers invalidating protected reads/predicates before the transaction ends. These are the catalogs' explicit derivations, not stronger-level guarantees inferred from a single weaker-level run.
A cycle with both old reads can still lack a serial ordering at READ COMMITTED; excluding a dirty-read cycle is not serializability. A read-skew guarantee for consistent SELECTs is not a guarantee for mixed current DML and snapshot reads.
What the P4 row is telling you
PostgreSQL REPEATABLE READ rejects the demonstrated post-snapshot changed target; the successful fresh retry is a separate asserted branch. InnoDB REPEATABLE READ permits the shown stale literal write. Neither result protects values read outside the transaction or nonparticipating writers. Single-row protocols offer alternative scoped repairs.
SERIALIZABLE protects a rule only when every relevant writer participates and each transaction preserves it in serial execution. InnoDB standalone autocommit reads do not retain the explicit-transaction protection. Conflicts can wait, fail, or succeed. Recovery must repeat fresh reads and decisions under a bound, or return controlled failure; no successful commit or external deduplication is automatic.
The full breakdown
- PostgreSQL catalog: every reference cell and its D/M/† support, with unexecuted variants stated.
- MySQL catalog: consistent/current scope, explicit SERIALIZABLE participation, and unexecuted variants.
- Hermitage: a separate suite. Neither project catalog claims complete execution coverage of it.