Skip to content
Tutorials Center
My learning

Topics

Lesson 44 of 90 ยท Transactions, MVCC and Concurrency

Transactions and ACID

Learn how PostgreSQL transactions work: BEGIN, COMMIT, ROLLBACK, savepoints, transactional DDL, the aborted-transaction state and idle in transaction sessions.

A transaction is a group of statements that PostgreSQL treats as one unit of work: all of it happens, or none of it does. If you already know the general idea from the SQL transactions lesson, this lesson adds what is specific to PostgreSQL: it runs in autocommit mode until you say otherwise, it lets you change the schema inside a transaction, it refuses to continue after an error, and it punishes transactions that stay open too long.

Nearly every PostgreSQL feature you will meet later (locking, isolation levels, VACUUM) is built on transactions, so it pays to get the basics exactly right.

What runs live here. The examples below run in the in-browser SQLite engine, which supports BEGIN, COMMIT, ROLLBACK, savepoints and transactional DDL in the same way PostgreSQL does for these simple cases. Behavior that exists only in a real PostgreSQL server (aborted-transaction state, pg_stat_activity, timeouts) is shown as read-only blocks.

ACID in four words

Atomicity All statements apply, or none do.
Consistency Constraints hold after every commit.
Isolation Concurrent work does not step on each other.
Durability Committed data survives a crash.

PostgreSQL gets atomicity and isolation from MVCC, and durability from the write-ahead log.

1 / 2

Autocommit and explicit transactions

If you send a lone statement, PostgreSQL wraps it in its own transaction and commits it as soon as it succeeds. That is autocommit, and it is why a failed multi-row INSERT leaves no half-inserted rows.

To group several statements, start a transaction yourself:

SQL
BEGIN;                                        -- START TRANSACTION means the same
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;                                       -- or ROLLBACK;

END is a synonym of COMMIT, and ABORT is a synonym of ROLLBACK. You can set options on the first line, for example BEGIN ISOLATION LEVEL REPEATABLE READ; or BEGIN READ ONLY;. (Isolation levels get their own lesson: Isolation Levels and Anomalies.)

Application drivers hide this: most of them start a transaction when you call something like connection.begin() or DB::transaction(...), and commit when the block returns normally. Some drivers, notably Python's psycopg in its default mode, open a transaction implicitly on the first statement and keep it open until you call commit(). That is convenient, and it is also the usual source of the "idle in transaction" trouble described below.

SQL Commit and rollback

Press Run (or Ctrl + Enter) to see the result here.

The transfer is permanent (700 and 550). The mistaken UPDATE ... SET balance = 0 is visible inside its own transaction and then disappears. That "change, check, then decide" routine is a safe way to run risky data fixes by hand: write BEGIN;, run the change, look at the result, and only then type COMMIT;.

Savepoints: undo part of a transaction

A savepoint marks a position inside a transaction. You can roll back to it and keep everything before it:

SQL
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 10);

SAVEPOINT after_order;
INSERT INTO order_items (order_id, sku) VALUES (1, 'LAMP');   -- might fail
ROLLBACK TO SAVEPOINT after_order;                           -- undo only the item

RELEASE SAVEPOINT after_order;                               -- forget the savepoint (optional)
COMMIT;

ROLLBACK TO keeps the savepoint alive so you can try again, and it also rescues a transaction that is in the failed state (see below). RELEASE SAVEPOINT just discards the marker; it does not undo or commit anything.

SQL Keep the order, undo the item

Press Run (or Ctrl + Enter) to see the result here.

Only the lamp and the bulbs survive. Everything between SAVEPOINT and ROLLBACK TO vanished, but the work before and after it is part of the same, still-atomic transaction.

