beginnerintermediateadvanced

PostgreSQL · Transactions

Transactions — Core Competency

BEGIN/COMMIT/ROLLBACK, atomicity and where to draw a transaction boundary, autocommit, savepoints, the three isolation levels, and why duration itself is a production risk

All-or-nothingnothing durable until COMMIT actually runs
One statement = one transactionunless BEGIN says otherwise
Duration is a costlocks + a stalled vacuum horizon, for as long as it stays open
3 isolation levelsRead Committed, Repeatable Read, Serializable

1.BEGIN, COMMIT and ROLLBACK

BEGIN starts a transaction block, grouping every following statement into one all-or-nothing unit. COMMIT makes every change in that block permanent and visible to other transactions. ROLLBACK discards every change made since BEGIN, as if none of it ever happened.

Key features

BEGIN opens a transaction block; every statement after it is part of the same all-or-nothing unit until COMMIT or ROLLBACK
COMMIT makes every change since BEGIN permanent and visible to other sessions, in a single atomic step
ROLLBACK discards every change since BEGIN, restoring the state as if the block never ran
A transaction block that hits an unhandled error is left in an "aborted" state — every statement inside it is discarded, and only ROLLBACK (not COMMIT) can close it out
END is a synonym for COMMIT in PostgreSQL

BEGIN / COMMIT / ROLLBACK

BEGIN / COMMIT / ROLLBACK
Command	Effect
BEGIN	starts a transaction block
COMMIT	makes every change since BEGIN permanent, atomically
ROLLBACK	discards every change since BEGIN

BEGIN opens a draft; COMMIT or ROLLBACK closes it

BEGINCOMMITROLLBACK

No transaction

start

Transaction block open

Committed

end

Rolled back

end

Command

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;   -- both updates become permanent together, or...
-- ROLLBACK;   -- ...neither does

BEGIN opens an all-or-nothing block; COMMIT makes every change since BEGIN permanent at once; ROLLBACK discards every change since BEGIN, not just the last statement. Nothing is durable until COMMIT actually runs.

2.Atomicity and Transaction Boundaries

Atomicity means a transaction is all-or-nothing from the point of view of every other transaction — the intermediate states between its individual statements are never visible to anyone else, only the state before it started or the state after it fully commits. A "transaction boundary" is the deliberate choice of exactly which statements belong inside one BEGIN/COMMIT — drawn around whatever set of writes must succeed or fail together as a single unit.

Key features

Other transactions see either the pre-transaction state or the fully-committed post-transaction state — never anything in between
A transaction boundary is a design choice: which set of statements must succeed or fail together as one unit
The right boundary is usually "exactly the invariant that must hold" — e.g. debit one account and credit another, together
A transaction that is too wide holds locks longer than the actual invariant requires, increasing contention with no correctness benefit
A transaction that is too narrow can let two writes that should be atomic actually commit independently, breaking the invariant under a crash or concurrent access

Choosing a transaction boundary

Choosing a transaction boundary
Boundary choice	Risk
Too narrow (splits a real invariant across two transactions)	the invariant can be observed broken, or left broken by a crash between the two
Too wide (wraps unrelated work into one transaction)	locks held longer than necessary, more contention, harder to reason about
Just right (exactly the invariant, nothing more)	atomic where it matters, no wasted lock duration

Where should the transaction boundary go?

Too narrow — split invariant

Debit and credit in two separate transactions
A crash between them leaves money permanently missing
No transaction left to roll back and recover

Matches the invariant

Debit and credit in one BEGIN/COMMIT
Other transactions see either both changes or neither
Nothing unrelated bundled in to hold locks longer

Command

sql

-- the boundary is drawn around exactly the invariant: "money moved" must be atomic
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- an unrelated, slow report query does NOT belong inside this same transaction

Atomicity means other transactions see either the fully-committed result or nothing — never a partial state. Draw the transaction boundary around exactly the invariant that must hold together: too wide wastes lock duration, too narrow can leave the invariant genuinely broken across a crash.

3.Autocommit Behavior

Every statement in PostgreSQL runs inside a transaction, even if you never type BEGIN. Without an explicit BEGIN, each individual statement gets its own implicit BEGIN and COMMIT wrapped around just that one statement — so a single UPDATE with no transaction block is automatically all-or-nothing on its own, but two separate UPDATEs with no BEGIN between them are two separate, independently-committed transactions.

