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.
Connections
- Part of
- Transactions
- Builds on this
- Transactional Outbox
- In real systems
- PostgreSQLMySQL
- Mentioned
- SerializabilityRead Committed
Recall
These cards are also in your review deck, scheduled with spaced repetition.