BEGIN, COMMIT, ROLLBACK
Here's the part many working devs never learned: there is no such thing as "outside a transaction" in PostgreSQL. Every statement you've ever run was in one. Without BEGIN, PostgreSQL wraps each statement in its own tiny transaction and commits it the moment it finishes — that's autocommit (the manual: "each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it").
BEGIN (or START TRANSACTION) says: keep the transaction open, I have more statements coming. From then on, nothing is visible to anyone else until COMMIT, and everything can still be abandoned with ROLLBACK.
Autocommit and visibility, demonstrated
No BEGIN — the UPDATE is its own transaction, committed the instant it finishes.
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;
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)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
One error poisons the whole transaction
A surprise that bites every ORM user eventually: after any error inside a transaction, PostgreSQL refuses every further statement — even perfectly valid ones — with SQLSTATE 25P02 (in_failed_sql_transaction) until you ROLLBACK.
A> BEGIN;
BEGIN
A> SELECT 1 / 0; -- division_by_zero
ERROR: 22012: division by zeroThe 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 blockROLLBACK is the only way out. Afterwards, the session works normally again.
A> ROLLBACK;
ROLLBACK
A> SELECT 1 AS innocent;
innocent
----------
1
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
So autocommit isn't a mode you flip off server-side; it's the default that "commit after every statement" describes, and you opt out of it one transaction at a time with BEGIN. The moment you do, keep that transaction short: an open BEGIN that holds the connection through slow work leaves everyone else waiting on your locks, a theme the locking and MVCC chapters return to. And when current transaction is aborted, commands ignored until end of transaction block shows up in your logs, some earlier statement failed and your code kept going — the fix lives in your error handling, or in savepoints.
ROLLBACK undoes DDL too
A migration inserts a backfill row, adds an index, creates a bookkeeping table, and then trips over a constraint violation on step four. On MySQL you now own a half-migrated database: each schema change committed itself, and everything before it, the moment it ran. On PostgreSQL you type ROLLBACK and the migration never happened — schema included.
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;
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
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 existThe script now hits an error and rolls back, trusting the transaction to clean up:
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)PostgreSQL keeps schema changes in the same transaction as the data. A migration that inserts, indexes, and creates its way to an error leaves nothing behind — design for atomicity, not for re-runnability.
Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
Watch session B through the middle of that transcript. It counts zero rows after A's INSERT, which you'd expect, and it still counts zero after A's CREATE INDEX, which is the part worth sitting with: the DDL committed nothing on its way past. B's read of migration_log doesn't come back empty either. It fails with 42P01, because a table that exists only inside someone else's open transaction doesn't exist for you at all. Schema lives in catalog rows, and catalog rows obey MVCC like every other row.
Then ROLLBACK, and both relations are gone. to_regclass returns NULL for a relation that isn't there rather than raising an error, so B can point it at the table and the index alike and get an answer instead of a failure. That's the guarantee in full — wrap a migration in BEGIN, and either all of it lands or none of it does.
Run that same script on MySQL and the CREATE INDEX performs an implicit COMMIT first, so the closing ROLLBACK undoes nothing at all. The MySQL lesson proves it with the same beats and the opposite ending.
One caveat, and it's the reason this section says ordinary DDL rather than all DDL. A handful of statements refuse to run inside a transaction block. The manual puts it plainly for the one you're likeliest to meet: "a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot." VACUUM, CREATE DATABASE, and ALTER SYSTEM behave the same way, each rejecting an open transaction with 25001 (active_sql_transaction); the manual documents the restriction on every such statement's own page rather than in one central list. A migration that needs one of them runs it outside the wrapper, and gives up atomicity for that step.
Further reading
- PostgreSQL docs: BEGIN · COMMIT · ROLLBACK
- PostgreSQL docs: SQLSTATE codes (Appendix A) —
25P02isin_failed_sql_transaction - PostgreSQL docs: CREATE INDEX — regular builds run inside a transaction block,
CONCURRENTLYcannot - The same lesson on MySQL