Transaction Isolation

Concept of the skill

Transaction isolation is the I axis of ACID: the property and configuration choice that determines what concurrent transactions can observe and which concurrent histories are allowed to commit. It is not just a four-row standard table. The SQL standard names four levels - Read Uncommitted, Read Committed, Repeatable Read, and Serializable - but the practical field includes Snapshot Isolation, Read Committed Snapshot Isolation, MVCC snapshots, key-range/gap locks, predicate locks, SSI, optimistic conflict detection, and distributed timestamp ordering.

Use this skill when the user needs to choose, explain, or debug an isolation contract for a workload. The useful answer names the engine, version, settings, transaction classes, invariants, allowed anomalies, required locks or constraints, and retry behavior. "We use serializable" is not complete until it also says which implementation, what abort or conflict errors look like, and how application code retries the whole transaction safely.

This skill is distinct from broader ACID-primer work outside the active skill corpus, which explains ACID as a whole; cap-theorem-tradeoffs and replication-patterns, which reason about replicas, freshness, and partitions; query-optimization, which diagnoses plan and latency evidence; indexing-strategy, which designs retrieval structures; and entity-relationship-modeling, which owns durable schema and constraints. It composes with those skills when concurrency correctness depends on constraints, index-backed range locks, replica-read routing, or lock-contention evidence, but it owns the isolation decision itself.

Coverage

Covers:

• The four SQL-standard isolation labels and the practical snapshot/read-versioning families beside them, including SI, RCSI, and vendor-specific snapshot modes.
• The anomaly catalog: dirty write, dirty read, non-repeatable/fuzzy read, phantom read, read skew/fractured read, lost update, write skew, read-only transaction anomaly, predicate/range anomaly, and serialization anomaly.
• The Berenson et al. critique and Adya graph-based framing that explain why implementation-independent anomaly analysis is stronger than trusting the standard's labels.
• Implementation families: strict two-phase locking, key-range locks, InnoDB gap and next-key locks, MVCC, statement snapshots, transaction snapshots, SSI predicate/SIREAD locks, optimistic write-write conflict detection, SQL Server RCSI/SNAPSHOT row versioning, Oracle transaction-level read consistency, materialized guard rows, and distributed-SQL serializable defaults.
• Engine adapters for PostgreSQL, MySQL/InnoDB, SQL Server/Azure SQL, Oracle, CockroachDB, and Spanner.
• Decision procedure: enumerate transaction invariants and anomaly vulnerabilities, choose the lowest engine-specific protection that prevents them, add targeted locks or database constraints where appropriate, and implement retry/idempotency for conflict-based isolation.

Does not cover replica freshness, CP/AP/PACELC choices, replication topology, query-plan tuning, durable index-portfolio design, schema modeling, or cross-service transaction orchestration.

Philosophy of the skill

Isolation is the part of ACID most often mis-defaulted because it looks like a dropdown and behaves like a proof obligation. The question is not "what level is safest?" or "what is fastest?" The question is "what histories would violate this workload's invariants, and what does this engine actually do with those histories?"

The discipline is per transaction class. A single-row atomic decrement can often be safe at Read Committed if expressed as one conditional UPDATE, while the same business operation can be unsafe if written as read-then-write application logic. A doctor-on-call invariant across rows is vulnerable to write skew under Snapshot Isolation. A cross-table report may only need a transaction snapshot. A uniqueness invariant should be a database constraint when possible, not an isolation-level hope. A work queue may need locking reads such as FOR UPDATE SKIP LOCKED, but that is a deliberately different concurrency contract from serializability.

Modern engines do not remove the need for this skill. PostgreSQL SSI, CockroachDB serializable defaults, Spanner external consistency, SQL Server optimized locking with RCSI, and MySQL next-key locking all solve some classes of anomalies better than old textbook levels, but they also move the burden to retries, version-store capacity, lock range shape, database options, and engine-specific behavior.

Anomaly Catalog

Read this table from the workload upward. Do not ask "which level sounds reasonable?" Ask which anomaly would create a real correctness bug for this transaction class.

Engine Semantics Matrix

Use this matrix as a starting map, not as proof. Always read the version-specific documentation and check the actual database options. The same application code can move from lock-based Read Committed to statement-snapshot Read Committed merely by enabling RCSI; that is a behavior change, not a harmless performance toggle.

