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.