Postgres by Example: BEGIN, COMMIT, ROLLBACK

A transaction groups statements into an all-or-nothing unit. BEGIN (or START TRANSACTION) starts one; COMMIT makes the changes permanent; ROLLBACK discards them. Outside a transaction, each statement runs in its own implicit one — PostgreSQL is always transactional, you just choose whether to name the boundaries. Transactions are how you keep your data consistent through multi-step changes.

What you'll learn:

• Starting a transaction with BEGIN and finishing with COMMIT or ROLLBACK
• The ACID properties (especially Atomicity and Isolation)
• Implicit per-statement transactions
• Isolation levels: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE
• The ABORT / "transaction is aborted" state

-- A transaction that we throw away
BEGIN;
DROP TABLE IF EXISTS tx_demo;
CREATE TABLE tx_demo (id integer PRIMARY KEY, val text);
INSERT INTO tx_demo VALUES (1, 'a');
ROLLBACK;

-- The table does not exist after rollback
SELECT count(*) FROM pg_tables WHERE tablename = 'tx_demo';

-- A transaction that we keep
BEGIN;
CREATE TABLE tx_demo (id integer PRIMARY KEY, val text);
INSERT INTO tx_demo VALUES (1, 'a'), (2, 'b');
COMMIT;

SELECT * FROM tx_demo ORDER BY id;

-- Set isolation level for the next transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM tx_demo;       -- sees a consistent snapshot
COMMIT;

-- When a statement errors inside a transaction, the whole transaction is aborted.
-- Subsequent statements fail until you ROLLBACK.
BEGIN;
INSERT INTO tx_demo VALUES (1, 'duplicate');  -- PK violation
-- Any further statements here would fail with "current transaction is aborted".
ROLLBACK;

-- Cleanup
DROP TABLE tx_demo;

PostgreSQL provides full ACID guarantees:

• Atomic: either every change in the transaction takes effect or none does.
• Consistent: constraints are checked; the database moves from one valid state to another.
• Isolated: concurrent transactions do not see each other's uncommitted work (degrees of strictness vary by isolation level).
• Durable: once COMMIT returns, the changes survive a crash.

The default isolation is READ COMMITTED: each statement sees a snapshot of the database at the moment it starts. REPEATABLE READ gives the whole transaction a single snapshot — repeated reads see the same data. SERIALIZABLE adds checks for serialization anomalies and can roll back transactions that would produce a non-serial schedule.

PostgreSQL's transaction error model is strict: any error inside a transaction aborts the transaction. Subsequent statements fail with "current transaction is aborted, commands ignored until end of transaction block" until you ROLLBACK. To handle errors gracefully mid-transaction, use savepoints (next lesson).

To run:

$ psql -f source/begin-commit-rollback.sql postgres
BEGIN
DROP TABLE
CREATE TABLE
INSERT 0 1
ROLLBACK
 count
-------
     0
(1 row)
BEGIN
CREATE TABLE
INSERT 0 2
COMMIT
 id | val
----+-----
  1 | a
  2 | b
(2 rows)
 ...

Common pitfalls:

• Forgetting to COMMIT in interactive psql means everything you did is rolled back when you disconnect. Set \set AUTOCOMMIT off if you want explicit control; the default AUTOCOMMIT on runs each statement in its own implicit transaction.
• Long-running transactions hold row locks and prevent autovacuum from reclaiming dead tuples. Keep transactions short.
• Mixing schema changes (DDL) and data changes (DML) in one transaction is fine in PostgreSQL — unlike MySQL, DDL is transactional too.
• In READ COMMITTED, two statements in the same transaction can see different values of the same row (if a concurrent transaction committed between them). For consistent multi-statement reads, switch to REPEATABLE READ.

Tip: Wrap risky scripts in BEGIN; ... ROLLBACK; to dry-run them — every EXPLAIN, SELECT, and even UPDATE/DELETE runs, but nothing persists. Change to COMMIT; when you are sure.

Try it: Open two psql sessions. In session A: BEGIN; UPDATE tx_demo SET val = 'x' WHERE id = 1; (do not commit yet). In session B: SELECT * FROM tx_demo; — you should see the old value. Then session A COMMIT; and session B's next SELECT will see the new value.

Source: begin-commit-rollback.sql

Next: Savepoints

Home: Postgres by Example