Engine Notes

PostgreSQL. Read Uncommitted behaves like Read Committed. Repeatable Read sees a transaction snapshot and prevents phantom reads in PostgreSQL, but serialization anomalies remain possible and updating a row changed after the transaction snapshot can raise SQLSTATE 40001. Serializable uses SSI with SIREAD/predicate locks that do not block writers but can abort transactions with 40001; retry the complete transaction. PostgreSQL 17 added configurable SLRU cache sizing including serializable_buffers; PostgreSQL 19 is a development/beta line and its pg_stat_lock / max_locks_per_transaction notes are operational visibility/capacity notes, not semantic isolation changes.

MySQL/InnoDB. InnoDB offers the four SQL-standard levels and defaults to Repeatable Read. Plain consistent reads within a Repeatable Read transaction use the snapshot established by the first read. Locking reads, updates, and deletes can use record, gap, or next-key locks; range scans can block inserts into covered gaps. MySQL explicitly warns against mixing locking statements and non-locking SELECTs in one Repeatable Read transaction when the intended behavior is usually Serializable. Lock wait timeout is a contention/locking failure, not the same thing as a serialization anomaly; MySQL rolls back the current statement by default unless configured to roll back the whole transaction.

SQL Server / Azure SQL. Read Committed is lock-based unless the database has READ_COMMITTED_SNAPSHOT enabled, in which case READ COMMITTED reads use statement-level row versions. SNAPSHOT is separate, opt-in transaction-level row versioning. SNAPSHOT update conflicts, such as Msg 3960, are not RCSI read behavior. Serializable uses range locks. Optimized locking combines TID locking and Lock After Qualification (LAQ) to reduce lock memory and blocking, but it is platform/option dependent: Azure SQL has different defaults, SQL Server 2025 requires enabling it, ADR is required, and LAQ only applies under Read Committed with RCSI and without conflicting hints or unsupported plan shapes.

Oracle. Oracle Read Committed is statement-level read consistency: every query sees data committed before that query began. Serializable and Read Only are transaction-level read consistency: the transaction sees changes committed before the transaction began plus its own changes. Oracle Serializable can raise ORA-08177 when a read/write transaction tries to update or delete data changed after the transaction began; retry the intended operation or transaction. Long-running Oracle consistent reads can also hit ORA-01555 snapshot-too-old when needed undo has been overwritten; fix transaction duration or undo retention rather than treating it as a normal serialization conflict.

CockroachDB and Spanner. CockroachDB defaults to Serializable and can still require SQLSTATE 40001 client-side retries when automatic retry is impossible; Read Committed exists to reduce retry burden by using statement-level behavior. Spanner supports Serializable and Repeatable Read. Spanner Serializable is externally consistent: transactions behave as if sequential, with real-time ordering guarantees for clients. Spanner Repeatable Read is snapshot isolation, can admit write skew for application constraints not enforced by schema, and may be combined with SELECT ... FOR UPDATE/pessimistic concurrency to validate or lock the data actually read.

Isolation Choice Procedure

1. Name the engine, version, and settings. Capture current isolation and relevant database options: PostgreSQL SHOW transaction_isolation; SQL Server READ_COMMITTED_SNAPSHOT, ALLOW_SNAPSHOT_ISOLATION, optimized locking, and ADR; MySQL InnoDB isolation and lock timeout behavior; Oracle transaction mode and undo-retention risk; CockroachDB/Spanner isolation and concurrency mode.

1. Classify transaction classes. For each class, record whether it is read-only, single-row read/write, multi-row aggregate, predicate/range read, uniqueness check, work-queue claim, cross-table report, replica-read path, or external-side-effect transaction.

1. Name invariants and vulnerable anomalies. Examples: "balance never negative" may be protected by one conditional update; "at least one doctor remains on call" is write-skew vulnerable; "only one active subscription per account" wants a unique/partial unique constraint; "monthly close report must be internally consistent" wants a transaction snapshot or serializable read.

