Concept · Transactions & indexes
ACID
Atomicity, consistency, isolation and durability: what a transaction promises and who keeps each promise.
3 mintransactionatomicitydurability
A transaction groups several reads and writes into one unit. ACID names the four guarantees a relational database gives that unit.
| Letter | Promise | Kept by |
|---|---|---|
| Atomicity | All of the writes happen, or none do. A crash or ROLLBACK mid-way leaves no trace. | The database, by undoing partial work |
| Consistency | The transaction moves the data from one valid state to another. | Mostly you: constraints, foreign keys and correct application logic |
| Isolation | Concurrent transactions don't see each other's half-done work. | The database, to the degree set by the isolation level |
| Durability | Once COMMIT returns, the data survives a crash. | The database, by flushing a write-ahead log to disk before replying |
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both updates, or (on error / crash before this) neitherThe fine print
- "C" is the odd one out. The database can only enforce rules you've declared, such as
CHECK (balance >= 0),UNIQUEand foreign keys. A transaction that moves money to the wrong account is perfectly consistent as far as the database is concerned. Put invariants in constraints wherever you can. - "I" is usually weaker than it sounds. Most databases default to read committed, which allows anomalies like lost updates. Full isolation is
SERIALIZABLE, and you have to ask for it. - "D" is only as good as the disk and the configuration. Settings like
synchronous_commit = offtrade durability for speed. Replication adds a second question: durable on one machine, or on several?
Transactions in application code
- Keep them short. A transaction holds locks and keeps old row versions alive (see MVCC). Never wait for a user or call a slow external API in the middle of one.
- Retry on serialization failures and deadlocks. Under stricter isolation levels the database aborts one of two conflicting transactions on purpose, and the right response is to run the whole transaction again.
- ACID stops at the database boundary. If a transaction commits and then your message publish fails, nothing rolls back. Use the transactional outbox for that gap, and sagas for work that spans services.