Use BEGIN, COMMIT, ROLLBACK

You’ve just run an UPDATE that mistakenly halved every price in your products table. The client is on the phone, the data is wrong, and there’s no backup. If you haven’t wrapped that statement in a transaction, you’re already in a world of pain. PostgreSQL gives you the power to undo mistakes like this with BEGIN, COMMIT, and ROLLBACK — but only if you understand how to wield them. This lesson turns you from a passive query-runner into a confident controller of atomic operations, so you can write code that never corrupts data, no matter how chaotic the moment gets.

The problem this lesson solves

SQL statements are executed one at a time, and by default, each statement is committed automatically. That feels convenient — until it isn’t.

Imagine a banking app that transfers money: you must UPDATE the sender’s account (deduct $100) and UPDATE the receiver’s account (add $100). If the second UPDATE fails because of a typo, a constraint violation, or a network blip, the first one is already permanent. The result? Money evaporates, and your customer service team gets a very angry call.

Even simpler: you’re cleaning up a messy table with DELETE. You forget a WHERE clause, and suddenly every row is gone. In a non-transactional mindset, that data is lost forever. PostgreSQL’s transaction system — powered by BEGIN, COMMIT, and ROLLBACK — exists to prevent exactly these disasters.

This lesson solves the core problem of atomicity: ensuring that a group of SQL statements either all succeed together or all fail together. You’ll learn to wrap operations in transactions, control the point of no return, and recover gracefully from errors. This isn’t advanced theory — it’s a daily necessity for anyone who inserts, updates, or deletes data.

Core concept / mental model

Think of a transaction as a staging area between your keyboard and the permanent database. Before BEGIN, PostgreSQL is like a painter who applies each brushstroke directly to the canvas — no undo. After BEGIN, you’re painting on a glass sheet: you can change your mind, wipe the glass clean, and only commit when you’re happy with the whole picture.

Here are the three commands and their roles:

• BEGIN — starts a transaction block. All subsequent statements are executed invisibly to other sessions until you commit (note: read operations are visible to your own session, but not to others).
• COMMIT — makes all changes permanent. This is the “apply to canvas” moment.
• ROLLBACK — aborts the transaction and undoes every change made since BEGIN. The glass is wiped clean; the database looks as if nothing happened.

In PostgreSQL, every statement outside an explicit BEGIN runs in autocommit mode: each one is wrapped in an implicit transaction that commits immediately. When you write BEGIN, you pause that autocommit behavior and take manual control.

A transaction has three states you’ll encounter:

1. In progress — after BEGIN, before COMMIT or ROLLBACK.
2. Committed — changes are durable and visible to everyone.
3. Aborted — changes are discarded; you can COMMIT (which acts like ROLLBACK) or ROLLBACK to finish.

Pro tip: A transaction is atomic (all or nothing), consistent (moves between valid states), isolated (invisible to others until commit), and durable (survives crashes after commit) — the ACID properties you’ll thank later.

How it works step by step

Mastering transactions is about understanding the flow: begin → operate → decide. Let’s break it down.

1. Start with BEGIN; — This tells PostgreSQL, “I’m taking manual control of the next group of statements.” From now on, nothing is permanent until you say so.

2. Run your statements — Insert, update, delete, or even SELECT (though reads don’t need transactions for correctness, they can benefit from consistent snapshots). All changes are made in the transaction’s private workspace.

3. Check the result — After each statement, you can inspect ROW_COUNT or test conditions. If something looks wrong, you still have the option to back out.

4. Commit or roll back — If everything is correct, run COMMIT; to make changes permanent. If not, run ROLLBACK; to undo the entire block. There is no middle ground — a transaction either commits as a whole or rolls back as a whole.

A critical nuance: PostgreSQL aborts a transaction automatically if any statement raises an error (e.g., a syntax error, constraint violation, or division by zero). In that aborted state, you cannot run more queries until you issue ROLLBACK (or COMMIT, which acts like a rollback). If you try to SELECT or UPDATE after an error, you’ll get an error like current transaction is aborted, commands ignored until end of transaction block.

This step-by-step logic is what separates safe database code from fragile scripts. By wrapping multi-statement operations in a transaction, you protect the system from partial changes and make your intentions explicit.

Hands-on walkthrough

Let’s put this into practice. We’ll create a simple accounts table and simulate a fund transfer with full control.

First, set up a test table:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (name, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 500.00);

Now, execute a transfer of $200 from Alice to Bob inside a transaction:

