Transactions: all or nothing

A transaction is a group of statements that either all take effect or none of them do. It is the single most important feature a database has, and it is three keywords.

The three keywords

BEGIN;
  UPDATE books SET copies = copies - 1 WHERE id = 1 AND copies > 0;
  INSERT INTO loans (book_id, member_id, borrowed_on, due_on)
  VALUES (1, 7, current_date, current_date + 21);
COMMIT;

BEGIN starts one. COMMIT makes everything in it permanent. ROLLBACK throws all of it away.

Between the two statements above there is a moment when the copy has been taken off the shelf and no loan records it. Inside a transaction, nobody else can see that moment, and if the server loses power between them, neither change survives.

Without the transaction, a crash in the middle leaves a book that is missing from the shelf and borrowed by nobody — and nothing in the system knows.

START TRANSACTION is the standard spelling; BEGIN is what everybody writes.

Autocommit

Every statement you run outside a transaction is already in one — its own. That is autocommit, it is on by default in psql and in every client library, and it means there is no such thing as a statement outside a transaction.

So this:

UPDATE books SET copies = 5 WHERE id = 1;

is exactly:

BEGIN; UPDATE books SET copies = 5 WHERE id = 1; COMMIT;

Which is why a single statement is always atomic. An UPDATE matching ten thousand rows either changes all of them or none — there is no halfway state, even if it fails on row 9,999.

`ROLLBACK`, and using it deliberately

BEGIN;
DELETE FROM loans WHERE returned_on < DATE '2020-01-01';
-- DELETE 37
ROLLBACK;

Nothing happened. The count told you what would have happened.

This is the safest way to work on production data, and it costs one word. Look at the number, then decide between COMMIT and ROLLBACK.

A failed statement poisons the transaction

BEGIN;
INSERT INTO books (title, author_id) VALUES ('First', 1);
INSERT INTO books (title, author_id) VALUES (NULL, 1);     -- fails
SELECT count(*) FROM books;

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

Once any statement errors, every subsequent statement in that transaction is refused until you end it. COMMIT at this point acts as a ROLLBACK.

This surprises people coming from other databases — Oracle and SQL Server let you continue after some errors. PostgreSQL does not, and the reasoning is that a transaction which has already failed cannot be trusted to produce a correct result.

The escape, when you genuinely want to continue, is a savepoint.

Savepoints

BEGIN;
  INSERT INTO books (title, author_id) VALUES ('Definitely fine', 1);

  SAVEPOINT maybe;
  INSERT INTO books (title, author_id) VALUES (NULL, 1);   -- fails
  ROLLBACK TO SAVEPOINT maybe;                              -- undo just that

  INSERT INTO books (title, author_id) VALUES ('Also fine', 1);
COMMIT;

Two books are inserted. The failure was rolled back to the savepoint and the transaction carried on.

Savepoints are how a client library implements "try this insert, ignore it if it is a duplicate" inside a larger transaction. They are not free — each one has a cost — so do not wrap every statement in one.

Keep transactions short

The rule that matters in production, and the reason is not obvious.

A long transaction holds its locks for its whole duration, so anybody who wants those rows waits.

And it holds back VACUUM for the entire database. PostgreSQL cannot reclaim any row version that your open transaction might still need to see — so one transaction left open for an hour prevents cleanup of rows changed by everybody for that hour. Tables bloat, queries slow down, and the cause is a session somebody forgot about.

So:

-- bad
BEGIN;
  SELECT * FROM books WHERE id = 1;
  -- … call a payment API, wait 4 seconds …
  UPDATE books SET copies = copies - 1 WHERE id = 1;
COMMIT;

Never do network I/O inside a transaction. Do the slow work outside, then open the transaction, do the database work, and commit. If the external call must be tied to the database change, that is the outbox pattern and it is beyond this course — but the rule is the same: the transaction is short.

Find the long ones:

SELECT pid, state, now() - xact_start AS open_for, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '1 minute'
ORDER BY xact_start;

