Two changes that must happen together
Asha sends Ben 30. In the database that is two changes:
- Subtract 30 from Asha's balance.
- Add 30 to Ben's balance.
Each change on its own is fine. The danger is the moment between them. If anything stops the program there, the first change has happened and the second has not.
Move 30 from Asha to Ben. That takes two changes: subtract from one account, add to the other.
Thirty has disappeared. No error was raised, because as far as the database could tell, it was asked to do one valid update and it did it.
Crashes at exactly the wrong moment sound unlikely. On a system that runs millions of operations a day, "unlikely" happens every week.
A transaction: all or nothing
A transaction tells the database that a group of changes belongs together. Either every one of them takes effect, or none does.
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Asha';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Ben';
COMMIT;BEGINstarts the group.COMMITsays "that was everything, make it permanent".ROLLBACKsays "forget all of it". The database also does this by itself if the connection is lost before a commit.
Here is the same crash, at the same point, inside a transaction.
Move 30 from Asha to Ben. That takes two changes: subtract from one account, add to the other.
And when nothing goes wrong:
Move 30 from Asha to Ben. That takes two changes: subtract from one account, add to the other.
What a transaction promises: ACID
The guarantees have a four-letter name. Each letter answers a different "what if".
A for Atomicity: what if it stops halfway?
All of the changes happen, or none do. There is no halfway. "Atomic" here means "cannot be split".
This is what saved the transfer above.
C for Consistency: what if the result breaks a rule?
You can give the database rules: a balance may not go below zero, every order must belong to a customer that exists, an email must be unique. A transaction that would break a rule is refused and rolled back.
So the data moves from one valid state to another valid state, never to an invalid one.
Example
A rule stops a bad transfer
Asha has 100 and tries to send 500. With a rule that balances cannot be negative, the first update fails, the transaction rolls back, and Ben does not receive 500 that never existed.
I for Isolation: what if two run at the same time?
Many transactions run at once. Isolation means each one behaves as if it were alone. Another user never sees your half-finished work, such as the moment when Asha has been debited and Ben has not yet been credited.
Full isolation is expensive, so databases offer several strengths. See Transaction Isolation Levels. The locks used to provide it are also what cause deadlocks.
D for Durability: what if the power fails after the commit?
Once the database has answered "committed", the change survives a crash, a restart or a power cut. The database does not say "ok" until the change is safely on disk. See The Write-Ahead Log for how.
| Letter | Promise | Protects against |
|---|---|---|
| Atomicity | All or nothing | A crash or error in the middle |
| Consistency | Rules always hold | Data that breaks its own rules |
| Isolation | As if you were alone | Other transactions running at the same moment |
| Durability | Committed means saved | Crashes after the commit |
Try each case
Move 30 from Asha to Ben. That takes two changes: subtract from one account, add to the other.
Using transactions well
Group what belongs together
If a later step failing would make an earlier step wrong, they belong in one transaction.
Keep them short
A transaction holds locks while it is open. Others wait for those locks. Do the reading and thinking first, then open the transaction, write, and commit.
Never wait for the outside world inside one
Do not call another service, send an email or wait for a user while a transaction is open. If that call takes thirty seconds, your locks are held for thirty seconds.
Be ready to retry
The database may cancel a transaction to resolve a deadlock or a conflict. That is normal. Run it again.
Where the guarantee ends
A transaction works inside one database. When a single operation must change two separate databases or services, no ordinary transaction covers both. That needs different tools, such as two-phase commit.
Many NoSQL databases give weaker guarantees by default in exchange for scale, for example atomic changes to a single record only. See SQL or NoSQL.
Key takeaways
- A transaction groups several changes so that all of them happen, or none.
- BEGIN starts it, COMMIT makes it permanent, and ROLLBACK undoes it. A lost connection rolls back by itself.
- Atomicity covers failures halfway, Consistency keeps the rules, Isolation hides unfinished work, Durability keeps what was committed.
- Keep transactions short, and never wait on anything outside the database while one is open.
- A transaction protects one database. It cannot undo an email, a payment or a change in another system.