Transactions

Atomicity, Consistency, Isolation, Durability: the safety guarantees a transaction is supposed to provide.

The term is more marketing than definition: implementations differ widely, and 'isolation' in particular rarely means serializable by default.

If a transaction can't finish, every write it made is discarded; it's all or nothing.

It has nothing to do with concurrency. Its value is that after a fault the application can safely retry instead of guessing which writes landed.

Application-defined invariants (e.g. credits equal debits) hold before and after every transaction.

It's a property of the application, not the database: the database can't know your invariants, it can only give you atomicity and isolation to preserve them.

Committed data is not lost, even after a crash: written to non-volatile storage (with a write-ahead log) or replicated before acknowledging.

Perfect durability doesn't exist (disks fail, fsync lies, correlated outages); it's a matter of reducing risk, usually by combining disks and replication.

A transaction sees another transaction's writes before they've committed.

The other transaction may still abort, so you'd act on data that never officially existed. Prevented by read committed and above.

A transaction overwrites a value that another uncommitted transaction has written.

Interleaved writes across rows (the car sold to Bob but invoiced to Alice). Almost every database prevents it with row-level write locks held until commit.

Isolation level that prevents dirty reads (show the last committed value) and dirty writes (row write locks).

The default in PostgreSQL, Oracle and SQL Server. It still allows read skew, lost updates and write skew.

A transaction sees different parts of the database at different points in time, e.g. one account before a transfer and the other after.

Tolerable for a single page view, fatal for backups and long analytic queries, which would observe a state that never existed.

Each transaction reads from a consistent snapshot of the database as of when it started.

Readers never block writers and writers never block readers. Called 'repeatable read' in PostgreSQL and MySQL and 'serializable' in Oracle, though it isn't serializable.

Keep several committed versions of each row, tagged with created_by and deleted_by transaction IDs, so different transactions can see different points in time.

Visibility rules decide which version each reader sees, which is how snapshot isolation is implemented without read locks; garbage collection removes versions nobody can see.

Two transactions read-modify-write the same value concurrently, and one overwrites the other's change.

The classic counter-increment bug. Fix it with atomic updates, explicit locks (SELECT FOR UPDATE), compare-and-set, or a database that detects it and aborts.

Write a new value only if the current value still equals what you read; otherwise retry.

Avoids locks, but under contention most of the work is spent on retries. Unsafe if the 'compare' reads from an old snapshot.

Two transactions read the same objects, each decides based on what it read, then they update different objects, together breaking an invariant.

A generalization of lost update that snapshot isolation does not prevent (doctors on call, double-spending, unique usernames). Needs serializable isolation or explicit locks on the rows read.

A write in one transaction changes the result of a search query in another, typically by inserting a row that didn't exist when the search ran.

There's no row to lock when the check is 'no booking exists'; you need predicate or index-range locks, materialized conflicts, or SSI.

The strongest isolation level: the result is the same as if transactions had run one at a time, in some order.

Prevents every race condition above. Three ways to get it: actual serial execution, two-phase locking, and serializable snapshot isolation.

Run every transaction one at a time on a single thread, with data in memory and transactions submitted as stored procedures.

No concurrency control at all, but throughput is capped at one CPU core per partition, and cross-partition transactions are much slower.

Readers take shared locks and writers exclusive locks, all held until the transaction ends (grow, then shrink).

Pessimistic: readers block writers and writers block readers. Serializable, but with unstable latency, low throughput under contention, and frequent deadlocks.

Locks on all objects matching a search condition, including rows that don't exist yet; usually approximated by locking a range of an index.

What lets 2PL prevent phantoms such as double-booked meeting rooms.

Transactions each waiting for a lock the other holds, forming a cycle in the wait-for graph.

The database detects the cycle and aborts one transaction, which the application must retry. Under 2PL this happens often.

Snapshot isolation plus tracking of which transactions read data that a concurrent transaction wrote; on a dangerous pattern, abort at commit.

Optimistic: no blocking, readers and writers proceed freely. Performs well under low contention, but abort rates rise as contention grows.