Transactions and ACID

Article.

Everyone can expand the acronym. The senior answer explains how the engine delivers each letter, because the mechanism is what tells you the limits: why a PostgreSQL rollback is instant, why an error does not roll anything back by itself, why “committed” can still be lost, and which invariants the database cannot enforce for you.

How does PostgreSQL roll back a 10 GB transaction almost instantly, and where does the cost go instead? Outputs below are from PostgreSQL 18.4 in Docker and psycopg 3.3.4.

A transaction in one example

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Either both updates become visible, durably, or neither does; no other session sees the money in flight; and the constraints on accounts hold at the end. Those four claims are the four letters, and each has a mechanism and a fine print.

A: atomicity

Commit and rollback are a status bit

Every row version carries the id of the transaction that created it (xmin) and the one that deleted it (xmax), see PostgreSQL architecture. Committing writes a commit record to the WAL and sets the transaction’s status to committed in pg_xact; rolling back just sets it to aborted. Nothing is undone: readers check the status and ignore rows from aborted transactions.

BEGIN; INSERT 1,000,000 rows        2,444 ms
ROLLBACK;                           0.234 ms
pg_xact_status(xid)                 aborted
pg_stat_user_tables.n_dead_tup      1,000,000
table size after rollback           135 MB

The rollback was free and the cost moved: a million dead rows and 135 MB of table that VACUUM must clean. In MySQL’s InnoDB the old values live in an undo log, so a large rollback replays the undo and can take as long as the work did.

An error does not roll back by itself

BEGIN;
UPDATE acc SET bal = bal + 50 WHERE id = 2;     -- fine
UPDATE acc SET bal = bal - 500 WHERE id = 1;    -- ERROR: violates check constraint
SELECT * FROM acc;
-- ERROR: current transaction is aborted, commands ignored until end of
--        transaction block
ROLLBACK;

In PostgreSQL a failed statement puts the whole transaction into an aborted state (SQLSTATE 25P02); every later statement fails until the client sends ROLLBACK, and a COMMIT is answered with ROLLBACK. The first update is discarded too. MySQL instead rolls back only the failing statement and lets the transaction continue, so the same code can commit half the work there.

Savepoints: partial rollback, real cost

SAVEPOINT lets a transaction survive an expected error: Django’s nested atomic(), SQLAlchemy’s begin_nested(). Each one starts a subtransaction with its own id. A backend caches 64 of them; past that, every snapshot has to consult pg_subtrans on disk, which caused GitLab’s 2021 throughput collapse. A loop that opens a savepoint per row in one long transaction is the pattern to avoid.

C: consistency

The database enforces what it can see: NOT NULL, CHECK, UNIQUE, primary and foreign keys, and exclusion constraints. Above, the CHECK (bal >= 0) stopped an overdraft. Most business rules are beyond that:

• A CHECK sees one row. “At least one doctor on call”, “a balance equal to the sum of its entries”, “no overlapping bookings” span rows. Enforce them with an exclusion constraint where one fits, a trigger, locks, or SERIALIZABLE (Transaction isolation levels).
• Constraints are checked per row by default, even inside one statement. Swapping two seats with a single UPDATE failed with duplicate key value violates unique constraint. Declared DEFERRABLE and set DEFERRED, the check moves to COMMIT and the swap works:

ALTER TABLE seat ADD CONSTRAINT seat_seat_key
    UNIQUE (seat) DEFERRABLE INITIALLY IMMEDIATE;
BEGIN;
SET CONSTRAINTS seat_seat_key DEFERRED;
UPDATE seat SET seat = 3 WHERE passenger = 'a';
UPDATE seat SET seat = 1 WHERE passenger = 'b';
UPDATE seat SET seat = 2 WHERE passenger = 'a';
COMMIT;                                     -- checked here: a=2, b=1

The C in ACID is not the C in CAP: ACID’s means “invariants hold after the transaction”; CAP’s means “every node returns the latest write” (CAP, ACID, BASE, PACELC).

I: isolation

