Skip to content
Hands-on IT IT Drills

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 UPDATE doesn’t overwrite, it creates a new row version. Each version carries xmin (who created it) and xmax (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 TABLE against everything.
  • Today you need two terminals on pg1, both with psql -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;
EOF

Expected (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;
EOF

Expected:

 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;
EOF

Expected:

  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 -5

Expected (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';
EOF

Expected:

  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

  1. Show your current transaction number and your snapshot. Target time: 20 s
  2. Set the default isolation level for tag04 to repeatable read, check it in a new session and revert it. Target time: 45 s
  3. Show how many deadlocks tag04 has 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;
EOF

Expected:

  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 tag04

Interview 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 TABLE is fast – why?” – It waited for a long transaction, and everyone else was queued behind it in the lock queue. Remedy: lock_timeout and retry.
  • “What do you do about serialization failures?” – Expected behavior with REPEATABLE READ and SERIALIZABLE; the application retries the whole transaction (SQLSTATE 40001, for deadlocks 40P01).

Enterprise Note

  • log_lock_waits = on and idle_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_timeout per 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).
  • SERIALIZABLE only with retry logic in the driver or framework – and with tests under load.