Key features

Every statement runs inside a transaction — autocommit means the default boundary is one statement, not that transactions are skipped
With no explicit BEGIN, each statement gets its own implicit COMMIT immediately after it succeeds
Two separate statements with no BEGIN between them are two independent transactions — no atomicity connects them
Explicit BEGIN suspends autocommit until the matching COMMIT or ROLLBACK
Most client libraries/ORMs run in autocommit mode by default unless told otherwise, which is a common source of accidentally-non-atomic multi-statement operations

Command

sql

-- autocommit: each statement is its own transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- committed already, the instant it succeeded

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- a SEPARATE transaction -- no atomicity connects it to the statement above

-- explicit BEGIN: both statements become one transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Autocommit means the default transaction boundary is one statement — not that statements skip transactions entirely. Two statements with no explicit BEGIN between them are two independent, separately-committed transactions with no atomicity connecting them.

4.Savepoints and Partial Rollback

A SAVEPOINT marks a named point inside a transaction that you can roll back to without discarding the whole transaction — ROLLBACK TO SAVEPOINT undoes everything since that point while keeping everything before it, and the transaction stays open, able to continue and eventually COMMIT.

Key features

SAVEPOINT name creates a named point inside the current transaction
ROLLBACK TO SAVEPOINT name undoes every change since that savepoint, but keeps changes from before it and leaves the transaction open
RELEASE SAVEPOINT name discards the savepoint without undoing anything — used once you no longer need the ability to roll back to it
A transaction can have multiple savepoints, including nested ones, each independently rollback-able
After a plain (unhandled) error inside a transaction, only rolling back to a savepoint taken before the error — or a full ROLLBACK — can recover the transaction; simply continuing is not possible

Savepoint commands

Savepoint commands
Command	Effect
SAVEPOINT name	marks a point in the current transaction
ROLLBACK TO SAVEPOINT name	undoes changes since that point; transaction stays open
RELEASE SAVEPOINT name	discards the savepoint marker, keeping all changes since it

Recovering from one failed statement without losing the whole transaction

Client

Transaction

1BEGIN
2INSERT (id=1)
3SAVEPOINT before_risky_insert
4INSERT (id=1) — duplicate key
5ROLLBACK TO SAVEPOINT before_risky_insert
6transaction usable again — first INSERT still queued
7COMMIT

Command

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
-- oops, wrong recipient
ROLLBACK TO SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Wally';
COMMIT;  -- Alice's debit and Wally's credit are both committed; Bob's never happened

SAVEPOINT marks a recoverable point inside a transaction; ROLLBACK TO SAVEPOINT undoes only what happened since that point, keeping earlier work and leaving the transaction open. Without a savepoint, any unhandled error inside a transaction aborts the whole thing — only a full ROLLBACK (discarding everything) can recover.

5.Transaction Isolation Levels

PostgreSQL offers Read Committed (the default — each statement sees a fresh snapshot of committed data), Repeatable Read (the whole transaction sees one snapshot taken at its start), and Serializable (the strongest — behaves as if transactions ran one at a time, detecting and rejecting any execution that could not have happened serially). Read Uncommitted is accepted as a name but behaves identically to Read Committed.

Key features

Read Committed is PostgreSQL's default — each individual statement sees a snapshot of data committed as of when that statement began
Repeatable Read takes one snapshot for the entire transaction, as of its first non-transaction-control statement — every SELECT in it sees the same data
Serializable is the strongest level — PostgreSQL guarantees the result is equivalent to some serial (one-at-a-time) execution of all concurrent serializable transactions
Read Uncommitted is accepted syntactically but PostgreSQL treats it identically to Read Committed — it never actually permits dirty reads
Repeatable Read and Serializable can both fail with a serialization error that requires the application to retry the transaction — this is expected, documented behavior, not a bug

The three distinct levels

The three distinct levels
Level	Snapshot taken	Can fail with a serialization error?
Read Committed (default)	fresh, per statement	no
Repeatable Read	once, at transaction start	yes
Serializable	once, at transaction start, plus conflict detection	yes

Command

sql

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or, in one line:
BEGIN ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

