skip to content

MVCC & Locking

PostgreSQL writes a new row version on every UPDATE, judges visibility by snapshot, and adds table, row and advisory locks on top. Interviewers probe it through blocking and 40001 retries.

part ofPostgreSQLread it →
on this pageshow

explore

questions

14

In PostgreSQL, what is the default transaction isolation level, how do you choose another, and what do you get if you request READ UNCOMMITTED?

level: juniorimportance: must knowfreq 78%

answer

  1. four names, three behaviours
  2. a GUC with a default_ prefix
  3. the first query fixes the level
  4. what the weakest name maps to

basics

~20 s

PostgreSQL defaults to READ COMMITTED. You pick a level per transaction with BEGIN ISOLATION LEVEL or SET TRANSACTION before its first query, or change default_transaction_isolation; READ UNCOMMITTED is accepted but behaves exactly like READ COMMITTED.

solid answer

~40 s

The default is `READ COMMITTED`, from `default_transaction_isolation = 'read committed'`. For one transaction you write `BEGIN ISOLATION LEVEL REPEATABLE READ`, or issue `SET TRANSACTION ISOLATION LEVEL ...` straight after `BEGIN`; once the transaction has run a query and taken its snapshot, a change fails with `SET TRANSACTION ISOLATION LEVEL must be called before any query`. A different default can be set in `postgresql.conf`, with `ALTER DATABASE` or `ALTER ROLE ... SET`, or for a session with `SET SESSION CHARACTERISTICS`. All four standard names are accepted, but PostgreSQL implements three behaviours: `READ UNCOMMITTED` runs exactly like `READ COMMITTED`, because a reader picks row versions by snapshot and never sees another transaction's uncommitted version. `SHOW transaction_isolation` reports the level the transaction requested.

code

sql · 14 lines
sql
SHOW default_transaction_isolation;      -- read committed

BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;              -- repeatable read
SELECT count(*) FROM seats WHERE event_id = 42 AND status = 'available';
COMMIT;

BEGIN;
SELECT count(*) FROM seats WHERE event_id = 42;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
ROLLBACK;

ALTER ROLE ticketing_app SET default_transaction_isolation = 'serializable';

go deeper

for a junior

Recall the default, READ COMMITTED, and the two ways to pick a level for one transaction: BEGIN ISOLATION LEVEL, or SET TRANSACTION as the first statement.

for a middle

Explain why READ UNCOMMITTED maps to READ COMMITTED: readers choose row versions by snapshot, so another transaction's uncommitted version is never visible.

for a senior

Show how you would raise the level safely: a per-role default, a retry path for 40001, and awareness that a hot standby tops out at REPEATABLE READ.

for a principal

Weigh a server-wide stronger default against per-transaction opt-in: who owns the retry logic, which clients break, and how the level is enforced across services.

