# BEGIN, COMMIT, ROLLBACK

> Group changes into transactions, commit or roll back, and observe an expected failure.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="46.1" caption="BEGIN, COMMIT, ROLLBACK"}
-- 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).

> [!IMPORTANT] Expected error
> This lesson deliberately inserts a duplicate primary key to demonstrate transaction failure. Run it with psql's default `ON_ERROR_STOP=off`, so the following `ROLLBACK` and cleanup execute. Other lessons can use `-v ON_ERROR_STOP=1`.

## Run the example {#run}

To run:

```text
$ 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 {#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] Tip
> `BEGIN; ... ROLLBACK;` can undo transactional table changes during a rehearsal, but it still takes locks and runs triggers. Sequence increments and external side effects are not rolled back. Use a disposable database for these exercises.

> [!NOTE] Try it
> The script drops `tx_demo` during cleanup. First recreate it with `CREATE TABLE tx_demo (id integer PRIMARY KEY, val text); INSERT INTO tx_demo VALUES (1, 'a');`. Then 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.

## Downloads {#downloads}

SQL file: [begin-commit-rollback.sql](/examples/source/begin-commit-rollback.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)