BEGIN;

UPDATE accounts SET balance = balance - 200 WHERE id = 1; -- Alice
UPDATE accounts SET balance = balance + 200 WHERE id = 2; -- Bob

COMMIT;

Check the result:

SELECT * FROM accounts;

Expected output:

 id | name  | balance
----+-------+---------
  1 | Alice |  800.00
  2 | Bob   |  700.00

Both updates succeeded together. Now, let’s see rollback in action. Suppose Alice sends $300 to Bob, but we realize a mistake before committing:

BEGIN;

UPDATE accounts SET balance = balance - 300 WHERE id = 1;

-- Oops! We don't want this. Undo it.
ROLLBACK;

SELECT * FROM accounts WHERE id = 1;

The output will show balance still at 800.00 for Alice — the UPDATE was discarded.

Now, test what happens when an error occurs mid-transaction. Try to send $1,000,000 (which violates Alice’s non-negative balance):

BEGIN;

UPDATE accounts SET balance = balance - 1000000 WHERE id = 1;
-- Error: new row for relation "accounts" violates check constraint "accounts_balance_check"

-- The transaction is now aborted. You must roll back.
ROLLBACK;

Even if you had run a second UPDATE after the error, you wouldn’t be able to — PostgreSQL stops you. The ROLLBACK cleans the slate.

Here’s a Python example using psycopg2 to show how transactions work in application code:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
try:
    with conn:
        with conn.cursor() as cur:
            cur.execute("BEGIN")
            cur.execute("UPDATE accounts SET balance = balance - 200 WHERE id = 1")
            cur.execute("UPDATE accounts SET balance = balance + 200 WHERE id = 2")
            cur.execute("COMMIT")
except psycopg2.Error as e:
    conn.rollback()
    print("Transaction failed, rolled back:", e)

The with conn context manager automatically commits on success and rolls back on exception — a great pattern to avoid forgetting.

Compare options / when to choose what

You’ll see several ways to manage transactions in PostgreSQL. Here’s how to choose:

Variations and when to choose: - Savepoints are perfect for complex ETL jobs where a batch can fail after several successful inserts — you can roll back just the bad chunk. - Client-side context managers are ideal for production code; they make transactional behavior explicit and error-safe. - Explicit BEGIN in SQL scripts is the simplest for ad-hoc maintenance tasks, giving you full control during interactive troubleshooting.

Pro tip: If you’re inside a transaction and need to undo just one step, use SAVEPOINT. It gives you surgical control without throwing away the entire transaction.

Troubleshooting & edge cases

1. “Current transaction is aborted, commands ignored until end of transaction block” — This occurs after an error inside BEGIN. The fix: run ROLLBACK; (or COMMIT;) to reset. Never try to continue executing queries before rolling back.

2. Forgetting COMMIT — You run a few UPDATEs, close your client, and the transaction is automatically rolled back. To avoid surprises, always COMMIT as soon as you’re satisfied, or use a client-side context manager.

3. ROLLBACK after COMMIT does nothing — Once committed, the transaction is over. There’s no way to undo it retroactively (except point-in-time recovery, which is beyond this lesson). This is why you must verify before committing.

4. Locks holding longer than expected — When you BEGIN and modify rows, PostgreSQL acquires locks that aren’t released until COMMIT or ROLLBACK. If you forget to end the transaction, you’ll block other sessions. Keep transactions short.

5. Nested transactions — PostgreSQL doesn’t allow true nested BEGINs. If you run BEGIN inside a transaction, you’ll get a warning, and the inner BEGIN is ignored. Use savepoints instead.

What you learned & what's next

You’ve mastered the core trio that controls PostgreSQL transactions: BEGIN to open a safe workspace, COMMIT to make changes permanent, and ROLLBACK to undo everything. You now understand the atomic, all-or-nothing behavior that prevents partial writes and data corruption. You’ve seen how to apply this in raw SQL and in Python, and you know the pitfalls — aborted transactions, forgotten commits, and lock contention — that trip up even experienced developers.

The next lesson in this track builds on this foundation by exploring isolation levels — how transactions interact when run concurrently. You’ll learn how to prevent lost updates, dirty reads, and phantom rows, taking your transactional toolkit to the next level. For now, practice wrapping every multi-step write operation in a BEGIN…COMMIT block, and get comfortable with ROLLBACK as your safety net.

Final thought: In production, treat every transaction like a bomb disposal: plan each step, know your exit, and never rush the COMMIT.