PostgreSQL Transactions, MVCC & Isolation - Complete Notes

An in-depth and interview-focused guide to atomic work, snapshots, row versions, isolation anomalies, safe retries, savepoints, and the concurrency behavior behind PostgreSQL transactions.

00. The transaction and snapshot mental model

A transaction defines one logical unit of work. MVCC decides which row versions that work can see. The isolation level decides when its snapshot is chosen and which concurrent outcomes PostgreSQL must reject.

Imagine a table as a history of row versions rather than a collection of boxes that are overwritten in place. When one transaction updates a row, PostgreSQL normally keeps the old physical version and creates a new physical version. Each statement reads through a snapshot that answers: "Which transactions count as completed for this view?" The visibility rules then choose the correct version of every logical row.

01. ACID and choosing the unit of work

ACID describes the guarantees expected around a transaction. It does not automatically make a multi-step business workflow correct. The application still has to choose the right boundary, constraints, isolation level, and retry behavior.

BEGIN;

UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 10
  AND balance >= 100.00;

-- The application must require exactly one updated source row.

UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 20;

INSERT INTO transfers (from_account, to_account, amount)
VALUES (10, 20, 100.00);

COMMIT;

Without one boundary, a crash after the debit but before the credit leaves incomplete work. Atomicity prevents that database state. It does not make an email, HTTP request, or message sent to an external system roll back. For cross-system work, patterns such as a transactional outbox are needed so the committed database state records what must be delivered.

02. BEGIN, COMMIT, ROLLBACK and autocommit

PostgreSQL always executes statements in transactions. An explicit transaction block groups several statements. Without one, a client normally uses autocommit, so each successful statement is committed as its own transaction.

-- BEGIN is PostgreSQL's common spelling.
BEGIN;
-- statements
COMMIT;

-- SQL-standard spelling with identical transaction-start behavior.
START TRANSACTION;
-- statements
ROLLBACK;

-- Set characteristics while starting the transaction.
BEGIN TRANSACTION
  ISOLATION LEVEL REPEATABLE READ
  READ ONLY;

-- Finish and immediately start another transaction with the same characteristics.
COMMIT AND CHAIN;
ROLLBACK AND CHAIN;

In many drivers, "autocommit off" means the driver opens a transaction implicitly and leaves it open until the application commits or rolls back. Check the driver and framework behavior. A forgotten transaction can retain snapshots, locks, and database resources even if the code never typed BEGIN.

What happens after a statement error

BEGIN;

INSERT INTO users (email) VALUES ('duplicate@example.com');
-- ERROR: duplicate key value violates unique constraint

SELECT 1;
-- ERROR: current transaction is aborted, commands ignored...

ROLLBACK;
-- The session can now start new work.

PostgreSQL does not silently skip a failed statement and continue the same top-level transaction. That would make it too easy to commit a partially understood state. Use a savepoint when a particular failure is expected and recoverable. Otherwise roll back the whole transaction.

03. Savepoints and partial rollback

A savepoint is a named recovery point inside one transaction. It is useful for an optional step or an expected error, but it is not an independently durable nested transaction.

BEGIN;

INSERT INTO orders (order_id, customer_id, status)
VALUES (501, 42, 'created');

SAVEPOINT optional_coupon;

UPDATE coupons
SET remaining_uses = remaining_uses - 1
WHERE code = 'WELCOME'
  AND remaining_uses > 0;

-- Suppose a later coupon operation fails.
ROLLBACK TO SAVEPOINT optional_coupon;

INSERT INTO audit_log (event_type, entity_id)
VALUES ('order_created_without_coupon', 501);

COMMIT;

• SAVEPOINT name records a point inside the current transaction. It requires an explicit transaction block.
• ROLLBACK TO SAVEPOINT name undoes work after the point and restores a usable transaction state. The named savepoint remains available.
• Rolling back to a savepoint destroys savepoints created after it. Earlier savepoints remain.
• RELEASE SAVEPOINT name removes that savepoint and later nested savepoints while keeping their successful effects in the enclosing transaction.
• The outer COMMIT is still required. A released savepoint has not made anything independently durable.

04. MVCC: multiple physical versions of one logical row

Multi-Version Concurrency Control lets readers use an older committed version while a writer creates a newer version. Ordinary reads therefore do not block ordinary writes, and ordinary writes do not block ordinary reads.