1. Choose the narrowest correct defense. Prefer database constraints for database invariants, single atomic SQL statements for row-local state transitions, row locks for known rows, range/predicate locks or serializable for set invariants, optimistic version columns for application-managed lost-update detection, advisory locks for logical critical sections that cannot be represented as data locks, and full serializable when the read set is broad or hard to lock precisely.

1. Verify engine-specific behavior. Check whether the chosen label actually prevents the anomaly on this engine. PostgreSQL RR is not MySQL RR is not SQL Server RR. SQL Server RCSI is statement-level, while SNAPSHOT is transaction-level. Oracle Serializable is not PostgreSQL SSI. Spanner Repeatable Read is SI-like and can admit write skew.

1. Design the conflict path before rollout. Higher isolation often fails by aborting a transaction rather than by silently committing a bad history. The application must retry the complete transaction, not just the last SQL statement, and must keep external side effects outside the retryable region or behind an outbox/idempotency key.

1. Measure the operational trade. Stronger isolation can add blocking, deadlocks, serialization failures, lock wait timeouts, snapshot-too-old errors, version-store pressure, predicate-lock memory, SLRU cache pressure, or wider next-key locks. If the symptom is latency under concurrency, verify with lock/wait evidence and co-load query-optimization only for the performance diagnosis.

Targeted Defenses

Explicit locks are tools, not magic. SELECT FOR UPDATE, FOR SHARE, SQL Server hints, advisory locks, and guard rows can be correct when they lock the same object or predicate every writer must pass through. They are harmful when they only lock a row that does not represent the invariant, or when they mask a schema constraint that should live in the database.

Materializing Conflicts

When Snapshot Isolation permits write skew because two transactions read the same predicate but update disjoint rows, one mitigation is to materialize the conflict into a concrete object every competing transaction must touch. A guard row, parent row, summary row, booking bucket, or invariant table can turn "at least one doctor must remain on call" or "total allocated capacity must not exceed limit" from an invisible predicate conflict into a row-level write-write or lock conflict the engine can detect under a weaker isolation mode.

A concrete shape is: store the invariant under a parent or summary row such as shift(id, on_call_version, on_call_count), and require every transaction that changes a doctor's on-call status to lock or update that shift row before changing the doctor row. The shared row forces the two transactions to contend on the same resource instead of writing disjoint rows based on stale snapshots.

Use this pattern only when the materialized object faithfully represents the invariant and every writer goes through it. If one code path can update the underlying rows without locking or updating the guard, the pattern is false comfort. Prefer a native unique, exclusion, or check constraint when the database can express the invariant directly; use a guard row when the conflict is real but otherwise too diffuse for row locks to catch.

Retry and Error Contract

Treat retryable isolation failures as part of the API contract:

• PostgreSQL Repeatable Read and Serializable can raise SQLSTATE 40001; PostgreSQL documentation says retry the complete transaction, including decision logic. It can also be appropriate, with care, to retry deadlocks (40P01) or unique/exclusion violations that are really application-level serialization conflicts.
• PostgreSQL SSI data read from a transaction that later aborts is not valid. A deferrable read-only serializable transaction is the special safe-snapshot case.
• On PostgreSQL 17+, serializable_buffers is a tunable SLRU cache size. Under SSI-heavy workloads with SLRU pressure, review it alongside abort rate and retry latency; it is an operational knob, not a substitute for correct isolation or retry design.
• CockroachDB uses SQLSTATE 40001 and restart transaction for retry errors when it cannot safely auto-retry. Serializable is default, but client-side retry handling remains required for multi-statement transactions that cannot be auto-retried.
• Oracle Serializable can raise ORA-08177 when a read/write transaction encounters data changed after the transaction began; retry the intended operation or transaction. Oracle ORA-01555 snapshot-too-old means the undo needed for a consistent read is no longer available; address transaction duration, undo retention, or rollback segment sizing rather than blindly retrying as if it were write contention.
• SQL Server SNAPSHOT uses transaction-level row versions and can terminate on update conflicts, including Msg 3960. RCSI is statement-level READ COMMITTED row versioning and, with optimized locking, has different writer behavior. Do not treat RCSI and SNAPSHOT as interchangeable.
• MySQL/InnoDB can block through record, gap, or next-key locks. Long range scans under RR or Serializable may lock more than the application expected. innodb_lock_wait_timeout produces ERROR 1205 when a row lock wait exceeds the configured timeout; by default only the current statement is rolled back, so decide whether to rollback/retry the whole transaction or handle the blocked operation explicitly.
• Deadlocks are not the same as lock wait timeouts. A deadlock victim is chosen to break a cycle; a timeout is a wait that exceeded policy. Both may be retryable, but their metrics and root-cause fixes differ.

