ACID in Practice
Four properties, shown as the four different ways one bank transfer can go wrong.
Skip to the animationACID is four guarantees a transaction makes — atomicity, consistency, isolation and durability — each preventing a different way that concurrent, interruptible work could corrupt a database.
Four properties, four failures
| Property | Prevents | Delivered by |
|---|---|---|
| Atomicity | a half-finished transaction being visible or permanent | undo records in the log |
| Consistency | moving to a state that breaks a declared constraint | constraint checking, with the application |
| Isolation | concurrent transactions seeing each other's intermediate states | locking or MVCC |
| Durability | committed work being lost in a crash | redo records, forced to disk at commit |
Knowing which mechanism delivers which property is worth more than the definitions. Atomicity and durability are both the log; isolation is the lock manager; consistency is shared with your schema.
The vocabulary
- Functional dependency (X → Y)
- Knowing X pins down Y. If two rows agree on X they must agree on Y. This is a statement about the world being modelled, not about the rows that happen to be in the table today.
- Superkey and candidate key
- A superkey determines every attribute in the relation. A candidate key is a superkey with nothing spare in it — remove any attribute and it stops working.
- Prime attribute
- One that appears in some candidate key. The normal forms are mostly rules about how non-prime attributes are allowed to depend on prime ones.
- Partial dependency
- A non-prime attribute determined by only part of a composite key. Forbidden by 2NF, and the direct cause of the update, insert and delete anomalies.
- Transitive dependency
- Key → X → Y where X is not a key. Forbidden by 3NF: the fact really belongs in a table keyed by X.
- Lossless decomposition
- Splitting a relation so that re-joining it gives back exactly the original rows — no more, no fewer. Guaranteed when the shared attribute is a key of one of the two pieces.
Isolation is a dial
Full isolation is expensive, so SQL defines levels that trade correctness for concurrency. Each permits a specific named anomaly:
| Level | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible |
| READ COMMITTED | prevented | possible | possible |
| REPEATABLE READ | prevented | prevented | possible |
| SERIALIZABLE | prevented | prevented | prevented |
Only SERIALIZABLE gives the I in ACID in full, and most databases default to something weaker. If your application assumes full isolation and runs at READ COMMITTED, the bugs will be rare, timing-dependent and extremely hard to reproduce.
Durability is about ordering, not speed
A commit is acknowledged before the modified data pages are written to disk. That is safe because of write-ahead logging: the log record describing a change is forced to durable storage before the commit returns, and the log alone is enough to reconstruct the pages.
So durability costs one sequential log write, not several random page writes. Recovery then replays the log to reproduce committed work and undoes anything uncommitted.
Advantages and disadvantages
Advantages
- Lets application code assume a transaction runs alone, completely, on a machine that never crashes.
- The anomalies are named and classified, so the trade-offs can be reasoned about rather than guessed.
- Recovery is automatic — the log makes crash consistency the database's problem, not yours.
Disadvantages
- Full isolation costs concurrency, which is why weaker defaults exist and surprise people.
- Forcing the log at every commit puts a hard floor on transaction latency.
- Distributed ACID needs two-phase commit, and its cost is why many systems chose eventual consistency instead.
Watch it work
Check yourself
question 1 / 4
One question at a time. Pick an answer to see why it is right or wrong, then move on — there is no score to keep and nothing is saved.