In this post we are going to cover transactions, MVCC and locks in PostgreSQL: row versions, isolation levels, lost updates, deadlocks, SKIP LOCKED, the lock queue behind an ALTER TABLE, and how to find a blocking session. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
Concurrency questions separate people who have run Postgres in production from people who have only written queries. To make every answer concrete, each example below uses two sessions, session A and session B, and every result, wait and error message is real. To follow along, open two psql windows.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 7 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: A long report reads the accounts table while a batch job updates the same rows. In some databases the report would wait. In Postgres it does not. Why?
- Question 2: A finance report runs two queries inside one transaction. The total changes between the first query and the second, because another session committed in between. Is that a bug, and how do you get a consistent report?
- Question 3: Two workers withdraw money from the same account at the same time. One takes 100, the other takes 200. The balance goes from 1000 to 800 instead of 700. Where did the 100 go?
- Question 4: The application log shows: ERROR, deadlock detected. Two money transfers ran at the same time. What is a deadlock, what does Postgres do about it, and how do you prevent it?
- Question 5: Ten workers pull jobs from a jobs table. Two workers must never take the same job, and they should not wait for each other. How do you write the query that picks the next jobs?
- Question 6: During a deployment, a simple ALTER TABLE ADD COLUMN froze the whole application for minutes. The ALTER itself is instant. What happened?
- Question 7: Users report that updates on the accounts table hang. Nothing is in the error log. How do you find out which session is blocking the others?
- Key points to remember
- The full SQL script
Let's create some sample data
For this post we use two small tables: accounts, with three bank accounts, and jobs, a queue of five jobs. Open two database sessions, A and B, to follow along. Each code box says which session runs it. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
CREATE TABLE accounts (id INT PRIMARY KEY, owner TEXT, balance INT);
INSERT INTO accounts VALUES (1, 'Sara', 1000), (2, 'Omar', 500), (3, 'Lina', 750);
CREATE TABLE jobs (job_id INT PRIMARY KEY, payload TEXT, status TEXT DEFAULT 'queued');
INSERT INTO jobs (job_id, payload) VALUES
(1, 'send invoice 1001'), (2, 'send invoice 1002'), (3, 'send invoice 1003'),
(4, 'send invoice 1004'), (5, 'send invoice 1005');Question 1: A long report reads the accounts table while a batch job updates the same rows. In some databases the report would wait. In Postgres it does not. Why?
Because of MVCC, multi-version concurrency control. An UPDATE does not overwrite the row. It writes a new version and keeps the old one. Every query reads the versions that were committed when its snapshot was taken, so readers and writers do not block each other.
Here is one row during an update. Version one says the balance is 1000. Session A updates it. Postgres does not touch version one, except to stamp it with A's transaction ID as its end. It writes version two, with balance 900. Session B reads now. A has not committed, so B's snapshot still sees version one: 1000. No waiting. After A commits, new snapshots see version two, and version one is dead, waiting for vacuum.
xmax = A (ended by A)
xmax = 0
Let's do it for real. Session A opens a transaction, takes 100 from account one, and does not commit.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;Result: UPDATE 1
Session B reads the same row at that moment. It answers immediately, with the old balance, 1000. And the hidden columns show why: B still sees the old version, and its xmax is now set, by A's transaction.
SELECT ctid, xmin, xmax, balance
FROM accounts
WHERE id = 1;| ctid | xmin | xmax | balance |
|---|---|---|---|
| (0,1) | 2079 | 2082 | 1000 |
Session A reads its own change: a new version at a new position, created by its own transaction, with balance 900. Then A commits.
SELECT ctid, xmin, xmax, balance
FROM accounts
WHERE id = 1;| ctid | xmin | xmax | balance |
|---|---|---|---|
| (0,4) | 2082 | 0 | 900 |
The price of MVCC is the dead versions it leaves behind. Cleaning them up is the job of vacuum, and that is why a long open transaction is dangerous: it forces Postgres to keep old versions alive.
Question 2: A finance report runs two queries inside one transaction. The total changes between the first query and the second, because another session committed in between. Is that a bug, and how do you get a consistent report?
It is the default level, READ COMMITTED: every statement gets a fresh snapshot, so it sees whatever was committed before it started. For a consistent report use REPEATABLE READ: one snapshot for the whole transaction.
Compare the two levels on a timeline. The report reads a total of 2250. Another session adds 500 and commits. In Read Committed, the report's second query takes a new snapshot and sees 2750. The two numbers in one report do not match. In Repeatable Read, the second query reuses the first snapshot and still sees 2250.
First the default. Session A begins and reads the total: 2250.
BEGIN; -- READ COMMITTED is the default
SELECT SUM(balance) AS total FROM accounts;| total |
|---|
| 2250 |
Session B adds 500 to account two. It is in autocommit mode, so the change is committed at once.
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;Result: UPDATE 1
Session A repeats exactly the same query, still inside the same transaction. 2750. The total moved under the report's feet.
SELECT SUM(balance) AS total FROM accounts;| total |
|---|
| 2750 |
Now Repeatable Read. Session A begins with that level and reads 2750.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) AS total FROM accounts;| total |
|---|
| 2750 |
Session B commits another 500. Session A reads again: still 2750. Its snapshot is frozen until the transaction ends.
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;Result: UPDATE 1
SELECT SUM(balance) AS total FROM accounts;| total |
|---|
| 2750 |
Two facts interviewers like. In Postgres, Read Uncommitted behaves like Read Committed, so there are no dirty reads. And in Repeatable Read, if you try to update a row that someone else changed after your snapshot, you get a serialization error and must retry.
No dirty reads in Postgres, ever. Repeatable Read writers must be ready to retry.
Question 3: Two workers withdraw money from the same account at the same time. One takes 100, the other takes 200. The balance goes from 1000 to 800 instead of 700. Where did the 100 go?
It is a lost update. Both workers read 1000, calculated in the application, and wrote their own result, so the second write erased the first. Fix it with an atomic UPDATE, or lock the row first with SELECT ... FOR UPDATE.
Read, calculate, write, in two sessions at once. Both read 1000. A calculates 900 and writes it. B calculates 800 from its old read, and writes that. A's withdrawal is gone. With FOR UPDATE, B's read has to wait until A is finished, so B reads 900, and writes 700.
Here is the bug for real. Both sessions read the balance. Both get 1000.
SELECT balance FROM accounts WHERE id = 1;| balance |
|---|
| 1000 |
SELECT balance FROM accounts WHERE id = 1;| balance |
|---|
| 1000 |
A writes 900. B writes 800. And the final balance is 800.
UPDATE accounts SET balance = 900 WHERE id = 1;UPDATE accounts SET balance = 800 WHERE id = 1;SELECT balance FROM accounts WHERE id = 1;| balance |
|---|
| 800 |
The simplest fix is to let the database do the arithmetic. Each session subtracts in the UPDATE itself. The second UPDATE waits for the first, then works on the new value. 700. Correct.
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;UPDATE accounts
SET balance = balance - 200
WHERE id = 1;SELECT balance FROM accounts WHERE id = 1;| balance |
|---|
| 700 |
When the application must read first, for example to check a limit, open a transaction and read with FOR UPDATE. That locks the row until you commit. A version column with a check in the WHERE clause, called optimistic locking, is the third option.
Read-then-write? SELECT ... FOR UPDATE inside a transaction, or a version column.
Question 4: The application log shows: ERROR, deadlock detected. Two money transfers ran at the same time. What is a deadlock, what does Postgres do about it, and how do you prevent it?
Two transactions each hold a lock the other one needs, so neither can finish. Postgres detects the cycle after deadlock_timeout, one second by default, and aborts one of them. Prevent it by locking rows in the same order, and retry the aborted transaction.
Two transfers in opposite directions. A locks account one. B locks account two. Now A wants account two, which B holds. A waits. And B wants account one, which A holds. B waits too. Each one is waiting for the other, forever. After one second Postgres checks the wait graph, finds the cycle, and cancels one transaction, so the other can finish.
Let's cause one. Session A locks account one. Session B locks account two.
BEGIN;
UPDATE accounts SET balance = balance - 50
WHERE id = 1;BEGIN;
UPDATE accounts SET balance = balance - 70
WHERE id = 2;A now updates account two, and hangs, waiting for B. B updates account one. That closes the circle. One second later, session A gets the error: deadlock detected. Its transaction is rolled back, and B's update goes through.
UPDATE accounts SET balance = balance + 50
WHERE id = 2;UPDATE accounts SET balance = balance + 70
WHERE id = 1;ERROR: deadlock detected
DETAIL: Process 16965 waits for ShareLock on transaction 2094; blocked by process 16966.
Process 16966 waits for ShareLock on transaction 2093; blocked by process 16965.Result: UPDATE 1
The fix is in the application. Always lock rows in the same order, for example the smaller account ID first. Then the second transfer simply waits, and no cycle can form. Keep transactions short, and retry when you get this error, because the victim did nothing wrong.
Lock in a consistent order (lowest ID first), keep transactions short, and retry.
Question 5: Ten workers pull jobs from a jobs table. Two workers must never take the same job, and they should not wait for each other. How do you write the query that picks the next jobs?
Select the next rows with FOR UPDATE SKIP LOCKED. FOR UPDATE locks the rows a worker takes, and SKIP LOCKED tells the other workers to skip rows that are already locked, instead of waiting for them.
Two workers ask for two jobs each, at the same moment. Worker A takes jobs one and two, and locks them. Worker B runs the same query. Jobs one and two are locked, so it skips them and takes three and four. Nobody waits, and no job is taken twice. Job five stays in the queue for the next worker.
| job_id | status |
|---|---|
| 1 | queued |
| 2 | queued |
| 3 | queued |
| 4 | queued |
| 5 | queued |
Here is worker A. It begins a transaction and selects two queued jobs with FOR UPDATE SKIP LOCKED. It gets jobs one and two.
BEGIN;
SELECT job_id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY job_id
LIMIT 2
FOR UPDATE SKIP LOCKED;| job_id | payload |
|---|---|
| 1 | send invoice 1001 |
| 2 | send invoice 1002 |
Worker B runs the identical query while A's transaction is still open. It returns at once, with jobs three and four.
BEGIN;
SELECT job_id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY job_id
LIMIT 2
FOR UPDATE SKIP LOCKED;| job_id | payload |
|---|---|
| 3 | send invoice 1003 |
| 4 | send invoice 1004 |
Each worker then does the work, marks its jobs as done, and commits. If a worker crashes, its transaction rolls back, the locks disappear, and the jobs are free again. Without SKIP LOCKED, worker B would have waited for A, and your ten workers would run one at a time.
A crashed worker = rollback = its jobs return to the queue automatically.
Question 6: During a deployment, a simple ALTER TABLE ADD COLUMN froze the whole application for minutes. The ALTER itself is instant. What happened?
ALTER TABLE needs an ACCESS EXCLUSIVE lock, which conflicts with everything, even a SELECT. One long open transaction on the table made the ALTER wait, and every new query then queued behind the ALTER. Always set a lock_timeout before DDL.
Three players. A session read the table inside a transaction and never committed. It holds a small lock, harmless by itself. The ALTER TABLE arrives. It needs the strongest lock, so it waits for that session. Now comes the real damage. Every new query on the table, even a plain SELECT, lines up behind the waiting ALTER. The application looks frozen.
Let's reproduce it safely. Session A reads the table in a transaction and leaves it open.
BEGIN;
SELECT COUNT(*) FROM accounts;| count |
|---|
| 3 |
Session B is the deployment. First it sets a lock timeout of two seconds, then it runs the ALTER. After two seconds Postgres cancels the statement. The deployment fails, but the application never queued behind it.
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note TEXT;ERROR: canceling statement due to lock timeoutOnce session A commits, the same ALTER takes a few milliseconds.
ALTER TABLE accounts ADD COLUMN note TEXT;Result: ALTER TABLE
So the rule for every migration: set lock_timeout, and retry in a loop. And protect the server from forgotten transactions with idle_in_transaction_session_timeout.
Migrations: SET lock_timeout + retry. Server: idle_in_transaction_session_timeout.
Question 7: Users report that updates on the accounts table hang. Nothing is in the error log. How do you find out which session is blocking the others?
Look in pg_stat_activity for sessions that are waiting on a Lock, and ask pg_blocking_pids who blocks each one. The blocker is very often a session that is idle in transaction.
The picture is always the same. One session changed a row and has not committed. It is not running anything. It is idle in transaction. Another session wants the same row, so its query is active, but waiting on a lock. pg_blocking_pids takes the waiting process ID and returns the process ID of the session in its way.
state: idle in transaction
state: active, waiting on Lock
pg_blocking_pids(pid of B) → { pid of A }Set the scene. Session A updates account three and keeps its transaction open. Session B tries to update the same row, and hangs.
UPDATE accounts SET balance = balance + 1
WHERE id = 3; -- inside BEGIN, no COMMIT yetUPDATE accounts SET balance = 0
WHERE id = 3;From a third session, we ask pg_stat_activity. Session B is active and waiting on a Lock, and blocked_by shows one process ID. That ID is session A, which is idle in transaction. There is the culprit.
SELECT pid, state, wait_event_type,
pg_blocking_pids(pid) AS blocked_by,
left(query, 34) AS query
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid()
ORDER BY pid;| pid | state | wait_event_type | blocked_by | query |
|---|---|---|---|---|
| 16965 | idle in transaction | Client | {} | UPDATE accounts SET balance = bala |
| 16966 | active | Lock | {16965} | UPDATE accounts SET balance = 0 WH |
Then you decide. Ask the owner to commit, or end that session with pg_terminate_backend and its process ID. And log lock waits, with log_lock_waits, so the next incident is in the log.
End the blocker: SELECT pg_terminate_backend(pid); Turn on log_lock_waits.
Key points to remember
- MVCC: an UPDATE writes a new row version, so readers and writers do not block each other
- Read Committed takes a new snapshot per statement, Repeatable Read keeps one per transaction
- Avoid lost updates with an atomic UPDATE or SELECT ... FOR UPDATE
- Deadlocks: lock rows in the same order, and retry the transaction Postgres aborts
- Use SKIP LOCKED for queues, lock_timeout for DDL, and pg_blocking_pids to find the blocker
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- PostgreSQL Interview Q&A #7 - Transactions, MVCC and locks
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================
-- Sample data
CREATE TABLE accounts (id INT PRIMARY KEY, owner TEXT, balance INT);
INSERT INTO accounts VALUES (1, 'Sara', 1000), (2, 'Omar', 500), (3, 'Lina', 750);
CREATE TABLE jobs (job_id INT PRIMARY KEY, payload TEXT, status TEXT DEFAULT 'queued');
INSERT INTO jobs (job_id, payload) VALUES
(1, 'send invoice 1001'), (2, 'send invoice 1002'), (3, 'send invoice 1003'),
(4, 'send invoice 1004'), (5, 'send invoice 1005');
-- Step 1 (question 1): a update
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- Step 2 (question 1): b read [run in session B]
SELECT ctid, xmin, xmax, balance
FROM accounts
WHERE id = 1;
-- Step 3 (question 1): a read
SELECT ctid, xmin, xmax, balance
FROM accounts
WHERE id = 1;
-- Step 4 (question 2): rc first
BEGIN; -- READ COMMITTED is the default
SELECT SUM(balance) AS total FROM accounts;
-- Step 5 (question 2): rc other [run in session B]
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;
-- Step 6 (question 2): rc second
SELECT SUM(balance) AS total FROM accounts;
-- Step 7 (question 2): rr first
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) AS total FROM accounts;
-- Step 8 (question 2): rr other [run in session B]
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;
-- Step 9 (question 2): rr second
SELECT SUM(balance) AS total FROM accounts;
-- Step 10 (question 3): lu read a
SELECT balance FROM accounts WHERE id = 1;
-- Step 11 (question 3): lu read b [run in session B]
SELECT balance FROM accounts WHERE id = 1;
-- Step 12 (question 3): lu write a
UPDATE accounts SET balance = 900 WHERE id = 1;
-- Step 13 (question 3): lu write b [run in session B]
UPDATE accounts SET balance = 800 WHERE id = 1;
-- Step 14 (question 3): lu final [run in session B]
SELECT balance FROM accounts WHERE id = 1;
-- Step 15 (question 3): atomic a
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- Step 16 (question 3): atomic b [run in session B]
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
-- Step 17 (question 3): atomic final [run in session B]
SELECT balance FROM accounts WHERE id = 1;
-- Step 18 (question 4): dl a1
BEGIN;
UPDATE accounts SET balance = balance - 50
WHERE id = 1;
-- Step 19 (question 4): dl b1 [run in session B]
BEGIN;
UPDATE accounts SET balance = balance - 70
WHERE id = 2;
-- Step 20 (question 4): dl a2
UPDATE accounts SET balance = balance + 50
WHERE id = 2;
-- Step 21 (question 4): dl b2 [run in session B]
UPDATE accounts SET balance = balance + 70
WHERE id = 1;
-- Step 22 (question 5): worker a
BEGIN;
SELECT job_id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY job_id
LIMIT 2
FOR UPDATE SKIP LOCKED;
-- Step 23 (question 5): worker b [run in session B]
BEGIN;
SELECT job_id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY job_id
LIMIT 2
FOR UPDATE SKIP LOCKED;
-- Step 24 (question 6): idle tx
BEGIN;
SELECT COUNT(*) FROM accounts;
-- Step 25 (question 6): alter timeout [run in session B]
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note TEXT;
-- Step 26 (question 6): alter ok [run in session B]
ALTER TABLE accounts ADD COLUMN note TEXT;
-- Step 27 (question 7): blocker
UPDATE accounts SET balance = balance + 1
WHERE id = 3; -- inside BEGIN, no COMMIT yet
-- Step 28 (question 7): blocked [run in session B]
UPDATE accounts SET balance = 0
WHERE id = 3;
-- Step 29 (question 7): who blocks [run in session C]
SELECT pid, state, wait_event_type,
pg_blocking_pids(pid) AS blocked_by,
left(query, 34) AS query
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid()
ORDER BY pid;
Part 8: JSONB, arrays and Postgres types
The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.