Retry loops need bounded attempts, backoff/jitter under contention, full rollback before retry, and metrics. Never put non-idempotent external effects inside a retryable transaction body unless an outbox or idempotency key makes repeated attempts safe.

Retry Loop Pattern

Use a language-specific transaction helper if the driver or framework provides one. Otherwise the shape is:

for attempt in 1..max_attempts:
  begin transaction with chosen isolation
  try:
    read the current database state needed for the decision
    re-evaluate business rules from those fresh reads
    perform writes
    commit
    return success
  catch retryable isolation error:
    rollback the whole transaction
    sleep with bounded backoff and jitter
    continue

return retry_exhausted

Do not retry just the failed statement after a serialization failure unless the engine explicitly documents statement-level retry for that mode. The retry must re-run the reads and decisions that made the write correct. For lock wait timeouts and snapshot-too-old errors, first classify whether the failure is a transient contention victim, a policy timeout, or version-retention/transaction-duration exhaustion; the right fix may be shorter transactions, different lock order, retention tuning, or a narrower lock footprint, not just more attempts.

Replica and Staleness Boundary

Replica reads can look like an isolation bug because the application sees "old data," but the mechanism is different. A dirty read is an uncommitted read inside the transaction system; a stale read is a freshness or routing issue across copies. Read-only replicas and hot standbys can expose snapshot semantics at the replica's replay/apply position, and follower reads can deliberately trade freshness for locality. Keep a short note here when the user asks how a replica read interacts with a transaction snapshot, then route the topology, monotonic-read, GTID, failover, and stale-read policy work to replication-patterns.

Upstream Displacement Check

No current major database feature makes transaction-isolation analysis obsolete. The trend is the opposite: engines have become more capable and more engine-specific.

• PostgreSQL continues to expose SSI as the serializable implementation. PostgreSQL 17 added configurable SLRU cache sizing, including serializable_buffers, and PostgreSQL 19 beta adds lock-statistics visibility such as pg_stat_lock; these are operational notes, not replacements for anomaly analysis or retry design.
• SQL Server 2025/Azure SQL optimized locking plus RCSI can reduce blocking for default Read Committed workloads, but it is a database-option-dependent behavior change and stricter isolation levels still have different lock and conflict behavior.
• CockroachDB and Spanner make serializable-by-default or externally consistent transactions more accessible, but both still require the application to understand aborts, retries, transaction duration, and when weaker snapshot/repeatable-read modes are acceptable.
• Testing tools such as Hermitage and Jepsen Elle make isolation claims more verifiable; they are evidence tools, not replacements for choosing the right contract.

Verification

After applying this skill, verify:

• [ ] The database engine, version, default isolation level, and row-versioning/optimized-locking options are known from the actual deployment, not assumed from vendor defaults.
• [ ] Each transaction class has a named read set, write set, predicate/range set, and invariant.
• [ ] The anomalies that would violate those invariants are named explicitly.
• [ ] The proposed defense is engine-specific: isolation level, lock, constraint, optimistic token, advisory lock, guard row, or schema handoff.
• [ ] Snapshot/RCSI/SI terminology is precise: statement snapshot vs transaction snapshot vs full serializability are not conflated.
• [ ] Write skew under SI/RR is considered for cross-row, aggregate, and "at least one" invariants.
• [ ] If write skew is defended with a materialized conflict object, every writer is proven to lock or update the same guard row, summary row, parent row, booking bucket, or equivalent invariant object.
• [ ] Lost-update protection is proven by atomic SQL, lock, version token, or engine conflict detection, not assumed from a label.
• [ ] Retry handling covers the complete transaction and maps vendor errors such as SQLSTATE 40001, ORA-08177, MySQL lock wait timeout, Oracle snapshot-too-old, and SQL Server SNAPSHOT update conflicts.
• [ ] Lock wait timeout, deadlock, serialization failure, snapshot update conflict, and snapshot-too-old are distinguished before prescribing retry behavior.
• [ ] Non-idempotent external side effects are outside retryable transaction bodies or guarded by outbox/idempotency.
• [ ] Stale replica reads are not misdiagnosed as dirty reads; if the issue is replica freshness or read-after-write routing, replication-patterns owns the next step.
• [ ] Isolation changes are rolled out as behavior changes with contention, abort-rate, lock-wait, deadlock, snapshot-too-old, and version-store monitoring.
• [ ] On PostgreSQL 17+ SSI-heavy deployments, SLRU cache pressure and serializable_buffers sizing have been checked separately from application retry correctness.
• [ ] If correctness depends on documented behavior that has surprised teams before, reproduce it with a small two-session test or an isolation test harness such as Hermitage or Elle.