Concurrent transactions do interleave. Isolation levels define which interleavings are allowed to be visible: PostgreSQL’s default, READ COMMITTED, gives each statement a fresh snapshot, so two reads in one transaction can disagree and a read-modify-write can lose an update.

Only SERIALIZABLE promises a result equal to some serial order. Levels, anomalies and what each database actually does are in Transaction isolation levels and Isolation anomalies across databases.

D: durability

What COMMIT waits for

COMMIT returns once the transaction’s WAL records are flushed to disk; the changed data pages are written later by the checkpointer, and after a crash the WAL is replayed (WAL, Checkpoints, and fsync). Several committing sessions share one flush (group commit), which is why throughput grows with concurrency.

It is a setting

pgbench write-only load, 8 clients, two runs each:

synchronous_commit = on     1,517 / 1,991 tps
synchronous_commit = off    5,310 / 5,892 tps

About three times faster, and the price is exact: after an OS crash or power loss, transactions acknowledged in the last 3 × wal_writer_delay (600 ms) are gone.

The database stays consistent, unlike fsync = off, which can corrupt it. It can be set per transaction: SET LOCAL synchronous_commit = off for a click log, on for payments.

Durable on which machine?

A committed transaction is durable on the primary. With asynchronous replication, a failover to a replica that had not received it loses it: the client was told “committed”, and the new primary never saw it.

Synchronous replication (synchronous_standby_names = 'ANY 1 (a, b)') closes the gap at the cost of a network round trip per commit. And all of it assumes the disk does not lie about flushes: consumer SSDs without power-loss protection can acknowledge a flush that is still in cache (pg_test_fsync measures it).

Transactions from Python

DB-API drivers open a transaction implicitly. In psycopg 3 with the default autocommit=False, one SELECT left the session like this:

after one SELECT:  state = idle in transaction
after commit():    state = idle

A read-only code path that never commits holds its locks until the connection is reused or closed: an ALTER TABLE on that table then waits, and every query behind the ALTER waits too. At REPEATABLE READ, or after any write, it also holds back VACUUM.

Use with conn.transaction(): or a session scope that always ends the transaction (Transactions, Savepoints, Isolation), and set idle_in_transaction_session_timeout, plus transaction_timeout (17) as a hard cap.

Where it breaks in production

Committing half the work after an error

Code catches a database exception inside a transaction, logs it, and carries on. In PostgreSQL every later statement fails with 25P02, so the bug is loud. Ported to MySQL, the failed statement alone is undone and the COMMIT succeeds with half the changes. Catch, then roll back the whole transaction, or use a savepoint deliberately.

A rollback that “fixed” a bad batch, then a slow table

A job inserts 50 million rows, fails, and rolls back instantly. The table is now mostly dead tuples, sequential scans read all of them until VACUUM finishes, and the space is not returned. Batch large loads into separate transactions.

Savepoint storms

An import wraps each row in a savepoint to skip bad rows, inside one transaction. Past 64 subtransactions, every other session’s snapshot slows down, and throughput across the database falls. Validate before inserting, or commit in batches.

Lost commits after failover

A primary dies, an async replica is promoted, and payments acknowledged in the last second are not in the database. The application never saw an error. Decide per system whether that is acceptable; if not, synchronous replication, or an idempotent retry from the source of truth.

The trade not taken

PostgreSQL’s choices are a status bit instead of an undo log, and a WAL-flushed commit instead of writing data pages at commit time. Undo makes rollback expensive and old versions cheap to discard; PostgreSQL’s way makes rollback free and leaves cleanup to VACUUM. Flushing only the WAL turns random page writes into one sequential append per commit.

And synchronous_commit exposes the remaining trade directly: latency against the last 600 ms of acknowledged work, chosen per transaction, not per server.

Related

• Transaction isolation levels - isolation levels
• Isolation anomalies across databases - anomalies per database
• WAL, Checkpoints, and fsync - WAL, checkpoints, crash recovery
• PostgreSQL architecture - MVCC and VACUUM
• Transactions, Savepoints, Isolation - transactions from SQLAlchemy
• CAP, ACID, BASE, PACELC - ACID vs BASE