idle in transaction in state means a client opened a transaction and went away. That is the thing to hunt.

SET idle_in_transaction_session_timeout = '30s';

kills them automatically, and is worth setting.

DDL is transactional

BEGIN;
  ALTER TABLE books ADD COLUMN subtitle text;
  UPDATE books SET subtitle = '';
ROLLBACK;

The column is gone. PostgreSQL can roll back schema changes, which many databases cannot — MySQL commits implicitly on DDL, so a half-applied migration stays half-applied.

This is why PostgreSQL migrations can be genuinely atomic, and it is a real practical advantage.

Transactions in application code

with conn:                      # commits on success, rolls back on exception
    with conn.cursor() as cur:
        cur.execute("UPDATE books SET copies = copies - 1 WHERE id = %s", (book_id,))
        cur.execute("INSERT INTO loans (book_id, member_id) VALUES (%s, %s)", (book_id, member_id))

Every library has this shape: a block that commits at the end and rolls back if an exception escapes.

The thing to get right is the boundary. One transaction per unit of business work — per loan, per order, per booking. Not per statement, which gives no atomicity. Not per request, which is too long if the request does slow things.

And do not catch an exception inside the block and carry on, because in PostgreSQL the transaction is already aborted and everything after it will fail anyway.

What a transaction does not give you

The honest limit, and it leads into the next lesson.

Atomicity and durability are absolute. Isolation is not — it has levels, and PostgreSQL's default is not the strictest.

BEGIN;
  SELECT copies FROM books WHERE id = 1;    -- 1
  -- somebody else sells the last copy and commits here
  UPDATE books SET copies = 0 WHERE id = 1;
COMMIT;

That transaction is atomic, consistent and durable — and it has just overwritten somebody else's change. A transaction alone does not prevent a lost update.

Two lessons from here, that exact scenario is run against a real server, and it does what you would hope it does not.

Check your work

The three keywords. BEGIN, COMMIT, ROLLBACK.

What autocommit means. Every statement is already in its own transaction.

Why a single statement is always atomic. Because of that.

The safest way to work on production data. BEGIN, look at the row count, then decide.

What happens after an error inside a transaction. Every later statement is refused, and COMMIT becomes ROLLBACK.

What a savepoint is for. Continuing after a failure you expected.

Two costs of a long transaction. It holds locks, and it holds back VACUUM for the whole database.

The rule about slow work. Never network I/O inside a transaction.

What idle in transaction means. A client opened one and went away.

What PostgreSQL can roll back that others cannot. Schema changes.

Where the transaction boundary belongs. One per unit of business work.

What a transaction does not prevent. A lost update — isolation has levels.

Practice

Run two statements in a transaction and COMMIT. Then the same and ROLLBACK.
Run a DELETE with no WHERE inside a transaction, read the count, and roll it back.
Cause an error inside a transaction, then run a SELECT. Read the message.
COMMIT that aborted transaction and check whether anything was saved.
Use a savepoint to survive an expected failure and commit the rest.
Open a transaction in one session and leave it. In another, run the pg_stat_activity query and find it.
With that transaction open, UPDATE the same row from the second session and watch it block.
Set idle_in_transaction_session_timeout to 10 seconds and leave a transaction open.
ALTER TABLE inside a transaction and roll it back. Confirm the column is gone.
Write the transaction boundary for "borrow a book" and list every statement that belongs inside it.
Put a pg_sleep(5) inside a transaction to simulate an API call, and run the long- transaction query from another session while it sleeps.
Reproduce the lost-update sequence by hand in two psql windows. Then read the next two lessons.

Official documentation

PostgreSQL — Transactions — The tutorial version, with savepoints.
PostgreSQL — BEGIN and COMMIT — The statements themselves.
PostgreSQL — SAVEPOINT — Including the cost note.
PostgreSQL — pg_stat_activity — Every column, including state and xact_start.
PostgreSQL — Routine vacuuming — Why a long transaction blocks cleanup everywhere.

Next: isolation levels, and what each one allows.