Acid

Overview Serializable isolation guarantees that concurrent transactions produce a result equivalent to some serial order, eliminating every possible race condition. Weaker isolation levels like Read Committed or Snapshot trade that guarantee for higher throughput, deliberately allowing certain read/write anomalies that application code must tolerate or guard against. Comparison Diagram SerializableWeaker IsolationT1T2time (T1 fully precedes T2)No overlap in effect =equivalent to a serial runT1T2time (T1 and T2 overlap)Overlap window =dirty/non-repeatable reads,phantoms, or write skew possible Comparison Table Aspect Serializable Weaker Isolation Isolation guarantee Result equivalent to some serial execution of all transactions Allows specific interleavings; guarantee varies by level (Read Committed, Repeatable Read, Snapshot) Concurrency control Full conflict serializability via strict two-phase locking, serializable snapshot isolation (SSI), or predicate locks Row-level locks or MVCC snapshots that only block on narrower conflict sets (e.g. write-write) Anomalies prevented Dirty reads, non-repeatable reads, phantom reads, and write skew all eliminated Only a subset prevented; phantoms and write skew commonly remain possible Contention behavior Higher rate of lock waits, deadlocks, or serialization-failure aborts under concurrent access Readers rarely block writers (MVCC) or lower lock scope, so contention is reduced Throughput impact Lower throughput and higher latency as concurrency increases, especially with hot rows Higher throughput and better scalability under contention Application responsibility Application can assume correctness; only needs to retry on serialization-failure errors Application must reason about anomalies and add explicit checks (e.g. version columns, SELECT FOR UPDATE) Conflict detection timing Detected either at lock-acquisition time or at commit time (optimistic serializable schemes) Detected only for the narrower conflicts the level covers, often just at commit for MVCC writes Default in major databases Rarely the default; must be explicitly requested (e.g. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE) Default in most systems out of the box (PostgreSQL/Oracle default to Read Committed, MySQL InnoDB to Repeatable Read) Key Differences Serializable enforces true serial equivalence; weaker levels only rule out a defined subset of anomalies. Weaker isolation relies on MVCC snapshots or narrow locks, cutting contention compared to serializable’s broader locking or SSI conflict tracking. Under Serializable, correctness bugs shift into retry loops on abort; under weaker levels, they shift into missed application-level checks. Write skew is the classic anomaly Serializable closes that even Snapshot Isolation leaves open. Choosing a level is a runtime trade-off between guaranteed correctness and throughput, not a one-time schema decision. When to Use Each Serializable ...