Transactions: making a mistake reversible

All of it, or none of it

A fee payment is recorded as two writes: insert a row into payments, and reduce balance_due on the student’s row. If the first succeeds and the second fails, the student has paid and still owes. If the second succeeds and the first fails, the debt is gone and there is no record of the money. Neither half is acceptable alone.

BEGIN;
INSERT INTO payments (reference, student_id, amount, paid_on, method)
VALUES ('P-10500', 812, 4500, '2026-03-14', 'upi');
UPDATE students SET balance_due = balance_due - 4500 WHERE id = 812;
COMMIT;

Between BEGIN and COMMIT the two writes are one unit. If the connection drops after the INSERT, the database throws the INSERT away when it recovers. If the UPDATE fails a constraint, nothing from the INSERT survives. Other users see neither change until the COMMIT, and then they see both. That is what a transaction is: a boundary inside which partial results do not exist.

The undo button

The same mechanism is the safety net for the previous lesson:

BEGIN;
UPDATE orders SET status = 'pending' WHERE status = 'pendng';
-- UPDATE 40000   <- not the 3 you rehearsed
ROLLBACK;

ROLLBACK discards everything since BEGIN. The 40,000 rows are as they were. Nothing happened, as far as anybody else can tell. This is why the habit is to open a transaction before any write on a database that matters: the cost is one word, and it turns a catastrophe into a ROLLBACK.

Autocommit, and which mode you are in

When you do not write BEGIN, most tools wrap each statement in its own transaction and commit it immediately. That is autocommit, and it means a bare UPDATE cannot be rolled back, because it was committed before the prompt returned. psql is in autocommit by default. DBeaver can be switched to manual commit, where nothing is saved until you press the commit button, and shows a counter of uncommitted statements. Application libraries vary: Python’s psycopg opens a transaction implicitly and needs commit() called; some others autocommit. Before writing anything on a new tool, find out which mode it is in, because the two modes produce opposite mistakes: work that vanished because nobody committed, and work that cannot be undone because everything did.

The error that aborts the whole thing

In Postgres, an error inside a transaction puts the transaction into an aborted state. Every subsequent statement fails with current transaction is aborted, commands ignored until end of transaction block, even a harmless SELECT 1, until you ROLLBACK. People meet this in a script that continues after a failed statement and cannot understand why everything after it is failing too. The behaviour is deliberate: a transaction with one failed step is not in a state anyone can reason about, so Postgres refuses to let you commit it.

SAVEPOINT gives a partial undo. After SAVEPOINT before_risky;, a failure can be answered with ROLLBACK TO before_risky;, which discards only the work since the savepoint and lets the transaction continue. Batch loaders use it to skip a bad row and keep going.

What a transaction costs

While it is open, its locks are held. An UPDATE inside a transaction that then waits for a human to read the result and type COMMIT holds a lock on those rows for as long as the human takes, and every other writer that needs them waits. A transaction left open over lunch is a common cause of a queue of stuck requests, and the performance module’s lesson on waiting shows how to find one. Keep transactions short: rehearse outside the transaction, then open it, write, check the count, commit.

Schema changes inside a transaction

Postgres treats CREATE TABLE, ALTER TABLE and DROP TABLE as transactional. A migration that creates two tables and fails on the third leaves nothing behind, and can be tested with a ROLLBACK at the end. MySQL commits implicitly before and after every schema statement, so a failed migration there leaves whatever it got through, and has to be cleaned up by hand. Oracle and SQL Server sit between. It is one of the larger practical differences between engines and it decides how you write a migration.

The habit

Open a transaction before a write you have not done a hundred times. Check the row count. Commit or roll back. If a tool commits for you, know that it does before you press enter.