All of It, or None of It: Transactions and the ROLLBACK That Saves You

Here is the problem no single statement can solve. Move $30 from Alice to Bob and it takes two UPDATEs: subtract from one account, add to the other. Now imagine the database dies, or hits an error, or the power fails, in the gap between them. Alice is down $30 and Bob never got it. The money evaporated. No amount of careful SQL in either statement prevents this, because the danger lives between them.

A transaction is the fix. It groups statements so they succeed or fail as one indivisible unit — all of them commit together, or none of them do. That property is atomicity, the “A” in ACID, and it is the single most important thing a database gives you. This chapter is BEGIN, COMMIT, and ROLLBACK, demonstrated in the scratch schema on two accounts that start with $100 each.

CREATE TABLE scratch.accounts (
    name    text PRIMARY KEY,
    balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO scratch.accounts VALUES ('alice', 100.00), ('bob', 100.00);

BEGIN, COMMIT, ROLLBACK

By default Postgres runs in autocommit: every statement is its own transaction, committed the instant it finishes. That’s why every INSERT so far has just stuck. BEGIN opens an explicit transaction that stays open across many statements until you end it. You end it two ways: COMMIT makes all the changes permanent, and ROLLBACK discards every one of them as if they never happened.

A committed transfer persists, exactly as you’d hope:

BEGIN;
UPDATE scratch.accounts SET balance = balance - 25 WHERE name = 'alice';
UPDATE scratch.accounts SET balance = balance + 25 WHERE name = 'bob';
COMMIT;

Open a brand-new session and the change is there — COMMIT made it durable:

SELECT * FROM scratch.accounts ORDER BY name;

 name  | balance
-------+---------
 alice |   75.00
 bob   |  125.00
(2 rows)

ROLLBACK: the undo button

ROLLBACK is where transactions earn their keep. Inside a transaction, your changes are real to you — you can see them — but nothing is permanent until COMMIT. Do a transfer, look at it mid-flight, then throw it away. (Balances are back to $100 each from a reset.)

BEGIN;
UPDATE scratch.accounts SET balance = balance - 30 WHERE name = 'alice';
UPDATE scratch.accounts SET balance = balance + 30 WHERE name = 'bob';
SELECT * FROM scratch.accounts ORDER BY name;

 name  | balance
-------+---------
 alice |   70.00
 bob   |  130.00
(2 rows)

Alice is at 70, Bob at 130 — the transfer is visible inside the open transaction. Now change our mind:

ROLLBACK;
SELECT * FROM scratch.accounts ORDER BY name;

 name  | balance
-------+---------
 alice |  100.00
 bob   |  100.00
(2 rows)

Both UPDATEs undone, together, in one word. This is the safety rail from the writing-data chapter, made real: wrap a risky UPDATE or DELETE in BEGIN, run it, SELECT to check the damage, and ROLLBACK if the row count or the result looks wrong. COMMIT only when you’re sure.

Atomicity: an error aborts the whole transaction

The ROLLBACK above was a choice. But atomicity also protects you when you don’t choose — when a statement fails partway through. Watch what happens when a transfer would overdraw Alice, and the CHECK (balance >= 0) constraint refuses it. First Bob gets credited, then Alice’s debit fails:

BEGIN;
UPDATE scratch.accounts SET balance = balance + 150 WHERE name = 'bob';

UPDATE 1

UPDATE scratch.accounts SET balance = balance - 150 WHERE name = 'alice';

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (alice, -50.00).

Bob’s $150 credit did run. But the transaction is now in a special poisoned state. Try to do anything else in it and Postgres refuses:

SELECT * FROM scratch.accounts ORDER BY name;

ERROR:  current transaction is aborted, commands ignored until end of transaction block

That message is the whole point. Once any statement errors, Postgres aborts the entire transaction and ignores every command until you close it. There is no limping along on the half that worked — you cannot commit a broken transaction. Your only move is ROLLBACK:

ROLLBACK;
SELECT * FROM scratch.accounts ORDER BY name;

 name  | balance
-------+---------
 alice |  100.00
 bob   |  100.00
(2 rows)

Back to $100 and $100. Bob’s $150 credit — the statement that succeeded — was rolled back along with the one that failed. That is atomicity doing exactly its job: because the debit couldn’t happen, the credit didn’t get to stick either. The accounts never spent a moment in an inconsistent state where the money didn’t add up. (This all-or-nothing on error is standard SQL, though a caveat: MySQL with the non-default MyISAM engine has no transactions at all and would have left Bob $150 richer. On Postgres, and on MySQL’s InnoDB, you’re safe.)

SAVEPOINT, in one line

Sometimes you want a partial undo within a transaction: a nested checkpoint you can roll back to without discarding everything before it. That’s a SAVEPOINT. SAVEPOINT s1; marks a spot, and ROLLBACK TO SAVEPOINT s1; rewinds to it — which also rescues a transaction from the aborted state. The work before the savepoint survives to be committed.

Final thoughts

That closes Series 1. Look back at the distance covered. You started with the relational model — five tables and the keys between them — and the idea that in SQL you describe the set you want rather than looping to collect it. From there: SELECT and its clauses, WHERE and the three-valued logic that NULL drags in, sorting and de-duplicating. Then the join family that reassembles data split across tables, aggregation that collapses many rows into summaries, and the subqueries and set operators that combine whole results. Then the writing side — INSERT/UPDATE/DELETE, the constraints that reject bad data at the door, and now transactions, the guarantee that a group of changes is all-or-nothing. That’s a complete working vocabulary. You can model a schema, load it, query it, change it safely, and trust that it stays consistent.

What you have now is correct SQL. The next series is about powerful SQL — the queries that make people ask how you did it in one statement. Querying Like You Mean It picks up three big tools. Window functions give you ranking, running totals, and per-group calculations without collapsing your rows. Common table expressions build a query in readable stages, and walk recursive structures like hierarchies. And Postgres’s first-class JSON handles the case where the neat rows-and-columns world meets the messy documents of the real one. Same bookshop, same run-it-and-paste-the-output rule, harder questions. Bring everything from this series; you’ll use all of it.