Notestransactions🌱 seedling

ACID

Also: ACID Transactions · Atomicity Consistency Isolation Durability · Database Transaction

A transaction groups reads and writes into one unit that commits or aborts as a whole. ACID names its four guarantees: atomicity (all or nothing on faults), consistency (your invariants), isolation (concurrent transactions don't interfere) and durability (committed data survives crashes).

Why it exists

Without transactions, every partial failure and every concurrent interleaving is the application's problem. A transaction collapses them into two outcomes, committed or aborted, so the application can simply retry an aborted one.

How it works

  • Atomicity is about faults: if anything fails before commit, all of the transaction's writes are discarded. "Abortability" would be a better name.
  • Consistency is mostly an application property: the database enforces a few invariants (foreign keys, uniqueness, CHECK), your code keeps the rest. It's the odd letter out.
  • Isolation is about concurrency. In theory it means Serializability; in practice most databases default to a weaker Isolation Level.
  • Durability: commit is acknowledged only after the change is on stable storage, via a fsynced Write-Ahead Log on one node, or copies on several nodes.

Trade-offs

Gains Costs
simple error handling: abort and retry coordination: locks, versions, aborts
invariants hold across concurrent requests weaker defaults quietly allow anomalies
crash recovery the application never sees durable commits wait on fsync or replication

In practice

  • Postgres, MySQL, Oracle, SQL Server provide full ACID transactions, but default to Read Committed or similar, not serializable.
  • Multi-object vs single-object: many stores (and MongoDB before 4.0) offered atomicity only per document or key; multi-object transactions across machines need a Distributed Transaction.

Pitfalls

  • "ACID" on a datasheet says nothing about which isolation level you get by default.
  • A retry after a lost commit acknowledgement can run a transaction twice; make it idempotent.

Recall

These cards are also in your review deck, scheduled with spaced repetition.