PostgreSQL Transactions Guide with COMMIT and ROLLBACK

Two UPDATE statements move an amount from one row to another, and the second one fails a constraint. Without a transaction, the first update stands and the rows no longer add up. BEGIN opens a block, COMMIT makes everything inside it permanent, and ROLLBACK discards it. SAVEPOINT with ROLLBACK TO undoes part of the block while the rest stays open.

The examples run on PostgreSQL 18 against this table:

CREATE TABLE accounts (
  acctnum int PRIMARY KEY,
  balance numeric(10,2) NOT NULL
    CONSTRAINT ck_accounts_balance CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (7534, 500.00), (12345, 100.00);

The CONSTRAINT keyword names the check constraint explicitly[23], and that is the name PostgreSQL quotes when the constraint rejects a row.

Transaction command reference for BEGIN, COMMIT, ROLLBACK and SAVEPOINT

Seven commands control transactions in PostgreSQL. Each command name below links to its page in the PostgreSQL 18 manual.

The transfer described above is two updates inside one block:

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
COMMIT;

Put ROLLBACK where the COMMIT is and the two rows keep the balances 500.00 and 100.00 they started with. For the psql session around blocks like this one, the essential PostgreSQL commands reference lists the meta-commands for connecting, listing objects and inspecting metadata.

What a transaction is in PostgreSQL

A transaction bundles operations such as inserting, updating or deleting rows into a single step. PostgreSQL applies every operation in the bundle or none of them.

Without BEGIN, PostgreSQL executes transactions in autocommit mode, where each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement if execution was successful[1]. BEGIN is what turns several statements into one unit.

PostgreSQL has no nested transactions. Issuing BEGIN when already inside a transaction block provokes a warning message, and the state of the transaction is not affected[1]; the manual sends you to savepoints to nest work inside a block.

A statement that raises an error puts the whole block into an aborted state, and every statement after it fails with the same message until the block ends:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

ROLLBACK ends an aborted block and throws the work away. ROLLBACK TO SAVEPOINT is the only way to keep one alive, which is what step 5 below does.

The ACID guarantees behind a PostgreSQL transaction

Atomicity is the guarantee that makes the transfer above safe: the block is one unit, so a failure anywhere inside it leaves every row as it was. Consistency means the constraints and integrity rules hold across that unit, so the database moves from one valid state to another instead of stopping in the half-finished state between the two updates. Isolation decides how much of another session's uncommitted work your statements can see, and the isolation level sets it. Durability means a committed change survives a crash.

One documented exception is worth knowing before you rely on rollback for everything. Changes made to a sequence, and therefore to the counter of a column declared using serial, are immediately visible to all other transactions and are not rolled back if the transaction that made the changes aborts[8]. A gap in a serial column after a rollback is expected behavior rather than corruption.

Run a transaction in psql

The five steps below are the whole cycle, from the connection to a partial rollback. The DbSchema section further down runs the same commands from a GUI and is deliberately short, so nothing is said twice.

Step 1: Connect to the database

psql -U <username> -d <database_name>

If there is no database to connect to yet, the CREATE DATABASE guide walks through psql and DbSchema.

Step 2: Start the transaction with BEGIN

From BEGIN until COMMIT or ROLLBACK, everything you type belongs to one unit of work, and psql marks the open block with an asterisk in the prompt.

BEGIN;

The isolation level goes on the same line when you want to set it, instead of a separate SET TRANSACTION[7] afterwards:

BEGIN ISOLATION LEVEL REPEATABLE READ READ WRITE;

Step 3: Run the statements inside the block

Anything you run between BEGIN and the end of the block is part of the transaction: INSERT, UPDATE, DELETE, MERGE and SELECT alike. Your own session sees its uncommitted changes, and no other session does.

A debit larger than the balance takes account 7534 below zero, so the check constraint rejects it and the block is now aborted:

UPDATE accounts SET balance = balance - 900.00 WHERE acctnum = 7534;

ERROR:  new row for relation "accounts" violates check constraint "ck_accounts_balance"

Step 4: End the block with COMMIT or ROLLBACK

COMMIT commits the current transaction, and all changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs[2].

COMMIT;

ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded[3]. It is also how you leave the aborted block from step 3.

ROLLBACK;

Issuing ROLLBACK outside of a transaction block emits a warning and otherwise has no effect[3], so a stray ROLLBACK in a script does nothing rather than something destructive. Issuing COMMIT when not inside a transaction does no harm either, and provokes a warning of its own[2].

Step 5: Roll back part of a transaction with SAVEPOINT

SAVEPOINT establishes a named mark inside the open block[4]. ROLLBACK TO rolls back the commands executed after that mark and starts a new subtransaction at the same level[5], so the failed debit no longer costs you the credit that came before it:

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
SAVEPOINT before_debit;
UPDATE accounts SET balance = balance - 900.00 WHERE acctnum = 7534;
ROLLBACK TO SAVEPOINT before_debit;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

The credit before the mark and the smaller debit after it both survive the rollback:

RELEASE SAVEPOINT does the opposite job: it releases the named savepoint and merges the changes made since it into the transaction that was active when the savepoint was created[6]. Two rules the manual is explicit about. Savepoints can only be established when inside a transaction block[4], and ROLLBACK TO SAVEPOINT implicitly destroys all savepoints that were established after the named savepoint[5].

Stored procedures are the other place transaction control shows up in PostgreSQL. In procedures invoked by CALL, and in anonymous DO blocks, it is possible to end transactions using COMMIT and ROLLBACK[21]; a function called from a SELECT cannot.

Transaction isolation levels in PostgreSQL

The SQL standard defines four levels of transaction isolation, each one described by the phenomena it forbids[8]. In PostgreSQL you can request any of the four standard levels, but internally only three distinct isolation levels are implemented[8]: Read Uncommitted behaves like Read Committed.

Read Committed is the default. A query sees only data committed before the query began, so two SELECT statements in one block can return different rows when another session commits between them.

Repeatable Read moves that snapshot to the start of the transaction: a query sees a snapshot as of the start of the first non-transaction-control statement in the transaction, so successive SELECT commands within a single transaction see the same data[8]. The price is that applications using this level must be prepared to retry transactions due to serialization failures[8], which arrive from the server like this:

ERROR:  could not serialize access due to concurrent update

Serializable adds the guarantee that a set of successfully committed concurrent Serializable transactions has the same effect as running them one at a time[8]. It forbids all four phenomena, and like Repeatable Read it aborts a transaction that then has to be retried. Read Uncommitted is accepted for compatibility and behaves as Read Committed, so asking for it changes nothing.

Set the level per transaction on BEGIN, or for the session with the default_transaction_isolation configuration parameter[7].

What you cannot run inside a transaction block

Some PostgreSQL commands act outside the normal transaction machinery, usually because they touch the file system or run their own internal transactions. Putting one inside BEGIN gets you an error rather than a rollback, and this is the list migration scripts trip over most often. Each entry links to the manual page that states the restriction.

• CREATE DATABASE[9] and DROP DATABASE[10] cannot be executed inside a transaction block.
• CREATE TABLESPACE[11] cannot be executed inside a transaction block.
• VACUUM[12] cannot be executed inside a transaction block.
• ALTER SYSTEM[13] acts directly on the file system and cannot be rolled back, so it is not allowed inside a transaction block or function.
• CREATE INDEX CONCURRENTLY[14] cannot run in a block, although a regular CREATE INDEX can. The same split applies to DROP INDEX CONCURRENTLY[15] and to REINDEX CONCURRENTLY[16].
• REINDEX SCHEMA, REINDEX DATABASE and REINDEX SYSTEM[16] cannot be executed inside a transaction block, and neither can REINDEX on a partitioned index or a partitioned table.
• CLUSTER without a table name[17], which reclusters every previously clustered table, cannot be executed inside a transaction block. CLUSTER on a partitioned table cannot either.
• DISCARD ALL[18] cannot be executed inside a transaction block.
• CREATE SUBSCRIPTION[19] cannot be executed inside a transaction block when it creates a replication slot, which is the default.

Two commands people expect on that list belong somewhere else. The ADD VALUE form of ALTER TYPE runs inside a transaction block on PostgreSQL 18, and the narrower restriction is that the new value cannot be used until after the transaction has been committed[20]. TRUNCATE is transaction-safe with respect to the data in the tables, so the truncation is safely rolled back if the surrounding transaction does not commit[22].

The consequence lands in migration scripts. A deploy that creates an index concurrently has to run that statement outside the block, and a script that drops and rebuilds a schema needs to know which of its steps can be undone. The CREATE INDEX guide covers the CONCURRENTLY parameter, and the guide to dropping all tables shows how to rehearse DDL inside BEGIN and ROLLBACK before committing it.

What transactions cost

A transaction holds its row and table locks until it ends, so a block left open blocks other writers on the same rows. A session that has run a statement and not committed sits in the state 'idle in transaction', where it keeps a snapshot open, and VACUUM cannot remove the dead row versions that snapshot can still see. At Repeatable Read and at Serializable there is a second cost: the application has to catch a serialization failure and run the whole transaction again. Wrapping a batch job in one giant block turns a partial failure into a total one, and rolling back a very large block is not free.

None of that is an argument against transactions. It is the argument for keeping a block short: open it, do the writes that belong together, close it.

Run a transaction in DbSchema

DbSchema is a visual PostgreSQL client and schema designer, and its SQL Editor runs the same BEGIN, COMMIT and ROLLBACK you type in psql. Open the DbSchema SQL Editor from the Editors menu, paste the transfer above, and run it against the connected database.

Two buttons replace the last line of the block. INSERT, UPDATE and DELETE, along with some DDL statements, require an explicit commit to become permanent, and the Commit and Rollback buttons in the DbSchema editor toolbar finalize or cancel the pending changes. Until you press one of them the connection sits in the same 'idle in transaction' state an open psql block produces, holding your work and its locks. The Run Script button in DbSchema also offers Auto-Commit, which commits each statement as it runs, for a script you do not want to close by hand.

Everything in this section writes to the PostgreSQL database rather than to the DbSchema model file, because the DbSchema SQL Editor sends its statements straight to the connection.

Transactions in PostgreSQL come down to five commands and two decisions. The commands are BEGIN, COMMIT, ROLLBACK, SAVEPOINT and ROLLBACK TO. The decisions are which isolation level the work needs, and whether any statement in the block is one PostgreSQL refuses to run inside a transaction. To run these blocks against your own schema and watch the tables you are changing, download DbSchema at https://dbschema.com/download.html and connect to your PostgreSQL database: the SQL Editor with its Commit and Rollback buttons, the interactive diagrams and reverse engineering are all in the free Community Edition.

Sources

1. PostgreSQL 18 documentation: BEGIN
2. PostgreSQL 18 documentation: COMMIT
3. PostgreSQL 18 documentation: ROLLBACK
4. PostgreSQL 18 documentation: SAVEPOINT
5. PostgreSQL 18 documentation: ROLLBACK TO SAVEPOINT
6. PostgreSQL 18 documentation: RELEASE SAVEPOINT
7. PostgreSQL 18 documentation: SET TRANSACTION
8. PostgreSQL 18 documentation: 13.2. Transaction Isolation
9. PostgreSQL 18 documentation: CREATE DATABASE
10. PostgreSQL 18 documentation: DROP DATABASE
11. PostgreSQL 18 documentation: CREATE TABLESPACE
12. PostgreSQL 18 documentation: VACUUM
13. PostgreSQL 18 documentation: ALTER SYSTEM
14. PostgreSQL 18 documentation: CREATE INDEX
15. PostgreSQL 18 documentation: DROP INDEX
16. PostgreSQL 18 documentation: REINDEX
17. PostgreSQL 18 documentation: CLUSTER
18. PostgreSQL 18 documentation: DISCARD
19. PostgreSQL 18 documentation: CREATE SUBSCRIPTION
20. PostgreSQL 18 documentation: ALTER TYPE
21. PostgreSQL 18 documentation: 41.8. Transaction Management
22. PostgreSQL 18 documentation: TRUNCATE
23. PostgreSQL 18 documentation: Constraints