Do NOT Use When

Key Sources

• Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E., & O'Neil, P. (1995). "A Critique of ANSI SQL Isolation Levels". Primary source for the critique of ANSI SQL phenomena and the expanded anomaly table; provenance: used to preserve and extend the skill's anomaly vocabulary and the four-standard-level wording.
• Adya, A. (1999). "Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions". Primary source for implementation-independent, graph-based isolation definitions; provenance: used to avoid locking-only reasoning.
• Cahill, M. J., Rohm, U., & Fekete, A. D. (2008). "Serializable Isolation for Snapshot Databases". SSI paper; provenance: grounds PostgreSQL SSI and the "abort/retry is part of correctness" claim.
• Ports, D. R. K., & Grittner, K. (2012). "Serializable Snapshot Isolation in PostgreSQL". PostgreSQL SSI implementation report; provenance: grounds predicate-lock and safe-snapshot details.
• Kleppmann, M. (2017). Designing Data-Intensive Applications. Chapter 7. Practitioner source for write-skew examples and materializing conflicts with guard/conflict rows; provenance: grounds the named "Materializing Conflicts" mitigation pattern.
• PostgreSQL Global Development Group. "Transaction Isolation" and "Serialization Failure Handling". Vendor truth source for PostgreSQL RU=RC, RR/SI behavior, SSI predicate locks, SQLSTATE 40001, and whole-transaction retry.
• PostgreSQL Global Development Group. "PostgreSQL 17 Release Notes" and "PostgreSQL 19 Release Notes". Vendor truth sources for serializable_buffers, pg_stat_lock, and current beta/development operational notes; provenance: used only for operational guidance.
• MySQL Reference Manual 8.4. "InnoDB Transaction Isolation Levels", "InnoDB Locking", and innodb_lock_wait_timeout. Vendor truth source for InnoDB RR default, consistent reads, gap/next-key locks, RC semi-consistent reads, Serializable SELECT behavior, and lock wait timeout semantics.
• Microsoft Learn. "SET TRANSACTION ISOLATION LEVEL", "Transaction Locking and Row Versioning Guide", and "Optimized Locking". Vendor truth source for SQL Server locking vs row-versioning, RCSI vs SNAPSHOT, optimized locking, update conflicts, and database-option-dependent semantics.
• Oracle Help Center. "Data Concurrency and Consistency", "ORA-08177", and "ORA-01555". Vendor truth source for Oracle statement vs transaction read consistency, Serializable/Read Only behavior, retry guidance for ORA-08177, and snapshot-too-old causes/actions.
• Cockroach Labs. "Transactions", "Read Committed Transactions", and "Transaction Retry Error Reference". Vendor truth source for serializable-by-default behavior, RC tradeoffs, SQLSTATE 40001, and retry guidance.
• Google Cloud Spanner. "Isolation Levels" and "Transactions". Vendor truth source for Spanner serializable external consistency, repeatable-read/snapshot-isolation tradeoffs, write-skew risk, and SELECT FOR UPDATE mitigation.
• Hermitage. "Testing transaction isolation levels". Tooling/source catalog for concrete multi-database isolation tests; provenance: used to support the recommendation to verify surprising behavior with small histories.
• Jepsen Elle. "Black-box transactional safety checker", Jepsen "Consistency Models", and Jepsen "Snapshot Isolation". Tooling and model references for black-box anomaly checking and SI-vs-serializability boundaries.