
The problem it solves
1. Subtract 100 from account A
the server crashes here
2. Add 100 to account BWithout a transaction, the money has left A and never arrived at B. With one, the database undoes the first step when it comes back, as if nothing had happened.
What it looks like
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;Everything between the first line and the last is one unit. If anything fails, a rollback puts the data back as it was.
The four promises
- All or nothing. No half-finished changes.
- The rules still hold afterwards. Constraints, such as a balance that cannot go below zero, are checked.
- Others do not see the middle. Another request never sees the money gone from A and not yet in B.
- Done means done. Once committed, the change survives a crash.
These four are what people mean by the letters ACID.
What a transaction does not cover
- Anything outside the database. An email sent or a card charged in the middle is not undone by a rollback.
- Two databases or two services. A transaction belongs to one database.
- Every timing problem. Depending on the settings, two transactions can still read the same value and both act on it. See race conditions.
Common mistakes
- Calling an outside service in the middle of a transaction. The transaction stays open while you wait, holding locks.
- Making transactions large. The more rows one touches, the more other requests wait.
- Catching the error and carrying on, so half the work is committed anyway.
- Assuming the framework opened one when it did not.
Questions you will hear in a meeting
- Are these two writes in one transaction?
- What happens if the second one fails?
- Are we calling anything slow while the transaction is open?