Day 4: Transactions, MVCC and Locks
Goal: You see how PostgreSQL manages row versions, pick the right isolation level, find blocking sessions and create a deadlock that you then track down in the log. · Duration: approx. 90 min · Prerequisite: Day 1
Briefing
- MVCC (Multi-Version Concurrency Control): an
UPDATEdoesn’t overwrite, it creates a new row version. Each version carriesxmin(who created it) andxmax(who deleted it). - Every query sees a snapshot: which transactions had already finished. Readers don’t block writers and vice versa.
- Locking still happens: two writers on the same row, and DDL such as
ALTER TABLEagainst everything. - Today you need two terminals on
pg1, both withpsql -d tag04: “Session A” and “Session B”. A third one for side queries.
Session A Session B
BEGIN; UPDATE id=1 ----> UPDATE id=1 (waits for A)
COMMIT; ----> UPDATE 1 (continues)
Drill 1: BEGIN, COMMIT, ROLLBACK, SAVEPOINT
createdb tag04
psql -d tag04 <<'EOF'
CREATE TABLE konto (id int PRIMARY KEY, inhaber text, saldo numeric NOT NULL);
INSERT INTO konto VALUES (1, 'Anna', 100), (2, 'Ben', 100);
BEGIN;
UPDATE konto SET saldo = saldo - 30 WHERE id = 1;
SAVEPOINT vor_ben;
UPDATE konto SET saldo = saldo / 0 WHERE id = 2;
ROLLBACK TO SAVEPOINT vor_ben;
UPDATE konto SET saldo = saldo + 30 WHERE id = 2;
COMMIT;
SELECT * FROM konto ORDER BY id;
BEGIN;
DELETE FROM konto;
SELECT count(*) FROM konto;
ROLLBACK;
SELECT count(*) FROM konto;
EOFExpected (abridged):
BEGIN
UPDATE 1
SAVEPOINT
ERROR: division by zero
ROLLBACK
UPDATE 1
COMMIT
id | inhaber | saldo
----+---------+-------
1 | Anna | 70
2 | Ben | 130
BEGIN
DELETE 2
count
-------
0
ROLLBACK
count
-------
2
Without the savepoint, the error would have aborted the entire
transaction (current transaction is aborted, Day 1).
ROLLBACK TO SAVEPOINT undoes only the part since the
savepoint; the debit on Anna’s account stays. Without
BEGIN, every statement is its own transaction
(autocommit).
Save your progress? Sign up for free
Drill 2: Make xmin, xmax and ctid visible
Every table has hidden system columns. ctid is the
physical address (page, position):
psql -d tag04 <<'EOF'
SELECT ctid, xmin, xmax, * FROM konto ORDER BY id;
BEGIN;
SELECT pg_current_xact_id();
UPDATE konto SET saldo = saldo + 1 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM konto ORDER BY id;
COMMIT;
EOFExpected:
ctid | xmin | xmax | id | inhaber | saldo
-------+------+------+----+---------+-------
(0,3) | 892 | 894 | 1 | Anna | 70
(0,4) | 893 | 894 | 2 | Ben | 130
(2 rows)
BEGIN
pg_current_xact_id
--------------------
895
(1 row)
UPDATE 1
ctid | xmin | xmax | id | inhaber | saldo
-------+------+------+----+---------+-------
(0,5) | 895 | 0 | 1 | Anna | 71
(0,4) | 893 | 894 | 2 | Ben | 130
(2 rows)
COMMIT
xmax = 894 comes from the rolled-back
DELETE in Drill 1: the deletion is recorded, but
transaction 894 was aborted – so the row is still valid. The
UPDATE creates a new version at (0,5) with
xmin = 895; the old one at (0,3) is now dead
and waits for VACUUM (Day 6). Your transaction numbers will
differ.
Now with two sessions. Session A:
BEGIN;
UPDATE konto SET saldo = 0 WHERE id = 2;
SELECT pg_current_xact_id();
SELECT ctid, xmin, xmax, saldo FROM konto WHERE id = 2;Session B:
SELECT ctid, xmin, xmax, saldo FROM konto WHERE id = 2;Session A:
ROLLBACK;Expected (A, then B):
pg_current_xact_id
--------------------
896
ctid | xmin | xmax | saldo
-------+------+------+-------
(0,6) | 896 | 0 | 0
ctid | xmin | xmax | saldo
-------+------+------+-------
(0,4) | 893 | 896 | 130
Two versions of the same row at the same time: A sees its own new
one, B the old one – with xmax = 896, because A wants to
delete it but hasn’t committed yet. Nobody waits.
Save your progress? Sign up for free
Drill 3: Read Committed versus Repeatable Read
The default is READ COMMITTED: every
statement gets a new snapshot. Session
A:
BEGIN;
SELECT saldo FROM konto WHERE id = 1;Session B:
UPDATE konto SET saldo = 200 WHERE id = 1;Session A:
SELECT saldo FROM konto WHERE id = 1;
COMMIT;Expected (A): first 71, then
200 – within the same transaction.
With REPEATABLE READ, the snapshot applies to the whole
transaction. Session A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT saldo FROM konto WHERE id = 1;Session B:
UPDATE konto SET saldo = 300 WHERE id = 1;Session A:
SELECT saldo FROM konto WHERE id = 1;
UPDATE konto SET saldo = saldo + 10 WHERE id = 1;
ROLLBACK;Expected (A, abridged):
saldo
-------
200
saldo
-------
200
ERROR: could not serialize access due to concurrent update
ROLLBACK
A consistently sees 200, even though B wrote
300 long ago. If A wants to change a row that someone else
changed after the snapshot, PostgreSQL aborts instead of overwriting B’s
change. The application has to retry the transaction.
Save your progress? Sign up for free
Drill 4: Serializable and the serialization failure
Rule: at least one doctor must be on duty. Both check, see two on duty and sign off – each correct on its own (“write skew”):
psql -d tag04 -c 'CREATE TABLE dienst (arzt text PRIMARY KEY, im_dienst boolean)' \
-c "INSERT INTO dienst VALUES ('Anna', true), ('Ben', true)"Session A and then Session B, each:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM dienst WHERE im_dienst;Session A:
UPDATE dienst SET im_dienst = false WHERE arzt = 'Anna';Session B:
UPDATE dienst SET im_dienst = false WHERE arzt = 'Ben';Then COMMIT; first in A, then in
B, and SELECT * FROM dienst;.
Expected (abridged):
count
-------
2
UPDATE 1
COMMIT
arzt | im_dienst
------+-----------
Anna | f
Ben | f
Both COMMITs succeed – nobody is on duty. The
transactions change different rows, REPEATABLE READ notices
nothing. Now set everything back to true
(UPDATE dienst SET im_dienst = true;) and run the same
sequence with BEGIN ISOLATION LEVEL SERIALIZABLE; in both
sessions.
Expected (B on COMMIT):
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
SELECT * FROM dienst; then shows Anna | f
and Ben | t. SERIALIZABLE tracks who read what
and aborts a transaction if no serial order could produce the same
result. The price: more aborts, which the application has to answer with
a retry – as the HINT says.
| Level | Snapshot | Typical error |
|---|---|---|
| Read Committed | per statement | none, but non-repeatable reads |
| Repeatable Read | per transaction | could not serialize … concurrent update |
| Serializable | per transaction + check | … read/write dependencies … |
READ UNCOMMITTED exists only as a name; PostgreSQL
treats it like Read Committed.
Save your progress? Sign up for free
Drill 5: Who blocks whom – pg_locks and pg_blocking_pids
log_lock_waits is off. As a superuser you may turn it on
per session – without a reload, without disturbing anyone else.
Session A:
BEGIN;
UPDATE konto SET saldo = saldo - 10 WHERE id = 1;
SELECT pg_backend_pid();Session B:
SET log_lock_waits = on;
SELECT pg_backend_pid();
UPDATE konto SET saldo = saldo + 10 WHERE id = 1;B hangs. In the third terminal:
psql -d tag04 <<'EOF'
SELECT pid, state, wait_event, pg_blocking_pids(pid) AS blockiert_von,
left(query, 25) AS query
FROM pg_stat_activity WHERE datname = 'tag04' AND pid <> pg_backend_pid();
SELECT l.pid, l.locktype, l.relation::regclass, l.transactionid AS xid, l.mode,
l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE a.datname = 'tag04' AND l.pid <> pg_backend_pid()
ORDER BY l.pid, l.granted DESC, l.locktype;
EOFExpected:
pid | state | wait_event | blockiert_von | query
-------+---------------------+---------------+---------------+--------------------------
14984 | idle in transaction | ClientRead | {} | SELECT pg_backend_pid();
14985 | active | transactionid | {14984} | UPDATE konto SET saldo =
(2 rows)
pid | locktype | relation | xid | mode | granted
-------+---------------+------------+-----+------------------+---------
14984 | relation | konto_pkey | | RowExclusiveLock | t
14984 | relation | konto | | RowExclusiveLock | t
14984 | transactionid | | 915 | ExclusiveLock | t
14984 | virtualxid | | | ExclusiveLock | t
14985 | relation | konto_pkey | | RowExclusiveLock | t
14985 | relation | konto | | RowExclusiveLock | t
14985 | transactionid | | 916 | ExclusiveLock | t
14985 | tuple | konto | | ExclusiveLock | t
14985 | virtualxid | | | ExclusiveLock | t
14985 | transactionid | | 915 | ShareLock | f
(10 rows)
Row locks don’t appear in pg_locks; they live in the
tuple itself (as xmax). Whoever waits for a row therefore
waits for the locker’s transaction:
ShareLock on xid 915, granted = f.
pg_blocking_pids() saves you the puzzling. Now
Session A: COMMIT; – B immediately reports
UPDATE 1. The log:
sudo grep -E 'still waiting|acquired ShareLock' -A1 \
/var/log/postgresql/postgresql-18-main.log | tail -5Expected (abridged):
... [14985] ubuntu@tag04 LOG: process 14985 still waiting for ShareLock on
transaction 915 after 1000.322 ms
... [14985] ubuntu@tag04 DETAIL: Process holding the lock: 14984. Wait queue: 14985.
--
... [14985] ubuntu@tag04 LOG: process 14985 acquired ShareLock on transaction 915
after 1674.208 ms
The message appears after deadlock_timeout (default 1
s). In production, log_lock_waits = on belongs in the
configuration (Day 7).
Save your progress? Sign up for free
Drill 6: Lock queue, lock_timeout, statement_timeout
A harmless SELECT in an open transaction holds a weak
lock (AccessShareLock) until the end. Session
A:
BEGIN;
SELECT saldo FROM konto WHERE id = 1;Session B:
ALTER TABLE konto ADD COLUMN notiz text;In the third terminal, first a reader that waits in the background, then a look at the chain:
psql -d tag04 -c 'SELECT count(*) FROM konto' &
sleep 1
psql -d tag04 -c "SELECT pid, state, pg_blocking_pids(pid) AS blockiert_von,
left(query, 30) AS query FROM pg_stat_activity
WHERE datname = 'tag04' AND pid <> pg_backend_pid() ORDER BY pid"Expected:
pid | state | blockiert_von | query
-------+---------------------+---------------+--------------------------------
13486 | idle in transaction | {} | SELECT saldo FROM konto WHERE
13487 | active | {13486} | ALTER TABLE konto ADD COLUMN n
13489 | active | {13487} | SELECT count(*) FROM konto
(3 rows)
The ALTER TABLE needs an
AccessExclusiveLock and waits for A. Every new reader
queues up behind the ALTER TABLE – the
table is closed for everyone, even though only a SELECT and
a small migration are involved. Session A:
ROLLBACK; – B and the reader go through.
The remedy for migrations: don’t wait forever. In the third terminal, remove the column again while nobody holds the table:
psql -d tag04 -c 'ALTER TABLE konto DROP COLUMN notiz'Then again Session A:
BEGIN; SELECT saldo FROM konto WHERE id = 1; and in the
third terminal:
psql -d tag04 -c "SET lock_timeout = '2s'" -c 'ALTER TABLE konto ADD COLUMN notiz text'
psql -d tag04 -c "SET statement_timeout = '1s'" -c 'SELECT pg_sleep(5)'Expected:
SET
ERROR: canceling statement due to lock timeout
SET
ERROR: canceling statement due to statement timeout
lock_timeout limits only the wait for locks,
statement_timeout the total runtime. Both can be set per
session, role (ALTER ROLE … SET) or database.
Session A: ROLLBACK;
Save your progress? Sign up for free
Drill 7: Create a deadlock and find it in the log
Two transfers in opposite order. Session A:
BEGIN;
UPDATE konto SET saldo = saldo - 5 WHERE id = 1;Session B:
BEGIN;
UPDATE konto SET saldo = saldo - 5 WHERE id = 2;Session A (waits for B):
UPDATE konto SET saldo = saldo + 5 WHERE id = 2;Session B (waits for A – the circle is closed):
UPDATE konto SET saldo = saldo + 5 WHERE id = 1;Expected (after about 1 s in one of the two sessions):
ERROR: deadlock detected
DETAIL: Process 36079 waits for ShareLock on transaction 926; blocked by process
36080.
Process 36080 waits for ShareLock on transaction 925; blocked by process 36079.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,4) in relation "konto"
The other session reports UPDATE 1. Finish both with
COMMIT; – the aborted one reports ROLLBACK.
After deadlock_timeout, the waiting process checks whether
a cycle exists and aborts itself. The statements of both sides appear
only in the log:
sudo grep -A5 'deadlock detected' /var/log/postgresql/postgresql-18-main.log | tail -6
psql -d tag04 -Atc "SELECT deadlocks FROM pg_stat_database WHERE datname = 'tag04'"Expected (abridged):
... [36079] ubuntu@tag04 ERROR: deadlock detected
... [36079] ubuntu@tag04 DETAIL: Process 36079 waits for ShareLock on transaction
926; blocked by process 36080.
Process 36080 waits for ShareLock on transaction 925; blocked by process 36079.
Process 36079: UPDATE konto SET saldo = saldo + 5 WHERE id = 2;
Process 36080: UPDATE konto SET saldo = saldo + 5 WHERE id = 1;
... [36079] ubuntu@tag04 HINT: See server log for query details.
1
Deadlocks are always logged, even without
log_lock_waits. The cure lies in the application: always
lock rows in the same order (for example sorted by id).
Save your progress? Sign up for free
Drill 8: idle in transaction
An open transaction that does nothing holds locks and – with
REPEATABLE READ – an old snapshot. Session
A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT saldo FROM konto WHERE id = 1;In the third terminal:
psql -d tag04 <<'EOF'
SELECT pid, state, now() - xact_start AS dauer, backend_xmin,
left(query, 20) AS query
FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' AND datname = 'tag04';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND datname = 'tag04';
EOFExpected:
pid | state | dauer | backend_xmin | query
-------+---------------------+-----------------+--------------+----------------------
36146 | idle in transaction | 00:00:02.004769 | 927 | SELECT saldo FROM ko
(1 row)
pg_terminate_backend
----------------------
t
(1 row)
backend_xmin is the oldest transaction this session
still needs to see – as long as it’s open, VACUUM may not
remove any row version that became dead afterwards (Day 6). If A types
something now, it gets:
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
...
A timeout does this automatically. In Session A (psql reconnects):
SET idle_in_transaction_session_timeout = '2s';
BEGIN;
SELECT 1;Wait three seconds, then SELECT 2;.
Expected:
FATAL: terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
pg_cancel_backend(pid) cancels only the running
statement, pg_terminate_backend ends the whole connection
along with its transaction.
Save your progress? Sign up for free
Exercise: Speed Drills
- Show your current transaction number and your snapshot. Target time: 20 s
- Set the default isolation level for
tag04torepeatable read, check it in a new session and revert it. Target time: 45 s - Show how many deadlocks
tag04has had so far. Target time: 20 s
Solution
psql -d tag04 -c 'BEGIN' -c 'SELECT pg_current_xact_id(), pg_current_snapshot()' \
-c 'COMMIT'
psql -d tag04 -c "ALTER DATABASE tag04 SET default_transaction_isolation =
'repeatable read'"
psql -d tag04 -Atc 'SHOW default_transaction_isolation'
psql -d tag04 -c 'ALTER DATABASE tag04 RESET default_transaction_isolation'
psql -d tag04 -Atc "SELECT datname, deadlocks FROM pg_stat_database
WHERE datname = 'tag04'"Expected (abridged):
pg_current_xact_id | pg_current_snapshot
--------------------+---------------------
917 | 917:917:
ALTER DATABASE
repeatable read
ALTER DATABASE
tag04|1
The snapshot 917:917: means: everything below 917 is
finished, from 917 on invisible, no exceptions in between.
pg_current_xact_id() assigns the number only when needed –
without BEGIN, each call would have used up its own.
Save your progress? Sign up for free
Checkpoint
psql -d tag04 -Atc 'SELECT id, saldo FROM konto ORDER BY id'Expected: 1|305 and 2|125
– or 1|295 and 2|135 if the deadlock in Drill
7 hit Session B.
psql -d tag04 -Atc "SELECT count(*) FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%' AND datname = 'tag04'"Expected: 0 – no session is hanging
anymore.
psql -d tag04 -Atc "SELECT deadlocks > 0 FROM pg_stat_database
WHERE datname = 'tag04'"Expected: t
psql -d tag04 -Atc 'SHOW log_lock_waits'Expected: off – the setting from Drill
5 applied only to Session B.
Challenge
Build a lock chain with three sessions: A changes row 2 in an open transaction, B and C want to update or delete the same row. With one query, find the session at the head of the chain and terminate only that one.
Hint: You’re looking for a PID that appears in other
sessions’ pg_blocking_pids() but is itself blocked by
nobody.
Solution
A:
BEGIN; UPDATE konto SET saldo = saldo + 1 WHERE id = 2; ·
B: UPDATE konto SET saldo = saldo + 1 WHERE id = 2; · C:
DELETE FROM konto WHERE id = 2;
psql -d tag04 <<'EOF'
SELECT pid, pg_blocking_pids(pid) AS blockiert_von, left(query, 30) AS query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
AND cardinality(pg_blocking_pids(pid)) = 0;
EOFExpected:
pid | blockiert_von | query
-------+---------------+--------------------------------
15587 | {15586} | UPDATE konto SET saldo = saldo
15588 | {15587} | DELETE FROM konto WHERE id = 2
(2 rows)
pg_terminate_backend
----------------------
t
(1 row)
C waits for B, not for A: whoever waits for a row first is at the
front of the queue. After A is terminated, B (UPDATE 1) and
C (DELETE 1) go through – afterwards row 2 no longer
exists, so the checkpoint above applies before the challenge.
Save your progress? Sign up for free
Cleanup
Leave all sessions with \q, then:
dropdb tag04Interview Note
- “Do readers block writers in PostgreSQL?” – No, MVCC: every reader sees its snapshot, writers create new versions. Only writers on the same row and DDL block.
- “A migration brought production to a halt even though
ALTER TABLEis fast – why?” – It waited for a long transaction, and everyone else was queued behind it in the lock queue. Remedy:lock_timeoutand retry. - “What do you do about serialization failures?” – Expected behavior
with
REPEATABLE READandSERIALIZABLE; the application retries the whole transaction (SQLSTATE40001, for deadlocks40P01).
Enterprise Note
log_lock_waits = onandidle_in_transaction_session_timeout(e.g. 5–15 min) globally; stricter values per application role.- Migration tools set
lock_timeout(a few seconds) and retry; large changes run in small steps (NOT VALID,CONCURRENTLY). statement_timeoutper role for web applications, not globally – otherwise maintenance jobs and backups get aborted.- Alerts on
pg_stat_activity: oldest open transaction, number of waiting sessions,pg_stat_database.deadlocks(Day 7). SERIALIZABLEonly with retry logic in the driver or framework – and with tests under load.