Logical account 42

Version A: balance = 500, xmin = 100, xmax = 125
Version B: balance = 450, xmin = 125, xmax = 0

Transaction 125 performed:
UPDATE accounts SET balance = 450 WHERE account_id = 42;

Old snapshot: Version A may be visible
New snapshot after transaction 125 commits: Version B may be visible

An UPDATE is conceptually a delete of the old tuple version plus an insert of a new tuple version. The old version cannot be reclaimed while an active snapshot might still need it. A DELETE marks a version as deleted but does not immediately remove its bytes. Routine VACUUM later makes dead-version space reusable after it is safe.

MVCC reduces read/write contention, but it does not mean "no locks." Writers of the same row can wait for each other, schema changes can conflict with queries, and constraints can coordinate concurrent transactions. Explicit lock modes belong to the locking topic; the important point here is that snapshot visibility and blocking are different mechanisms.

05. Tuple xmin, xmax and visibility

Every table has system columns. For MVCC, xmin identifies the transaction that created a row version and xmax usually identifies the transaction that deleted, updated, or locked it.

SELECT
  account_id,
  balance,
  xmin,
  xmax,
  ctid
FROM accounts
WHERE account_id = 42;

-- Use modern transaction and snapshot information functions.
SELECT pg_current_xact_id_if_assigned();
SELECT pg_current_snapshot();

A simplified visibility checklist

1. Is the inserting transaction this transaction? If so, command ordering determines whether the version is visible to later commands in the same transaction.
2. Otherwise, did the inserting transaction commit before the snapshot, and was it not in progress for that snapshot? If not, the version is not visible.
3. Has a deleting or updating transaction marked the version? If that transaction is aborted or not visible to the snapshot, the older version can remain visible.
4. If the deleting transaction committed and is visible to the snapshot, the old version is no longer visible.

06. Snapshots: a consistent visibility decision

A snapshot is not a full copy of the database. It is compact transaction visibility information used to decide which stored row versions a statement may see.

100:110:103,108

xmin = 100
xmax = 110
xip_list = 103,108

XID less than 100:
  completed before the snapshot horizon, then visibility depends on commit status

XID 103 or 108:
  in progress at snapshot time, so its new effects are invisible

XID from 100 through 109 but not in xip_list:
  completed by snapshot time, then commit or abort status decides

XID 110 or greater:
  had not completed as of the snapshot and is invisible

A transaction always sees its own earlier changes even though no other transaction can see them before commit. At READ COMMITTED, each command normally gets a fresh snapshot. At REPEATABLE READ and SERIALIZABLE, the transaction uses the snapshot established by its first query or data-changing statement.

07. Concurrency anomalies in plain language

Isolation levels are easiest to understand by the bad outcomes they permit or prevent. The SQL standard names dirty reads, nonrepeatable reads, phantom reads, and serialization anomalies.

Where does lost update fit?

"Lost update" is a widely used practical term, but its exact definition varies. The classic form is application-side read-modify-write:

-- Both transactions read 10.
SELECT quantity FROM inventory WHERE sku = 'A';

-- Each application calculates 10 - 1 and sends 9.
UPDATE inventory SET quantity = 9 WHERE sku = 'A';

-- Both can commit, but two sales reduced quantity only once.

Prefer one atomic SQL expression when possible:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = 'A'
  AND quantity > 0
RETURNING quantity;

Concurrent writers of this row serialize at the row update, and the second statement evaluates against the current row version under READ COMMITTED. For a disconnected workflow, use optimistic concurrency with an explicit version column:

UPDATE documents
SET body = $1,
    version = version + 1
WHERE document_id = $2
  AND version = $3
RETURNING version;

-- Zero returned rows means another writer changed the document.

08. The four isolation levels in PostgreSQL

SQL names four levels, but PostgreSQL implements three distinct behaviors because READ UNCOMMITTED behaves exactly like READ COMMITTED.

"Prevented" does not mean every transaction always commits. PostgreSQL may wait for a concurrent writer or abort a transaction to preserve the requested guarantee. Higher isolation trades some retry risk and overhead for stronger reasoning. It is not a substitute for constraints, correct transaction boundaries, or handling external side effects.

09. Read Committed: one snapshot per command

