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 ....
| Level | What each statement sees | When to use it |
|---|---|---|
READ COMMITTED (default) | Data committed before the statement began | Most applications |
REPEATABLE READ | One snapshot taken at the transaction's first query | Reports and exports that must be consistent |
SERIALIZABLE | Like REPEATABLE READ, and PostgreSQL cancels transactions whose result could differ from running them one at a time | Business 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.