Transactions, MVCC & Isolation

All-or-nothing changes, savepoints, what happens after an error, and how PostgreSQL keeps readers and writers apart.

A transaction groups statements so they succeed or fail together: a budget transfer must never subtract from one department without adding to the other. PostgreSQL makes every statement a transaction automatically; BEGIN and COMMIT group several.

Transactions in practice

BEGIN ... COMMIT moves 200,000 between budgets as one unit.

Inside the transaction the DELETE removed all 17 assignments, but ROLLBACK brought them all back.

After the CHECK violation the transaction is aborted: even the harmless SELECT is refused until ROLLBACK.

ROLLBACK TO SAVEPOINT undoes only the failed part; the title change before the savepoint is committed.

Meera's row was rewritten by the last transaction, so its xmin differs from the untouched rows: that is MVCC.

Isolation levels and locking

BEGIN; ... an error ... COMMIT; -- expecting the other statements to be kept

Handle the error and ROLLBACK (or use a SAVEPOINT around the risky part)

After an error PostgreSQL aborts the whole transaction; COMMIT then actually performs a ROLLBACK.

What does 'current transaction is aborted, commands ignored until end of transaction block' mean?

An earlier statement in the transaction failed. PostgreSQL refuses everything until ROLLBACK (or ROLLBACK TO a savepoint taken before the error).

What anomaly can READ COMMITTED allow that REPEATABLE READ prevents?

Non-repeatable reads: running the same SELECT twice in one transaction can return different data if another transaction committed in between. REPEATABLE READ uses one snapshot for the whole transaction.