READ COMMITTED is PostgreSQL's default. A plain query sees rows committed before that query began plus earlier changes from its own transaction.

-- Session A                            -- Session B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT status FROM orders
WHERE order_id = 10; -- 'pending'

                                       BEGIN;
                                       UPDATE orders
                                       SET status = 'paid'
                                       WHERE order_id = 10;
                                       COMMIT;

SELECT status FROM orders
WHERE order_id = 10; -- 'paid'
COMMIT;

Each individual statement sees a consistent snapshot, so it does not see half of Session B's transaction. However, Session A's second statement starts later and gets a newer snapshot. Therefore a multi-query report can mix different committed moments.

Concurrent writes under Read Committed

An UPDATE, DELETE, MERGE, or row-locking command finds targets using its command snapshot. If a target was concurrently changed, the command can wait. After the other transaction commits, PostgreSQL can follow the updated version and re-evaluate the command's condition against it.

UPDATE jobs
SET status = 'running',
    started_at = clock_timestamp()
WHERE job_id = 900
  AND status = 'queued'
RETURNING *;

-- Exactly one contender changes queued to running.
-- Other contenders eventually return zero rows.

10. Read Uncommitted: an alias in PostgreSQL

PostgreSQL accepts the SQL-standard name READ UNCOMMITTED, but gives it READ COMMITTED behavior. Dirty reads are not available.

BEGIN ISOLATION LEVEL READ UNCOMMITTED;

SHOW transaction_isolation;
-- PostgreSQL reports read uncommitted as the requested characteristic,
-- but visibility behavior is the same as read committed.

SELECT * FROM accounts;
COMMIT;

This is an important interview detail. Do not describe PostgreSQL as exposing another session's uncommitted tuple data through this level. PostgreSQL maps the level to its MVCC-compatible READ COMMITTED behavior.

11. Repeatable Read: one stable transaction snapshot

PostgreSQL REPEATABLE READ uses one transaction snapshot. Successive queries see the same committed database view plus the transaction's own changes.

-- Session A                            -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders
WHERE status = 'paid'; -- 50

                                       INSERT INTO orders (order_id, status)
                                       VALUES (999, 'paid');
                                       COMMIT;

SELECT COUNT(*) FROM orders
WHERE status = 'paid'; -- still 50
COMMIT;

-- A transaction started after both commits can see 51.

The SQL standard permits phantoms at Repeatable Read, but PostgreSQL's snapshot isolation implementation prevents them. A repeated predicate sees the same row set from concurrent transactions. PostgreSQL's implementation is therefore stronger than the standard minimum on that dimension.

Updating a row changed after the snapshot

If a Repeatable Read transaction tries to modify or lock a row that another transaction changed after its snapshot, it can fail with could not serialize access due to concurrent update. The application must roll back and retry the complete unit of work.

12. Serializable and Serializable Snapshot Isolation

SERIALIZABLE guarantees that committed transactions have an effect consistent with some serial, one-at-a-time order. PostgreSQL preserves MVCC concurrency and aborts a transaction when dangerous read/write dependencies make that guarantee impossible.

PostgreSQL implements this with Serializable Snapshot Isolation, commonly abbreviated SSI. A serializable transaction reads from a stable snapshot like Repeatable Read. PostgreSQL also tracks read/write dependency patterns, including predicate reads. If concurrent work forms a dangerous structure that could produce a non-serializable result, one transaction receives a serialization failure instead of all conflicting results being committed.

-- Initially Alice and Bob are both on call.

-- Session A                            -- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;     BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM doctors            SELECT COUNT(*) FROM doctors
WHERE on_call; -- 2                     WHERE on_call; -- 2

UPDATE doctors                          UPDATE doctors
SET on_call = false                     SET on_call = false
WHERE name = 'Alice';                   WHERE name = 'Bob';

COMMIT;                                 COMMIT;

-- PostgreSQL cannot allow both effects to commit because no serial order
-- would let both transactions read 2 and leave nobody on call.
-- One transaction can fail with SQLSTATE 40001.

Serializable does not mean PostgreSQL runs every transaction one after another. Many serializable transactions execute concurrently and commit. It also does not promise the wall-clock order you imagined. It promises that some valid serial order explains the committed effects.

The guarantee requires a consistent policy