## The default and where it comes from Every PostgreSQL transaction runs at an **isolation level**, which decides what it can see of other transactions' work. Out of the box that level is **READ COMMITTED**: the configuration parameter `default_transaction_isolation` ships as `'read committed'`, and each new transaction copies it into `transaction_isolation`. At this level every statement takes a fresh **snapshot** — a record of which transactions had committed when the statement began — so two `SELECT`s in one transaction can see different committed data. READ COMMITTED is the default because it is cheap and forgiving: when an `UPDATE` runs into a row another session has just changed, it waits and then works on the newest committed version instead of failing. The stronger levels trade that for a stable view and can end a transaction with a **serialization failure** (SQLSTATE `40001`) that the application must retry. ## Choosing a level There are several equivalent ways to ask for a level; they differ only in scope. | Scope | How | Example | |---|---|---| | One transaction | `BEGIN` / `START TRANSACTION` with the level | `BEGIN ISOLATION LEVEL REPEATABLE READ;` | | One transaction | `SET TRANSACTION` right after `BEGIN` | `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;` | | Rest of the session | `SET SESSION CHARACTERISTICS` or `SET default_transaction_isolation` | `SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;` | | A role or a database | `ALTER ROLE` / `ALTER DATABASE ... SET` | `ALTER ROLE ticketing_app SET default_transaction_isolation = 'serializable';` | | Whole server | `postgresql.conf` | `default_transaction_isolation = 'repeatable read'` | `SET SESSION CHARACTERISTICS` is only a verbose spelling of setting `default_transaction_isolation`. The per-transaction parameter `transaction_isolation` cannot be placed in the configuration file; it exists so the current transaction's level can be read or set from SQL. Client drivers usually expose the same choice as a connection or transaction property and send one of these commands for you. ## The timing rule The level must be fixed **before the transaction takes its first snapshot**, which happens at its first query or data-modifying statement. Run a `SELECT` first and then try to change the level, and PostgreSQL raises `SET TRANSACTION ISOLATION LEVEL must be called before any query`; like any error, that aborts the transaction. Setting the level to the value it already has is always allowed, and a change inside a subtransaction (after a `SAVEPOINT`) is rejected, so the level belongs to the top-level transaction. The read-only and deferrable properties follow the same before-the-first-query rule. ## Four names, three behaviours The SQL standard names four levels. PostgreSQL accepts all four and implements three: | You request | You get | Snapshot taken | |---|---|---| | `READ UNCOMMITTED` | READ COMMITTED behaviour | per statement | | `READ COMMITTED` (default) | READ COMMITTED | per statement | | `REPEATABLE READ` | snapshot isolation | once, at the first query | | `SERIALIZABLE` | Serializable Snapshot Isolation (SSI) | once, at the first query, plus conflict monitoring | READ UNCOMMITTED collapses into READ COMMITTED because of the storage model. An `UPDATE` in PostgreSQL does not overwrite a row in place; it writes a new **row version** stamped with the writing transaction's ID, and every reader decides from its snapshot which version it may see. A version written by a transaction that has not committed is invisible to every other transaction, so there is no path by which a reader could see uncommitted data. The manual calls this mapping the only sensible one for its MVCC design. The standard lists the anomalies each level must *prevent*, so giving more than was asked is conformant — which is also why PostgreSQL's REPEATABLE READ prevents phantom reads the standard would allow. ## Checking and enforcing - `SHOW transaction_isolation;` (or `current_setting('transaction_isolation')`) reports the level the current transaction requested; after asking for READ UNCOMMITTED it says `read uncommitted`, although the behaviour is READ COMMITTED. - `SHOW default_transaction_isolation;` shows what new transactions will get. - A per-role default is the usual way to move one application's connections to a stronger level without touching other clients. - On a hot standby SERIALIZABLE is not available: requesting it fails with `cannot use serializable mode in a hot standby`, and REPEATABLE READ is the strongest level a read replica offers. ## Traps interviewers listen for 1. Assuming the default is SERIALIZABLE because the SQL standard names it as the default. 2. Choosing READ UNCOMMITTED to make reports faster: plain reads in PostgreSQL already do not wait for row writers, and the request changes nothing. 3. Issuing `SET TRANSACTION` after the first query and assuming it took effect. 4. Raising the default without giving the application a retry path for `40001`.

  • Why does PostgreSQL refuse to change the isolation level after the transaction's first query?
    The level decides how the snapshot is taken: per statement at READ COMMITTED, once per transaction at REPEATABLE READ and SERIALIZABLE, plus predicate locking at SERIALIZABLE. After the first query the transaction already holds a snapshot taken under the old rules, so switching would leave it half one level and half another. The check is whether a snapshot exists, so `SET TRANSACTION` must precede any query.
  • Can you run SERIALIZABLE transactions on a PostgreSQL streaming standby?
    No. Serializable is not supported on a hot standby; requesting it fails with `cannot use serializable mode in a hot standby`, and REPEATABLE READ is the strongest level there. Even when every write on the primary is serializable, a REPEATABLE READ query on the standby can see a transient state that no serial order of the primary's transactions would produce.
  • If you set default_transaction_isolation to serializable for the ticketing role, what does the application now owe?
    A retry path. Both REPEATABLE READ and SERIALIZABLE can end a transaction with SQLSTATE `40001`, and PostgreSQL offers no automatic retry, so every transaction that role runs must be rerun from the start by the client when it fails that way. The manual also suggests guarding against other levels sneaking in, for example by checking the level in triggers.

  • PostgreSQL's default isolation level is SERIALIZABLE, as in the SQL standard.
  • READ UNCOMMITTED in PostgreSQL lets a report read rows other sessions have not committed.
  • SET TRANSACTION ISOLATION LEVEL can be issued at any point inside a transaction.
  • Switching to READ UNCOMMITTED stops PostgreSQL readers waiting on writers.
  • A hot standby serves SERIALIZABLE read transactions just like the primary.
open as a page

In PostgreSQL, does an UPDATE overwrite the row in place, and what happens to a table's size after you update every row?

level: juniorimportance: must knowfreq 62%

basics

~20 s

No. A PostgreSQL UPDATE writes a complete new row version and stamps the old one's xmax with the updating transaction's ID; the old version stays until VACUUM reclaims it, so updating every row can roughly double the table.

open as a page

Under PostgreSQL's REPEATABLE READ, what happens when an UPDATE targets a row changed and committed after the snapshot, and what must the application do?

level: middleimportance: must knowfreq 64%

basics

~20 s

It waits for the other writer; if that transaction commits an update or delete of the row, the UPDATE fails with could not serialize access due to concurrent update, SQLSTATE 40001. The application must roll back and rerun the whole transaction.

open as a page

In PostgreSQL, how should workers of a transactional-email queue claim rows with SKIP LOCKED, and how does that differ from NOWAIT?

