Menu
CoddyTech

PostgreSQL Transactions and MVCC Interview Questions

ACID, MVCC, isolation levels, row locks and job queues, deadlocks, savepoints, advisory locks, transactional DDL and long-running transactions.

11 questions
  1. What are the ACID properties, and how does PostgreSQL provide them?

    Atomicity, consistency, isolation, durability: a transaction applies fully or not at all, leaves every constraint satisfied, does not see others' unfinished work, and survives a crash once committed.

    PostgreSQL gets atomicity and isolation from MVCC (row versions become visible only at commit), consistency from constraints, and durability from the write-ahead log. By default, COMMIT returns only after its WAL record is flushed to disk. Outside BEGIN, each statement is its own transaction. More in the DBMS questions.

  2. Does UPDATE change a row in place in PostgreSQL?

    PostgreSQL
    CREATE TABLE accounts (id int PRIMARY KEY, balance int);
    INSERT INTO accounts VALUES (1, 100);
    UPDATE accounts SET balance = 90 WHERE id = 1;
    SELECT ctid, balance FROM accounts;

    It returns (0,2) | 90. ctid is a row version's physical address (page 0, item 2). UPDATE writes a new version and sets the old version's xmax to the updating transaction's id, so the old one is dead once that transaction commits. That is MVCC. Each statement sees the versions its snapshot allows, so readers and writers never block each other, and VACUUM later reclaims the dead versions.

  3. What are the transaction isolation levels in PostgreSQL, and which is the default?

    Read Committed is the default. Read Uncommitted is accepted but behaves as Read Committed, so dirty reads never happen.

    LevelSeesStill possible
    Read Committeddata committed before each statementnon-repeatable reads, phantoms
    Repeatable Readone snapshot for the transactionwrite skew
    Serializableone snapshot, plus conflict checksnothing: one transaction aborts

    PostgreSQL's Repeatable Read also prevents phantom reads, which the standard allows.

    PostgreSQL
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SHOW transaction_isolation;

    Transaction isolation

  4. What is a serialization failure in PostgreSQL, and how should an application handle it?

    It is SQLSTATE 40001. Repeatable Read raises it when a row you update was changed by a concurrent transaction. Serializable also raises it when no serial order of the transactions gives the same result. Retry the whole transaction from BEGIN, not only the failed statement.

    Serializable's classic catch is write skew: two doctors on call each check that the other is on call, then sign off. Under Repeatable Read both commit; under Serializable one gets 40001. Retry deadlocks (40P01) the same way.

  5. How do you build a job queue in PostgreSQL with FOR UPDATE SKIP LOCKED?

    Each worker locks the next free job with FOR UPDATE SKIP LOCKED: rows another worker holds are skipped, not waited on, so no two workers take the same job.

    PostgreSQL
    CREATE TABLE jobs (id int PRIMARY KEY, status text NOT NULL DEFAULT 'queued');
    INSERT INTO jobs (id) VALUES (1), (2), (3);
    WITH next AS (
      SELECT id FROM jobs
      WHERE status = 'queued'
      ORDER BY id
      LIMIT 1
      FOR UPDATE SKIP LOCKED
    )
    UPDATE jobs SET status = 'running'
    FROM next
    WHERE jobs.id = next.id
    RETURNING jobs.id, jobs.status;

    Either process the job inside that transaction, or mark it running and commit as here, with a timeout that requeues jobs from crashed workers. NOWAIT raises an error instead of skipping.

  6. What is a lost update, and how do you prevent it in PostgreSQL?

    Two transactions read a value, compute a new one in the application and write it back; the second write erases the first. Read Committed allows it. The fixes: do the arithmetic in SQL (balance = balance - 30), lock first with SELECT ... FOR UPDATE, add a version column and retry when no row changes, or use Repeatable Read and retry on the serialization error.

    PostgreSQL
    CREATE TABLE accounts (id int PRIMARY KEY, balance int CHECK (balance >= 0));
    INSERT INTO accounts VALUES (1, 100);
    UPDATE accounts SET balance = balance - 30 WHERE id = 1
    RETURNING balance;

    Of two racing withdrawals, the second waits for the row lock, rereads the new balance, and the CHECK rejects it if the result would go below zero.

  7. How does PostgreSQL handle deadlocks?

    After a session has waited deadlock_timeout (1 second by default) for a lock, PostgreSQL checks for a deadlock. If it finds one, it aborts one transaction with deadlock detected (SQLSTATE 40P01) so the other can proceed.

    -- Session A                           -- Session B
    BEGIN;                                 BEGIN;
    UPDATE accounts SET ... WHERE id = 1;
                                           UPDATE accounts SET ... WHERE id = 2;
    UPDATE accounts SET ... WHERE id = 2;  -- A waits for B
                                           UPDATE accounts SET ... WHERE id = 1;  -- deadlock
    

    Prevent them by locking rows in the same order everywhere (for example ORDER BY id FOR UPDATE) and keeping transactions short. Retry the aborted transaction.

  8. What does ROLLBACK TO SAVEPOINT undo in PostgreSQL?

    PostgreSQL
    BEGIN;
    CREATE TABLE items (n int);
    INSERT INTO items VALUES (1);
    SAVEPOINT s;
    INSERT INTO items VALUES (2);
    ROLLBACK TO SAVEPOINT s;
    INSERT INTO items VALUES (3);
    COMMIT;
    SELECT string_agg(n::text, ',' ORDER BY n) FROM items;

    It returns 1,3. ROLLBACK TO SAVEPOINT s undoes only the work after the savepoint (inserting 2) and keeps the transaction open. In PostgreSQL any error inside a transaction block aborts the whole transaction (current transaction is aborted ...); a savepoint lets you undo only the failed step and carry on. ORMs use savepoints for nested transactions.

  9. What are advisory locks in PostgreSQL?

    Locks on a number your application picks, not on a row or table. PostgreSQL only ensures that two sessions cannot hold the same key; the meaning is yours, such as "one server runs the billing job".

    PostgreSQL
    SELECT pg_try_advisory_lock(42) AS first, pg_try_advisory_lock(42) AS again;

    Both are true: a session can take its own lock again (and must release it as many times); another session would get false. Session locks (pg_advisory_lock) survive a ROLLBACK. pg_advisory_xact_lock ends with the transaction, the safe choice behind PgBouncer in transaction pooling mode. Advisory locks

  10. Can you roll back a CREATE TABLE in PostgreSQL?

    PostgreSQL
    CREATE TABLE kept (id int);
    BEGIN;
    CREATE TABLE draft (id int);
    INSERT INTO draft VALUES (1);
    ROLLBACK;
    SELECT count(*) FROM pg_tables WHERE tablename IN ('kept', 'draft');

    It returns 1: kept existed before the transaction, and draft vanished with the ROLLBACK. Almost all DDL in PostgreSQL is transactional, so a migration can run in one transaction and leave nothing half done; MySQL commits DDL implicitly. The exceptions refuse to run inside a transaction block: CREATE DATABASE, CREATE INDEX CONCURRENTLY, VACUUM.

  11. Why are long-running transactions a problem in PostgreSQL?

    While an old transaction or query is running, VACUUM cannot remove rows that died after it started, in any table of the database, so tables and indexes bloat. Its locks stay too: if it holds one on a table, an ALTER TABLE waiting behind it blocks every later query on that table. The usual culprit is a session idle in transaction: the app ran BEGIN and never finished.

    SELECT pid, state, now() - xact_start AS open_for, query
    FROM pg_stat_activity
    WHERE xact_start IS NOT NULL
    ORDER BY xact_start
    LIMIT 5;
    

    Set idle_in_transaction_session_timeout, and keep network calls and user input out of transactions.

Coddy programming languages illustration

Learn to code with Coddy

Get started