Database transactions, four letters that promise four things.

ACID transactions guarantee that a sequence of database operations is atomic, consistent, isolated, and durable. Jim Gray articulated the properties in 1981; SQL-92 (1992) standardised the four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Each level forbids progressively more concurrency anomalies — at progressively higher performance cost.

The original 1983 paper compressed transactions into one acronym. Each letter pulls weight; together they describe the contract every relational database aims to honour.

D · Durable — once committed, a transaction's effects survive any crash. fsync'd to disk before the commit returns. The price of this is the topic of the WAL guide.

Below: the SQL-92 isolation level matrix. Click a level to see which anomalies it forbids and which it permits. Click an anomaly to walk through a concurrent scenario where it surfaces.

The weakest level. Transactions can read other transactions' uncommitted writes. If T2 writes a row and rolls back, T1 might have already seen the value. This is dirty reading — and it's mostly a curiosity. Postgres doesn't even implement it — its lowest level is Read Committed.

MySQL, SQL Server, and Oracle do support Read Uncommitted. Use cases are extremely narrow: approximate monitoring queries where stale and incorrect both feel similar.

The default in most databases. Each statement sees only data that was committed before the statement began — so dirty reads are gone. But within a single transaction, two reads of the same row can return different values (non-repeatable read) and two reads of the same range can return different sets of rows (phantom).

For most OLTP workloads, this is fine. The application either doesn't notice — most reads are independent — or wraps logically-coupled reads in SELECT ... FOR UPDATE to lock them. The price of stronger isolation levels usually isn't worth it. The B-tree pages underneath are described in database indexing.

Most databases implement this with MVCC (Multi-Version Concurrency Control): when the transaction begins, it captures a snapshot timestamp; every read filters to rows visible at that timestamp. T2 can update rows freely — T1 keeps seeing the old versions through its snapshot.

SQL-92 Repeatable Read still allows phantoms (range-query weirdness). Postgres implements a stronger version that prevents phantoms too — its Repeatable Read is closer to MySQL InnoDB's. Read the docs of your database; the level names are not standardised in practice.

The strongest level. The database guarantees that the result of any concurrent execution is equivalent to some serial ordering of the transactions. No anomalies of any kind.

Two implementation strategies: strict 2PL (lock everything you touch, hold until commit — DB2, SQL Server) or SSI (serializable snapshot isolation — Postgres). SSI starts with snapshot isolation and detects dependency cycles at commit time, aborting transactions that would violate serialisability. Lower contention, occasional retries.

The cost of Serializable is real: more aborts, more retries, sometimes lower throughput. Use it when business correctness depends on it (financial transfers, inventory) and use Read Committed otherwise. The deeper trade-off (consistency vs availability) is the CAP theorem in miniature.

How does the database give every transaction a stable view while concurrent writes happen? Every row is stored as a list of versions, each tagged with the transaction ID (a kind of monotonic ID) that created it (xmin) and, when superseded, the one that retired it (xmax). A reader sees only versions visible at its snapshot timestamp.

The price is space — old versions hang around until no transaction needs them. Postgres calls cleanup VACUUM; MySQL calls it the undo log; SQL Server stores versions in tempdb. The shape mirrors language-runtime garbage collection. If you ever wondered why Postgres has a VACUUM process, this is why.

One: does the operation depend on a stable read? If two reads of the same data inside one transaction must agree (calculating a balance from a list of transactions), at least Repeatable Read. Two: could two concurrent commits both individually look fine, but together violate an invariant? Then Serializable.

Otherwise Read Committed — and write the application to expect that two reads of the same row may return different values. That last clause is the one that bites; most isolation bugs are written by developers who didn't expect it.

PostgreSQL

Default: READ COMMITTED. SERIALIZABLE uses Serializable Snapshot Isolation (Cahill 2008) — true serializability via abort-on-conflict. Snapshot isolation via REPEATABLE READ. The right level for most applications is the default; switch to SERIALIZABLE only when invariants demand it.

MySQL InnoDB

Default: REPEATABLE READ — but MySQL's REPEATABLE READ is closer to Snapshot Isolation than the SQL-92 standard, and it forbids phantom reads (where Postgres's REPEATABLE READ does not). Subtle compatibility footgun when porting between the two.

SQL Server

Default: READ COMMITTED with SNAPSHOT isolation available. Famous for the lock-based legacy semantics; modern config (READ_COMMITTED_SNAPSHOT=ON) makes it MVCC-based.

Oracle

Default: READ COMMITTED. Doesn't implement READ UNCOMMITTED at all (always shows committed values). SERIALIZABLE since Oracle 7 is true serializable.

SQLite

Default: SERIALIZABLE — really. Single-writer, MVCC for readers via WAL mode. The small-database simplicity tax.

CockroachDB / Spanner / FoundationDB

SERIALIZABLE by default — strict serializable in fact (linearizable across transactions). Pay the latency cost for global correctness.

The one-line rule. If you don't know your isolation level, you are probably running on the database's default, and you are probably fine. The bugs SI/RC permit but SERIALIZABLE forbids are rare in well-modelled schemas. When they bite, they bite hard — financial-correctness, inventory-counting, and uniqueness-invariant violations are the canonical examples.