ROLLBACK

Objectives

By the end of this lesson, you should be able to:

• Undo every change in a transaction with ROLLBACK
• Explain what happens to a transaction after a statement inside it fails
• Recover from a failed statement using ROLLBACK

💡 Why this matters: Sometimes a transaction needs to be abandoned on purpose, or PostgreSQL abandons it automatically because a statement inside it failed. Either way, ROLLBACK is how everything since BEGIN gets undone.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the accounts table from the previous lesson (Erin Castillo at 400.00, Jordan Blake at 300.00, after that lesson’s committed transfer).

Voluntary ROLLBACK

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE owner = 'Erin Castillo';

SELECT owner, balance FROM accounts ORDER BY owner;

     owner       | balance
------------------+---------
 Erin Castillo   | 350.00
 Jordan Blake    | 300.00

ROLLBACK;

SELECT owner, balance FROM accounts ORDER BY owner;

     owner       | balance
------------------+---------
 Erin Castillo   | 400.00
 Jordan Blake    | 300.00

Erin’s balance shows 350.00 mid-transaction, then reverts to 400.00 after ROLLBACK, exactly as if the UPDATE had never happened. ROLLBACK discards every change made since BEGIN, this is Atomicity in action: an incomplete transaction leaves no trace.

An Error Forces a Transaction Into a Failed State

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE owner = 'Erin Castillo';

SAVEPOINT before_risky_update;

UPDATE accounts SET balance = balance - 9999 WHERE owner = 'Jordan Blake';

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"

Subtracting 9999 from Jordan’s balance would violate CHECK (balance >= 0) (Consistency, from Lesson 1), so PostgreSQL rejects this statement. But the transaction itself doesn’t just skip this one statement and move on:

SELECT owner, balance FROM accounts ORDER BY owner;

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

Once any statement inside a transaction fails, the entire transaction is marked aborted, every subsequent command is refused with this same error, even a harmless SELECT, until the transaction is explicitly ended. This is intentional: PostgreSQL won’t let the transaction silently continue past a point where something already went wrong.

ROLLBACK Recovers From an Aborted Transaction

ROLLBACK;

SELECT owner, balance FROM accounts ORDER BY owner;

     owner       | balance
------------------+---------
 Erin Castillo   | 400.00
 Jordan Blake    | 300.00

ROLLBACK ends the aborted transaction and discards everything, including Erin’s earlier, otherwise-successful UPDATE. This is the real cost of an all-or-nothing transaction: one failed statement near the end undoes everything that came before it too, unless a SAVEPOINT was used, covered in the next lesson, to protect the earlier work.

Try It

1. Start a transaction, update a balance, then run ROLLBACK voluntarily without any error occurring, and confirm the balance reverts.
2. Start a transaction, run one successful update, then run a second update that would violate the CHECK constraint, and observe the error.
3. Try running a SELECT after the failed statement in question 2, before running ROLLBACK, and read the resulting error.
4. Explain, in your own words, why PostgreSQL refuses every command after a failed statement instead of just ignoring that one bad statement and continuing.

Recap

• ROLLBACK undoes every change made since BEGIN, discarding the entire transaction.
• A failed statement inside a transaction marks the whole transaction as aborted, every subsequent command is refused until the transaction ends.
• ROLLBACK is required to recover from an aborted transaction, and it discards everything in that transaction, including any earlier successful statements.

Next lesson: SAVEPOINT, a checkpoint inside a transaction that protects earlier work from being lost.