PostgreSQLTransactions

Transactions

A transaction groups multiple statements into a single all-or-nothing unit of work: either every statement in it succeeds and its effects become permanent, or something goes wrong and every effect is undone as if none of it had ever run. There is no in-between state where only some of the statements took effect.
BEGIN, COMMIT, ROLLBACK

The classic motivating example is a bank transfer: moving money from one account to another is really two separate updates, and they must both happen or neither should.

SQL
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
If the second UPDATE failed for any reason after the first had already run — a constraint violation, a crash, a deliberate ROLLBACK — the whole transaction is undone, including the first update. Without wrapping both statements in a transaction, a failure between them would leave $100 permanently deducted from account 1 with nothing credited to account 2.

SQL
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something looks wrong here, back out entirely
ROLLBACK;

-- both accounts are exactly as they were before BEGIN
Autocommit by default
Note
PostgreSQL runs in autocommit mode by default: if you don't explicitly wrap statements in BEGIN / COMMIT, every individual statement is automatically treated as its own complete transaction — it commits immediately if it succeeds, or rolls itself back if it fails. Explicit BEGIN is only needed when you want two or more statements to succeed or fail together as one unit.
SAVEPOINT: partial rollback within a transaction
A SAVEPOINT marks a point inside a transaction that you can roll back to without discarding the entire transaction — useful when one step of a larger transaction might fail but the rest of the work already done should still be kept.

SQL
BEGIN;

INSERT INTO orders (customer_id, total) VALUES (42, 150.00);

SAVEPOINT before_discount;

UPDATE orders SET total = total * 0.9 WHERE customer_id = 42;
-- suppose applying the discount was actually invalid for this order
ROLLBACK TO SAVEPOINT before_discount;

-- the order insert is still intact; only the discount update was undone
COMMIT;
ROLLBACK TO SAVEPOINT undoes everything after that savepoint while keeping everything before it, and the transaction remains open — you can keep working, set another savepoint, or eventually COMMIT or fully ROLLBACK the whole thing.

  • BEGIN starts an explicit transaction; COMMIT makes its effects permanent; ROLLBACK undoes all of them.

  • PostgreSQL autocommits each statement by default — explicit BEGIN is for grouping multiple statements together.

  • SAVEPOINT lets you roll back part of a transaction without discarding the whole thing.

  • The bank-transfer example is the textbook case: two updates that must succeed or fail as one unit.