BEGIN and COMMIT

Objectives

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

• Start a transaction with BEGIN
• Make a transaction’s changes permanent with COMMIT
• Explain what happens to statements run without an explicit transaction

💡 Why this matters: BEGIN and COMMIT are the actual commands that turn the ACID guarantees from the previous lesson into something real: a withdrawal and a deposit, wrapped between them, either both take effect or neither does.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

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

INSERT INTO accounts (owner, balance) VALUES ('Erin Castillo', 500.00), ('Jordan Blake', 200.00);

CHECK (balance >= 0) (Module 5) is doing real work in this module, PostgreSQL will refuse any update that would push a balance negative, this becomes important two lessons from now.

BEGIN and COMMIT

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE owner = 'Erin Castillo';
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Jordan Blake';

SELECT owner, balance FROM accounts ORDER BY owner;

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

BEGIN starts a transaction, every statement afterward is part of it, not yet permanent. The two UPDATE statements together represent one transfer, 100 moving from Erin to Jordan, both already visible within this same session even though nothing has been made permanent yet.

COMMIT;

SELECT owner, balance FROM accounts ORDER BY owner;

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

COMMIT makes every change since BEGIN permanent, all at once. Both updates are now durably saved (Durability, from the previous lesson), the balances shown are identical before and after COMMIT here specifically because nothing went wrong, the next lesson covers what happens when a transaction is undone instead.

Statements Without an Explicit Transaction

Every single statement run outside an explicit BEGIN … COMMIT block, an ordinary UPDATE or INSERT on its own, is still technically a transaction, PostgreSQL automatically wraps it in an implicit one and commits it immediately if it succeeds. BEGIN and COMMIT become necessary specifically when multiple statements need to succeed or fail together as one unit, a single statement is already atomic on its own.

Try It

1. Write a transaction that transfers 50 from Jordan Blake to Erin Castillo, using BEGIN, two UPDATE statements, and COMMIT.
2. Query accounts before and after the COMMIT in question 1, and confirm the balances only become permanent after it.
3. Explain, in your own words, why a single UPDATE statement run on its own doesn’t need an explicit BEGIN/COMMIT to be safe.
4. Explain, in your own words, what “not yet permanent” means for changes made between BEGIN and COMMIT, given that the same session can already see them.

Recap

• BEGIN starts an explicit transaction, grouping every statement that follows.
• COMMIT makes every change since BEGIN permanent, all at once.
• A single statement run without an explicit transaction is automatically wrapped in an implicit one and committed right away if it succeeds.

Next lesson: ROLLBACK, undoing every change in a transaction instead of committing it.