Menu

PostgreSQL Transactions: BEGIN, COMMIT, ROLLBACK

How PostgreSQL transactions work: BEGIN, COMMIT and ROLLBACK, savepoints, what an error does to an open transaction, and the isolation levels.

This page includes runnable editors. Edit, run, and see output instantly.

A transaction groups statements so that they all take effect or none do. Start it with BEGIN, make it permanent with COMMIT, or undo everything since BEGIN with ROLLBACK. Until you commit, other sessions do not see your changes.

Alice ends with 70 and Bob with 80. If the server crashed between the two updates, neither would survive: money cannot leave one account without reaching the other.

Undo changes with ROLLBACK

ROLLBACK throws away every change made since BEGIN:

The DELETE removed all three rows inside the transaction, and the rollback brought them back: the count is 3. In psql, wrap a risky manual UPDATE or DELETE the same way. Run it after BEGIN, check the result with a SELECT, then choose COMMIT or ROLLBACK.

Without BEGIN, PostgreSQL runs each statement in its own transaction and commits it when it finishes (autocommit). Drivers differ: Python's psycopg starts a transaction on the first statement and waits for conn.commit().

Roll back part of a transaction with SAVEPOINT

A savepoint marks a point inside a transaction. ROLLBACK TO SAVEPOINT name undoes only the work done after it, and the transaction continues:

The log holds "import users" and "import products". The orders step was undone without losing the first insert. RELEASE SAVEPOINT name forgets a savepoint you no longer need.

Fix "current transaction is aborted"

When a statement fails inside a transaction, PostgreSQL marks the whole transaction as failed and refuses every following statement until you end it:

BEGIN;
INSERT INTO accounts VALUES ('alice', 10);   -- duplicate key: error
UPDATE accounts SET balance = 0;
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

The fix is ROLLBACK. A COMMIT on an aborted transaction saves nothing; PostgreSQL answers ROLLBACK. To keep going after an expected error, set a savepoint before the risky statement and roll back to it. Drivers and ORMs that offer "nested transactions" work this way.

Roll back table changes too

CREATE TABLE, ALTER TABLE, DROP TABLE and most other DDL are transactional, so a migration that fails halfway leaves nothing behind:

to_regclass returns NULL: the table never existed for anyone else. A few commands refuse to run inside a transaction block, among them VACUUM, CREATE DATABASE and CREATE INDEX CONCURRENTLY.

Choose an isolation level

The isolation level decides which of other sessions' commits a transaction sees. Set it with BEGIN ISOLATION LEVEL ....

LevelWhat each statement seesWhen to use it
READ COMMITTED (default)Data committed before the statement beganMost applications
REPEATABLE READOne snapshot taken at the transaction's first queryReports and exports that must be consistent
SERIALIZABLELike REPEATABLE READ, and PostgreSQL cancels transactions whose result could differ from running them one at a timeBusiness rules that read and then write

READ UNCOMMITTED is accepted but behaves as READ COMMITTED: PostgreSQL never shows uncommitted data. Under REPEATABLE READ and SERIALIZABLE, expect errors with SQLSTATE 40001 ("could not serialize access") and retry the whole transaction when you get one.

Lock a row you are about to change

To stop another session from changing a row between your read and your write, lock it as you read with FOR UPDATE. Other writers wait until you commit:

BEGIN;
SELECT balance FROM accounts WHERE name = 'alice' FOR UPDATE;
UPDATE accounts SET balance = balance - 30 WHERE name = 'alice';
COMMIT;

Keep transactions short

An open transaction holds its row locks until it ends, and it stops VACUUM from removing old row versions. A session left "idle in transaction" by a crashed script can block other writers for hours. Set idle_in_transaction_session_timeout to end such sessions automatically.

Inside a transaction, now() returns the same time for every statement: the time the transaction started (see date functions). The official tutorial on transactions has more examples.

Frequently Asked Questions

Does PostgreSQL use autocommit?

Yes. A statement sent outside BEGIN runs in its own transaction and is committed when it finishes. Some drivers turn this around: Python's psycopg opens a transaction for you and waits for commit() unless you enable autocommit.

What is the default isolation level in PostgreSQL?

READ COMMITTED. Each statement sees the data committed before that statement began. Use BEGIN ISOLATION LEVEL REPEATABLE READ or SERIALIZABLE when a transaction must see one consistent snapshot.

Can I roll back CREATE TABLE or ALTER TABLE in PostgreSQL?

Yes. Almost all DDL in PostgreSQL is transactional, so a CREATE, ALTER or DROP inside a transaction disappears on ROLLBACK. A few commands, such as CREATE DATABASE, VACUUM and CREATE INDEX CONCURRENTLY, cannot run inside a transaction block at all.

What is the difference between COMMIT and END in PostgreSQL?

None. END is a PostgreSQL extension that does the same as COMMIT, and START TRANSACTION is the standard spelling of BEGIN.

Coddy programming languages illustration

Learn to code with Coddy

Get started