Read Committed (default): fresh snapshot per statement. Repeatable Read: one snapshot for the whole transaction. Serializable: same snapshot guarantee plus a guarantee the outcome is equivalent to some serial execution. Repeatable Read and Serializable can both require the application to retry on a serialization error — expected, not a bug.

6.How Transaction Duration Affects Locks, Vacuum, Bloat and Concurrency

A transaction holds its row/table locks for its entire duration, not just for the instant each statement runs — so a slow transaction blocks other writers for as long as it stays open. Separately, a long-running transaction holds back the oldest snapshot VACUUM must respect, meaning VACUUM cannot reclaim dead row versions that transaction could still theoretically need to see, letting dead tuples (and table bloat) accumulate for as long as the transaction stays open.

Key features

Locks are held for a transaction's full duration, from acquisition until COMMIT/ROLLBACK — not just while the acquiring statement runs
A long-running transaction holds back the oldest snapshot (visible via backend_xmin in pg_stat_activity) that VACUUM must respect
VACUUM cannot remove a dead row version if any open transaction's snapshot could still need to see it — regardless of whether that transaction ever touches the table in question
This means one idle-in-transaction session can stall cleanup across the entire database, not just the tables it used
The practical fix is the same in both cases: keep transactions short, and never leave one "idle in transaction" waiting on something outside the database

One long-open transaction, two independent costs

row/tablelocksoldest snapshotVACUUM must respect

Transaction stays open

idle, or genuinely slow

Locks held the whole time

blocks other writers

Vacuum horizon held back

dead tuples accumulate, table bloats

Command

sql

-- find long-running / idle-in-transaction sessions
SELECT pid, state, now() - xact_start AS duration, query
  FROM pg_stat_activity
 WHERE state IN ('active', 'idle in transaction')
   AND xact_start IS NOT NULL
 ORDER BY duration DESC;

A transaction holds its locks for its whole duration, not just per-statement — and a long-running or idle-in-transaction session holds back the oldest snapshot VACUUM must respect, stalling cleanup across the WHOLE database, not just tables it touched. Never leave a transaction open around a network call or other non-database work.

7.Why Long-Running Transactions Are Dangerous in Production

In production, a long-running transaction compounds every cost the previous concept described (held locks, blocked vacuum, growing bloat) with two additional production-specific risks: it makes an eventual rollback or crash more expensive to recover from, and — under sustained load — a pile-up of long transactions can push the whole database toward exhausting connections or, in extreme cases, transaction ID wraparound protection kicking in and refusing new writes entirely.

Key features

Under real concurrency, lock contention from a long transaction compounds — more sessions queue up behind the same held locks, not just one
Sustained long transactions can push table/index bloat high enough to noticeably slow every query against the affected tables, not just the ones the transaction used
Connection pools are typically sized assuming transactions are short — a pile-up of long-held connections can exhaust the pool, starving unrelated requests
In extreme, sustained cases, held-back vacuum can delay relfrozenxid advancement enough to approach PostgreSQL's transaction ID wraparound protection, which will eventually refuse new writes database-wide to protect data integrity
pg_stat_activity with age(backend_xmin) or age(backend_xid) is the standard way to find and terminate offending long-running sessions in production

Compounding production risks of a long transaction

Compounding production risks of a long transaction
Risk	Why it is worse under real load
Lock contention	more concurrent sessions queue behind the same held locks
Bloat	higher write throughput means more dead tuples accumulate behind the stalled vacuum horizon
Connection exhaustion	pools sized for short transactions run out of capacity
Wraparound protection	in extreme, sustained cases, the database can refuse new writes entirely

Command

sql

-- production triage: find and (if safe) terminate the worst offenders
SELECT pid, usename, state, now() - xact_start AS duration, query
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
 ORDER BY duration DESC
 LIMIT 10;

-- SELECT pg_terminate_backend(pid);  -- only once confirmed safe to kill

Long-running transactions in production compound every cost of transaction duration under real concurrency and write volume — lock contention, bloat, connection pool exhaustion, and in extreme sustained cases, transaction ID wraparound protection. Find offenders via pg_stat_activity's xact_start/backend_xmin age, and fix by keeping transactions short and never leaving one open around external work.

Spotted a mistake on this page? Tell us.

Read Sheet