If a business rule depends on several writers, running only one of them at Serializable cannot make non-serializable writers participate in SSI dependency tracking. For a serializable guarantee over permanent data, all transactions that can affect the invariant should follow the required policy.

13. Worked isolation scenarios

The fastest way to master isolation is to predict two-session outcomes before running them.

Scenario A: dirty read attempt

-- Session A                            -- Session B
BEGIN;                                  BEGIN ISOLATION LEVEL READ UNCOMMITTED;
UPDATE accounts
SET balance = 0
WHERE account_id = 42;

                                       SELECT balance FROM accounts
                                       WHERE account_id = 42;
                                       -- Sees the previously committed balance.

ROLLBACK;                               COMMIT;

Scenario B: phantom at Read Committed

-- Session A                            -- Session B
BEGIN;
SELECT COUNT(*) FROM bookings
WHERE room_id = 7
  AND booking_date = DATE '2026-08-01'; -- 3

                                       INSERT INTO bookings
                                         (room_id, booking_date)
                                       VALUES (7, DATE '2026-08-01');
                                       COMMIT;

SELECT COUNT(*) FROM bookings
WHERE room_id = 7
  AND booking_date = DATE '2026-08-01'; -- 4
COMMIT;

Scenario C: write skew at Repeatable Read

-- Rule: at least one operator must remain active.
-- Both transactions use REPEATABLE READ and see two active operators.

-- Session A updates operator 1 to inactive.
-- Session B updates operator 2 to inactive.
-- They do not update the same row.
-- Both may commit, leaving zero active operators.

-- SERIALIZABLE detects the dangerous dependency pattern and aborts one.
-- A schema redesign or explicit coordination may also enforce the rule.

Scenario D: check-then-insert race

BEGIN;

SELECT 1 FROM users WHERE email = 'sam@example.com';
-- No row does not reserve this email.

INSERT INTO users (email) VALUES ('sam@example.com');
-- A concurrent transaction may insert it first.

COMMIT;

-- Define UNIQUE (email), then handle the conflict or use ON CONFLICT.

14. Setting transaction characteristics

Isolation, access mode, and deferrability can be set when a transaction starts or before its first query or data-changing statement.

BEGIN;
SET TRANSACTION
  ISOLATION LEVEL SERIALIZABLE
  READ WRITE
  NOT DEFERRABLE;
-- The SET must happen before the first query or data-changing statement.

SHOW transaction_isolation;
SHOW transaction_read_only;
COMMIT;

-- Default for later transactions in this session.
SET SESSION CHARACTERISTICS AS TRANSACTION
  ISOLATION LEVEL REPEATABLE READ
  READ ONLY;

READ ONLY rejects many commands that change non-temporary tables or schema objects. It expresses intent and reduces accidental writes, but it is not a statement that the server will perform no internal disk writes.

Serializable Read Only Deferrable

BEGIN TRANSACTION
  ISOLATION LEVEL SERIALIZABLE
  READ ONLY
  DEFERRABLE;

SELECT region, SUM(amount)
FROM payments
GROUP BY region;

COMMIT;

This transaction may wait while acquiring its initial snapshot. Once PostgreSQL finds a safe snapshot, the transaction can run without normal Serializable tracking overhead and without risk of a serialization cancellation caused by concurrent updates. DEFERRABLE has an effect only with SERIALIZABLE READ ONLY.

Exporting and importing a snapshot

-- Exporting transaction
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT pg_export_snapshot();
-- Example result: 00000003-0000001B-1

-- Importing transaction, before its first query
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';

-- The exporter must remain open while the snapshot is imported.

Snapshot export is useful for coordinated parallel reporting or backup tooling. It also prolongs the visibility horizon while the exporting and importing transactions remain open, so keep the operation bounded.

15. Correct transaction retry design

A serialization failure is not a server defect. It is PostgreSQL refusing a concurrent outcome that violates the chosen guarantee. The application must retry the complete transaction.

for attempt from 1 to MAX_ATTEMPTS:
    begin transaction isolation level serializable
    try:
        read every value used to make the decision
        recompute the decision
        perform every database write
        commit
        return success
    catch SQLSTATE 40001:
        rollback
        if attempt == MAX_ATTEMPTS:
            report transient failure
        wait using bounded exponential backoff plus jitter
    catch any other error:
        rollback
        rethrow

