Transactions and ACID in Plain Words

From lesson 7-2, UPDATE accounts SET balance = 0; is dangerous because a missing WHERE clause makes every row match, and there is no undo once it has run.

Every account is zeroed immediately, with no error and no confirmation.

This lesson finally supplies the undo button. Risky statements run inside a transaction can be thrown away with ROLLBACK.

All or nothing

Move $30 from Ana to Ben and you must run TWO updates: subtract from Ana, add to Ben. Now imagine the server dies between them. Ana lost $30, Ben got nothing, and $30 vanished. Partial completion is worse than failure.

A transaction makes a group of statements all-or-nothing:

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Ana';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Ben';
COMMIT;

• BEGIN starts the transaction. Changes after it are provisional.
• COMMIT makes them all permanent, together.
• ROLLBACK instead throws them ALL away, as if nothing happened.

If the crash happens before COMMIT, the database recovers to the state before BEGIN. Money never half-moves.

A transfer that gets thrown away

The transfer runs inside a transaction that ends in ROLLBACK, so the final SELECT shows both balances untouched.

CREATE TABLE accounts (
  name TEXT,
  balance INTEGER
);

INSERT INTO accounts VALUES ('Ana', 100);
INSERT INTO accounts VALUES ('Ben', 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Ana';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Ben';
ROLLBACK;

SELECT * FROM accounts;

Output

Ana|100
Ben|50

Both UPDATE statements ran and both succeeded. Inside a transaction their effects are provisional, visible to this connection and nobody else, until the transaction ends. ROLLBACK discards them completely.

With COMMIT in place of ROLLBACK the final query would report 70 and 80, and there would be no going back. That is the whole mechanism: BEGIN opens a provisional window, and exactly one of COMMIT or ROLLBACK closes it.

ACID, translated

Databases promise transactions behave well under the acronym ACID:

• Atomic: all of the transaction happens, or none of it. The transfer never half-completes.
• Consistent: the rules always hold. Constraints (lesson 7-3) are never violated, even mid-crash.
• Isolated: concurrent transactions do not see each other's half-done work. Two people buying the last ticket cannot both get it.
• Durable: once COMMIT returns, the data survives a power cut. The database wrote it to disk in a recoverable way before saying yes.

This is the answer to lesson 1-2's promise: this is why thousands of simultaneous users can hammer one database and the data stays sane, and it is what a shared spreadsheet can never give you.

Committing the transfer

Both updates sit between BEGIN and COMMIT, which makes the pair of changes permanent together.

CREATE TABLE accounts (
  name TEXT,
  balance INTEGER
);

INSERT INTO accounts VALUES ('Ana', 100);
INSERT INTO accounts VALUES ('Ben', 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Ana';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Ben';
COMMIT;

SELECT * FROM accounts;

Output

Ana|70
Ben|80

SET balance = balance - 30 reads the current value and writes the adjusted one, which is safer than computing 70 in application code and writing a fixed number, since the arithmetic happens against whatever the balance actually is at that moment.

Each UPDATE still needs its own WHERE, as lesson 7-2 established. The transaction protects you from a half-finished transfer, not from a statement that was wrong in the first place.

The 30 leaves one account and arrives in the other, and the total of 150 is unchanged. Preserving that kind of invariant across several statements is exactly what transactions are for.

Abandoning a transaction in progress

When the app discovers halfway through a BEGIN ... COMMIT block that the transfer is invalid, because the account would go negative, it should send ROLLBACK.

ROLLBACK is the escape hatch. Every change made since BEGIN is discarded and the data returns to its pre-transaction state, including the UPDATE that already appeared to succeed.

The alternative is far worse than an error message. Committing half a transfer means money has left one account without arriving in the other, and that is precisely the corruption transactions exist to prevent.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE name = 'Ana';
-- app checks the new balance, finds it negative
ROLLBACK;

A crash, a dropped connection, or a power cut has the same effect as an explicit ROLLBACK. An uncommitted transaction is never applied, which is why the database can recover cleanly from a machine dying mid-statement.