level: middleimportance: must knowfreq 66%

basics

~20 s

Each worker runs a SELECT ... FOR NO KEY UPDATE SKIP LOCKED LIMIT n that locks only rows no other transaction holds, so workers get disjoint batches without waiting. NOWAIT instead raises SQLSTATE 55P03 the moment a selected row is locked.

open as a page

In PostgreSQL, which table-level lock do SELECT, SELECT ... FOR UPDATE and UPDATE each take, and which mode alone blocks a plain SELECT?

level: juniorimportance: should knowfreq 52%

basics

~20 s

A plain SELECT takes ACCESS SHARE, SELECT ... FOR UPDATE takes ROW SHARE and UPDATE takes ROW EXCLUSIVE, and none of the three conflict. Only ACCESS EXCLUSIVE, taken by DROP TABLE, TRUNCATE and many ALTER TABLE forms, blocks a plain SELECT.

open as a page

In PostgreSQL, what are the four row-lock strengths, and why do foreign-key checks take FOR KEY SHARE rather than FOR SHARE?

level: middleimportance: should knowfreq 44%

basics

~20 s

PostgreSQL's row locks, strongest first, are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE. A foreign-key check only needs the referenced key to survive, so FOR KEY SHARE lets non-key updates of the parent row proceed.

open as a page

In PostgreSQL, a row returned by SELECT xmin, xmax, ctid has a nonzero xmax; what do these columns mean, and why is it visible?

level: middleimportance: should knowfreq 44%

basics

~20 s

In PostgreSQL, xmin is the creating XID, xmax the XID that deleted, updated or locked the version, and ctid its address. A visible row keeps a nonzero xmax when that transaction is still running, rolled back, only locked it, or committed after your snapshot.

open as a page

A PostgreSQL concert-ticket reservation service moved to SERIALIZABLE and now logs SQLSTATE 40001 read/write dependency errors; how do you diagnose and reduce them?

level: seniorimportance: should knowfreq 36%

basics

~20 s

Treat some 40001s as normal, retried work, then cut the excess: find relation-level SIReadLock entries in pg_locks, index the filtered columns so scans lock pages not tables, raise the max_pred_locks limits, mark reports READ ONLY and keep transactions short.

open as a page

In PostgreSQL, how do session-level and transaction-level advisory locks differ, and why does a session-level one leak behind PgBouncer's transaction pooling?

level: seniorimportance: should knowfreq 42%

basics

~20 s

A session-level advisory lock is held until pg_advisory_unlock or disconnect, even across ROLLBACK; a transaction-level one ends with its transaction. Behind transaction pooling the unlock can reach another server connection, so the lock stays held.

open as a page

A PostgreSQL email worker fails with SQLSTATE 40P01 after a one-second stall; what did deadlock_timeout do, and how do the error and pg_locks reveal the cycle?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A lock waiter sleeps for deadlock_timeout (default 1s) before searching for a wait cycle; on finding one, PostgreSQL aborts one transaction with 40P01. Its DETAIL lists who waits on which transaction, and pg_locks shows row waits as ungranted transactionid ShareLocks.

open as a page

Right after a bulk load into a PostgreSQL table, why does the first full scan dirty pages and generate WAL while later scans do not?

level: seniorimportance: should knowfreq 32%

basics

~20 s

Freshly loaded tuples carry only the loader's XID. The first reader checks pg_xact and caches the commit status as hint bits in each tuple header, dirtying every page; with data checksums on, PostgreSQL 18's initdb default, that also writes full-page images to WAL.

open as a page

Four PostgreSQL import workers each wrap every one of 5,000 order lines in SAVEPOINT and RELEASE SAVEPOINT inside one transaction, and the cluster slows to a crawl; why, and what fixes it?

level: seniorimportance: should knowfreq 42%

basics

~20 s

In PostgreSQL each writing savepoint gets its own subtransaction XID, and a backend caches only 64. RELEASE keeps the slot, so past 64 the cache overflows and other sessions' snapshots resolve XIDs through pg_subtrans, contending on SubtransSLRU.

open as a page

In PostgreSQL, SELECT pg_current_snapshot() returns 742:750:742,745; what do the three parts mean, and whose changes does that snapshot see?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

The format is xmin:xmax:xip_list. XIDs below 742 had finished, 742 and 745 were in progress, 743, 744 and 746 to 749 had finished, and 750 or higher had not. Finished XIDs are seen only if pg_xact says they committed.

open as a page

In PostgreSQL, why can a read-only SERIALIZABLE report still fail with 40001, and what does SERIALIZABLE READ ONLY DEFERRABLE change?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

Under SSI a read-only transaction can be the reader that completes a dangerous chain of read/write conflicts, so it can be cancelled. READ ONLY DEFERRABLE waits for a safe snapshot, then runs without predicate locks and can neither fail nor cause failures.

open as a page