• Retry SQLSTATE 40001 from the beginning, including reads and application-side calculations. Retrying only the final UPDATE reuses a stale decision.
• Deadlock SQLSTATE 40P01 is often retryable too, but it is a different failure and can reveal inconsistent lock ordering.
• Bound retries. Use exponential backoff and jitter so many conflicting workers do not retry in lockstep.
• Keep effects outside PostgreSQL idempotent or delay them until the database outcome is known. A retried transaction can otherwise send an email or charge a card twice.
• Generate an idempotency key at the operation boundary and enforce it with a unique constraint when requests themselves may be repeated.

16. Performance and operational implications

Transactions are not only correctness boundaries. Their duration and contention shape vacuum progress, lock duration, memory use, latency, and retry rates.

SELECT
  pid,
  usename,
  state,
  xact_start,
  backend_xid,
  backend_xmin,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

A long transaction is not automatically wrong. A consistent backup or report may require one. The operational lesson is to make its cost intentional and monitored. The separate VACUUM topic explains cleanup and transaction ID wraparound in depth.

17. PostgreSQL transaction edge cases

Most table changes are transactional, but several PostgreSQL behaviors surprise developers who assume rollback rewinds every observable effect.

BEGIN;
INSERT INTO orders (description) VALUES ('cancelled attempt')
RETURNING order_id; -- suppose this returns 100
ROLLBACK;

INSERT INTO orders (description) VALUES ('real order')
RETURNING order_id; -- commonly returns 101, not 100

18. Common mistakes and precise fixes

19. Choosing an isolation strategy

Start from the invariant and concurrent decision pattern, not from a belief that the strongest isolation is always the fastest or that the default is always enough.

1. Write the invariant in one sentence.
2. List every row or predicate read to make the decision.
3. List every concurrent transaction that can change those facts.
4. Prefer constraints and atomic SQL for invariants they can express directly.
5. Choose isolation for the remaining multi-statement or multi-row reasoning.
6. Design retries and idempotency before enabling a retry-producing level.
7. Test the interleavings using two real sessions, not only single-session unit tests.

20. Practice problems

Predict the visibility, blocking, and commit outcome before reading each answer.

21. Interview Q&A

22. One-screen cheat sheet

• Transaction: one atomic database unit ending in commit or rollback.
• Autocommit: usually one implicit transaction per statement.
• Savepoint: partial rollback point, never an independent commit.
• MVCC: concurrent snapshots choose among physical tuple versions.
• UPDATE: marks an old version and creates a replacement version.
• Tuple xmin: XID that created a physical row version.
• Tuple xmax: deletion, update, or locking metadata; nonzero is not enough.
• Snapshot: transaction visibility information, not a database copy.
• Read Uncommitted: behaves like Read Committed in PostgreSQL.
• Read Committed: fresh snapshot per command; default level.
• Repeatable Read: stable transaction snapshot; no PostgreSQL phantoms.
• Repeatable Read risk: serialization anomalies such as write skew.
• Serializable: serial outcome through SSI dependency checks and aborts.
• 40001: retry the entire transaction from fresh reads.
• Atomic update: compute from the current column in one SQL statement.
• Optimistic edit: explicit version column plus compare-and-set.
• Constraint: final authority for expressible data invariants.
• Sequences: unique allocation can have gaps and does not rewind on rollback.
• Operational rule: keep transactions short and never idle unintentionally.

23. Official reference map

• MVCC and PostgreSQL concurrency control introduction
• Transaction isolation levels, anomalies, and PostgreSQL behavior
• Serialization failure handling and retry SQLSTATE codes
• BEGIN syntax, transaction modes, and autocommit behavior
• COMMIT behavior and syntax
• ROLLBACK behavior and syntax
• SAVEPOINT semantics and nesting behavior
• ROLLBACK TO SAVEPOINT semantics and cursor caveats
• RELEASE SAVEPOINT behavior
• SET TRANSACTION, read-only, deferrable, and snapshot import
• System columns including xmin, xmax, and ctid
• Transaction ID and snapshot information functions
• MVCC row-version cleanup and effects of old transactions
• Sequence allocation and non-rollback behavior
• MVCC caveats for DDL, catalogs, and standby behavior