46 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

Example

-- 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: with the normal durability settings, a successful COMMIT makes logged changes survive a crash; settings such as synchronous_commit = off change that guarantee.

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).

Run the example

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.

Downloads

SQL file: begin-commit-rollback.sql · All lesson files · Setup guide