BEGIN, COMMIT, ROLLBACK

The PostgreSQL 18 transaction tutorial documents implicit transactions: without an explicit transaction block, a successful individual statement commits its transaction. A client can issue BEGIN automatically; inspect the SQL sent by your driver before assuming autocommit. These Scenarios send one statement per request. Multiple statements in a single simple-query message can share an implicit transaction, as the protocol manual documents.

BEGIN keeps a transaction open for multiple statements. COMMIT makes its transactional changes committed; ROLLBACK discards them. Visibility to a later reader depends on that reader's isolation level and snapshot, not just on whether the writer has committed.

Autocommit and visibility, demonstrated

B uses plain SELECTs at the pinned server default, READ COMMITTED. Its next SELECT sees A's successful autocommit UPDATE, excludes A's later uncommitted UPDATE, then sees that UPDATE after COMMIT. A division-by-zero error outside a transaction block is followed by a successful new statement.

A

B

UPDATE balance = 150 (autocommit, committed at once)

SELECT→ 150 ← visible right away

UPDATE balance = 999 (uncommitted)

SELECT→ 150 ← still the old value

COMMIT

SELECT→ 999 ← now B sees it

No BEGIN: this successful UPDATE commits as its own transaction before the next SELECT.

A> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1

B> SELECT balance FROM accounts WHERE id = 1; -- B sees it immediately
 balance 
---------
     150 
(1 row)

Inside an explicit transaction, A's change is invisible to B…

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1

B> SELECT balance FROM accounts WHERE id = 1; -- still the old value
 balance 
---------
     150 
(1 row)

…until A commits.

A> COMMIT;
COMMIT

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     999 
(1 row)

A failed autocommit statement does not leave the next statement in a failed transaction.

A> SELECT 1 / 0;
ERROR:  22012: division by zero

A> SELECT 1 AS recovered;
 recovered 
-----------
         1 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

One error poisons the whole transaction

In an explicit transaction, the demonstrated division-by-zero error leaves the transaction failed. A subsequent ordinary SELECT returns SQLSTATE 25P02. This heading concerns that statement-error case, not every kind of connection or transaction failure.

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> SELECT 1 / 0; -- division_by_zero
ERROR:  22012: division by zero

The transaction is now aborted. Even a perfectly innocent statement is refused.

A> SELECT 1 AS innocent; -- in_failed_sql_transaction
ERROR:  25P02: current transaction is aborted, commands ignored until end of transaction block

This first transaction uses full ROLLBACK. The next transaction checks COMMIT after an error; savepoint recovery has its own Scenario.

A> ROLLBACK;
ROLLBACK

A> SELECT 1 AS innocent;
 innocent 
----------
        1 
(1 row)

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> INSERT INTO samples VALUES (1);
INSERT 0 1

A> SELECT 1 / 0;
ERROR:  22012: division by zero

A> COMMIT; -- a failed transaction ends by rolling back, even when COMMIT is sent
ROLLBACK

A> SELECT count(*)::int AS remaining FROM samples;
 remaining 
-----------
         0 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

Full ROLLBACK ends the failed transaction. A prior savepoint permits ROLLBACK TO SAVEPOINT after a recoverable statement error. The second transaction above executes COMMIT after an error and asserts that its earlier INSERT was rolled back. These recovery commands are exceptions to the rejection of ordinary statements. A closed connection requires a new connection; a serialization failure requires reconsidering the whole transaction in a fresh attempt, not just rerunning the failed statement.

ROLLBACK undoes DDL too

This migration inserts a row, builds a non-concurrent index, creates a table, then encounters an asserted uniqueness violation. No implicit commit occurs along the way: B still counts zero orders and cannot resolve the uncommitted table. After ROLLBACK, B asserts that the row, table, and index are absent.

A migration script wraps its work in a transaction, believing that makes it atomic: insert data, add an index, create a table — and if anything fails, roll back.

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> INSERT INTO orders VALUES (1, 'draft');
INSERT 0 1

B> SELECT count(*)::int AS visible FROM orders; -- uncommitted, invisible — so far so good
 visible 
---------
       0 
(1 row)

A> CREATE INDEX idx_orders_state ON orders (state); -- DDL — on MySQL this is where the implicit COMMIT would fire
CREATE INDEX

A> CREATE TABLE migration_log (id int PRIMARY KEY, note text);
CREATE TABLE

A> SELECT to_regclass('migration_log') IS NOT NULL AS table_exists,
          to_regclass('idx_orders_state') IS NOT NULL AS index_exists;
 table_exists | index_exists 
--------------+--------------
 t            | t            
(1 row)

B> SELECT count(*)::int AS visible FROM orders; -- still nothing committed — the DDL joined the transaction
 visible 
---------
       0 
(1 row)

B> SELECT count(*) FROM migration_log; -- undefined_table — uncommitted schema is invisible, not merely empty
ERROR:  42P01: relation "migration_log" does not exist

The script now hits an error and rolls back, trusting the transaction to clean up:

A> INSERT INTO orders VALUES (1, 'duplicate');
ERROR:  23505: duplicate key value violates unique constraint "orders_pkey"

A> ROLLBACK;
ROLLBACK

B> SELECT count(*)::int AS visible FROM orders; -- the row is gone
 visible 
---------
       0 
(1 row)

B> SELECT to_regclass('migration_log') IS NULL AS table_gone,
          to_regclass('idx_orders_state') IS NULL AS index_gone; -- so is the table, and so is the index
 table_gone | index_gone 
------------+------------
 t          | t          
(1 row)

This rollback removed the INSERT, the non-concurrent index, and the new table. It does not test all DDL or external migration effects.

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

This demonstrates the listed operations, not every schema command. The CREATE INDEX manual, Building Indexes Concurrently documents that a regular index build can run inside a transaction block, while CREATE INDEX CONCURRENTLY cannot. Other commands have their own restrictions; a migration must check each command's manual and its external effects before claiming all-or-nothing execution. The MySQL lesson demonstrates that non-concurrent CREATE INDEX commits the preceding INSERT on MySQL 8.4.11.

Further reading

• PostgreSQL 18: BEGIN, COMMIT, ROLLBACK
• SQLSTATE codes: 25P02 is in_failed_sql_transaction
• The same lesson on MySQL