Under the hood, a savepoint starts a subtransaction. In PostgreSQL, an EXCEPTION block in PL/pgSQL does the same, and so do some ORMs that wrap each save in a savepoint. Subtransactions are cheap in small numbers, but a single transaction that opens many of them (more than 64 live ones is the point where PostgreSQL's per-session cache overflows) can slow down other sessions' reads. In loops over thousands of rows, create savepoints sparingly.

When a statement fails

This is the biggest difference from SQLite and MySQL. In PostgreSQL, any error inside a transaction block aborts the whole transaction:

SQL
BEGIN;
INSERT INTO accounts (id, owner, balance) VALUES (1, 'Amara', 100);
INSERT INTO accounts (id, owner, balance) VALUES (1, 'Duplicate', 5);
TEXT
ERROR:  duplicate key value violates unique constraint "accounts_pkey"
DETAIL:  Key (id)=(1) already exists.
SQL
SELECT 1;
TEXT
ERROR:  current transaction is aborted, commands ignored until end of transaction block

From that moment, every statement is refused until the transaction ends. Both ROLLBACK and COMMIT end it, and COMMIT on an aborted transaction does a rollback (and tells you so, with the command tag ROLLBACK). The first insert is gone as well, which is exactly what atomicity promises.

flowchart LR
  A["Idle<br/>(autocommit)"] -->|BEGIN| B["In transaction"]
  B -->|statement OK| B
  B -->|error| C["Aborted"]
  C -->|"ROLLBACK or COMMIT"| A
  B -->|COMMIT| A
  B -->|ROLLBACK| A
  C -->|"ROLLBACK TO savepoint"| B

A PostgreSQL session moves between these states; an error pushes an open transaction into "Aborted" until it is rolled back.

To recover from an error and keep going, wrap the risky statement in a savepoint and roll back to it. An example is a bulk import that should skip rows that violate a constraint:

SQL
BEGIN;
SAVEPOINT row_1;
INSERT INTO accounts (id, owner, balance) VALUES (1, 'Amara', 100);
RELEASE SAVEPOINT row_1;

SAVEPOINT row_2;
INSERT INTO accounts (id, owner, balance) VALUES (1, 'Duplicate', 5);   -- fails
ROLLBACK TO SAVEPOINT row_2;                                           -- transaction is usable again
COMMIT;

The psql client can do this for you in interactive sessions: after \set ON_ERROR_ROLLBACK interactive, it adds an invisible savepoint before every statement you type. For loads that can hit conflicts, a cleaner tool is INSERT ... ON CONFLICT DO NOTHING; see INSERT ON CONFLICT and RETURNING.

In application code, the rule stays simple: if anything fails, roll back the whole transaction and decide whether to retry it from the start.

DDL is transactional

PostgreSQL lets you create, alter and drop tables inside a transaction, and roll the changes back. That makes schema migrations safer: either every step applies, or none does.

SQL Roll back a schema change

Press Run (or Ctrl + Enter) to see the result here.

The first listing shows only price_history; after ROLLBACK, products is back with both rows and no price column, and price_history is gone. (In PostgreSQL you would query information_schema.tables or use \dt instead of sqlite_master.)

Not everything is rolled back, though:

  • Sequences. nextval() is never undone, so a rolled-back insert leaves a gap in your serial or identity values. That is by design: handing out numbers without waiting for other transactions is what keeps sequences fast. Never rely on IDs being gapless.
  • Some commands cannot run inside a transaction block at all: CREATE INDEX CONCURRENTLY, DROP DATABASE, VACUUM, and REINDEX CONCURRENTLY. PostgreSQL rejects them with an error if you try.
  • Side effects outside the database, such as an email you sent or an HTTP call you made, can't be rolled back. Do them after COMMIT.

Time inside a transaction

now() (and CURRENT_TIMESTAMP) returns the time the transaction started, and it does not change while the transaction runs. clock_timestamp() returns the real current time on every call:

SQL
BEGIN;
SELECT now() AS tx_start, clock_timestamp() AS real_time;
SELECT pg_sleep(2);
SELECT now() AS tx_start, clock_timestamp() AS real_time;
COMMIT;

The tx_start column shows the same value both times while real_time has moved on by two seconds. This is handy for audit columns (every row written by one transaction gets the same timestamp) and surprising for anyone measuring elapsed time with now().

Durability and synchronous_commit

When COMMIT returns, PostgreSQL has by default flushed the transaction's record to the write-ahead log (WAL) on disk. After a crash, replaying the WAL restores every committed transaction. The setting synchronous_commit = off (per session or per transaction) skips waiting for that flush. A crash can then lose the last fraction of a second of commits, but it will never corrupt the database. It is a legitimate trade for logging-style data you can afford to lose, and a poor choice for payments.

Idle in transaction: the silent problem

An open transaction costs the server something even when it does nothing. It keeps its snapshot alive, holds the locks it has taken, and prevents VACUUM from removing old row versions that the transaction might still need to see (MVCC and VACUUM explain why). A session that runs BEGIN, then waits for a human, an HTTP call or a crashed worker is shown as idle in transaction:

SQL
SELECT pid, usename, state, now() - xact_start AS open_for, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;
TEXT
  pid  | usename |        state        |    open_for     |          last_query
-------+---------+---------------------+-----------------+-------------------------------
 18211 | app     | idle in transaction | 02:14:09.442113 | UPDATE jobs SET status = 'run'

Two hours of an open transaction can block schema changes (anyone who needs a strong lock queues behind it) and make tables bloat. Defend yourself in three ways:

  • Keep transactions short. Gather input, call external services and render pages before BEGIN, not during it.
  • Set idle_in_transaction_session_timeout (for example '60s') for application roles, so the server ends abandoned sessions. PostgreSQL 17 added transaction_timeout to limit the total length of a transaction too.
  • Make sure your code always ends a transaction, using finally or the framework's closure-based API (with connection: in Python, DB::transaction() in Laravel).
SQL
ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL you run BEGIN, then a statement fails with a unique violation, then you run SELECT 1. What happens?

    PostgreSQL marks the whole transaction as aborted after an error. It answers "current transaction is aborted" until you end it with ROLLBACK or COMMIT, or ROLLBACK TO a savepoint.
  2. 2. Which of these can PostgreSQL roll back?

    DDL is transactional in PostgreSQL. Sequence values are never returned, CREATE INDEX CONCURRENTLY cannot run in a transaction block, and external side effects are outside the database.
  3. 3. What does ROLLBACK TO SAVEPOINT s do?

    It undoes later changes, leaves earlier ones in place, keeps the savepoint available and leaves the transaction open (and usable again, even if it was in the failed state).
  4. 4. Two calls to now() in the same transaction, one second apart, return...

    now() is fixed for the whole transaction. Use clock_timestamp() when you need the actual current time.
  5. 5. Why is a session in the state idle in transaction a problem?

    An open transaction keeps its locks and snapshot even when it is doing nothing. Short transactions and idle_in_transaction_session_timeout keep this under control.

Summary

  • PostgreSQL runs each statement in its own transaction unless you use BEGIN ... COMMIT or ROLLBACK.
  • An error inside a transaction block aborts the whole transaction; end it with ROLLBACK, or recover by rolling back to a savepoint.
  • Savepoints give you partial rollback, at the cost of a subtransaction each; avoid creating thousands.
  • Most DDL is transactional, which makes migrations safer; sequences, CREATE INDEX CONCURRENTLY, VACUUM and external side effects are the exceptions.
  • now() is the transaction start time; clock_timestamp() is the real time.
  • Durability comes from the WAL; synchronous_commit = off trades a little durability for speed.
  • Keep transactions short and set idle_in_transaction_session_timeout.

Next, learn how PostgreSQL makes all of this fast and non-blocking in MVCC: How PostgreSQL Keeps Versions.