Transactions, MVCC and locks

Why this lesson exists

The most confusing PostgreSQL outage looks like this: CPU idle, disk idle, no errors - and every request times out. Nothing is slow; everything is waiting. One transaction holds a lock, and a queue of sessions waits behind it. The cure takes ten seconds once you can read pg_stat_activity and pg_locks: find the session at the root of the chain and end it. This lesson is how transactions, row versions and locks work, so the chain makes sense, and the exact queries to find and break it.

What you need to know already: pg_stat_activity (lesson 1), psql and the open-transaction trap (lesson 3), roles (lesson 4). Processes and signals from the Linux chapters.

The words you need first

• Transaction - a group of statements that commit together or not at all. Outside an explicit BEGIN every statement is its own transaction.
• MVCC (multi-version concurrency control) - UPDATE and DELETE do not overwrite a row: they write a new row version (tuple) and mark the old one as ended. Each transaction sees the versions that were committed when its snapshot was taken.
• xmin / xmax - hidden columns on every row version: the transaction ID that created it and the one that ended it.
• Dead tuple - a row version no running transaction can see any more. Space that only VACUUM gets back (next lesson).
• Lock - a claim on a table, a row or a transaction ID. Table locks have 8 modes that conflict in a fixed table; row locks are stored on the row itself.
• Blocking - a session waiting for a lock that another session holds. pg_blocking_pids(pid) lists who it waits for.
• Idle in transaction - a session that opened a transaction, did something, and is now waiting for its client - holding every lock it took.

Row versions

Look at the hidden columns of one order, update it, and look again:

$ sudo -u postgres psql -d orders -c "select ctid, xmin, xmax, id, status from orders where id = 7"
 ctid  | xmin | xmax | id |  status
-------+------+------+----+----------
 (0,7) |  766 |  767 |  7 | refunded
(1 row)
$ sudo -u postgres psql -d orders -c "update orders set status = 'shipped' where id = 7"
UPDATE 1
$ sudo -u postgres psql -d orders -c "select ctid, xmin, xmax, id, status from orders where id = 7"
   ctid   | xmin | xmax | id | status
----------+------+------+----+---------
 (370,40) |  823 |    0 |  7 | shipped
(1 row)

The row moved: a new ctid (page, item - its physical place) and a new xmin (the transaction that wrote it). The old version is still in the table, marked dead - nobody can see it, but it takes space until VACUUM cleans it. Every UPDATE in PostgreSQL is an insert plus a delete; a table updated a million times a day writes a million dead tuples a day.

What MVCC buys you: readers never block writers and writers never block readers. A long report (SELECT) does not stop the app from updating, and an update does not make the report wait - each sees its own consistent snapshot. The price is the dead tuples, and the rule that a dead tuple can only be removed when no running transaction could still need it. One transaction left open for hours keeps every tuple that died since it started. That is the link to the next lesson.

Isolation levels

(READ UNCOMMITTED exists in the syntax but behaves as READ COMMITTED: PostgreSQL never shows uncommitted data.)

Locks

Every statement takes a lock on each table it touches - most of them so weak you never notice:

Two rows of that table cause most lock incidents: ACCESS EXCLUSIVE (a migration's ALTER TABLE stops even reads) and SHARE (a plain CREATE INDEX stops all writes for as long as it builds). Lesson 10 is the safe way to run both.

Row locks are separate: UPDATE, DELETE and SELECT ... FOR UPDATE lock the rows they touch until the transaction ends. Two transactions updating the same row: the second waits for the first to commit or roll back. Different rows: no waiting at all.

Every lock is held until the transaction ends - not until the statement ends. That is why a transaction that is open but doing nothing is the classic root of a chain.

A blocking chain, live

A session in the background opens a transaction, updates order 42 and then waits for its client (the way an app does when it calls another service in the middle of a transaction):

Now the app tries to update the same order, with a 5 second statement timeout:

$ sudo -u postgres psql -d orders -c "set statement_timeout = '5s'" -c "update orders set status = 'shipped' where id = 42"
SET
ERROR:  canceling statement due to statement timeout
CONTEXT:  while updating tuple (0,3) in relation "orders"

It waited the full 5 seconds and was cancelled by its own statement_timeout - to the app this is a timeout, not a database error; nothing in the server was wrong. Start two more in the background so there is something to look at:

$ sudo -u postgres psql -c "select pid, usename, state, wait_event_type, wait_event, now() - xact_start as xact_age, left(query, 50) as query from pg_stat_activity where datname = 'orders' order by xact_start"
  pid  |  usename   |        state        | wait_event_type |  wait_event   |  xact_age  |                       query
-------+------------+---------------------+-----------------+---------------+------------+----------------------------------------------------
 17418 | orders_app | idle in transaction | Client          | ClientRead    | 00:00:49.1 | UPDATE orders SET status = 'paid', updated_at = no
 17428 | postgres   | active              | Lock            | transactionid |            | update orders set status = 'refunded' where id = 4
 17431 | postgres   | active              | Lock            | tuple         |            | select count(*) from orders where id = 42 for upda
(3 rows)

Read it top to bottom:

• orders_app, idle in transaction, transaction open for 40+ seconds, last query the UPDATE - it holds the row lock and is waiting for its client (wait_event_type = Client, ClientRead).
• two sessions active with wait_event_type = Lock. The first waits on transactionid: a row lock is waited on through the transaction ID of the holder. The second waits on tuple: waiters for the same row queue up behind each other on a lock for that row.

pg_blocking_pids() says who waits for whom directly:

$ sudo -u postgres psql -c "select pid, pg_blocking_pids(pid) as blocked_by, wait_event, left(query, 45) as query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0"
  pid  | blocked_by |  wait_event   |                     query
-------+------------+---------------+-----------------------------------------------
 17428 | {17418}    | transactionid | update orders set status = 'refunded' where i
 17431 | {17428}    | tuple         | select count(*) from orders where id = 42 for
(2 rows)

A chain of three: the second waiter is blocked by the first, the first by the idle in transaction session. The root is the PID that appears in blocked_by but is not blocked itself - one query finds it in any chain:

select distinct b from pg_stat_activity, unnest(pg_blocking_pids(pid)) as b
where cardinality(pg_blocking_pids(b)) = 0;

shows the raw locks - granted = f is a waiter:

$ sudo -u postgres psql -c "select l.pid, l.locktype, l.mode, l.granted, c.relname from pg_locks l left join pg_class c on c.oid = l.relation where l.pid in (select pid from pg_stat_activity where datname = 'orders') and l.locktype in ('relation', 'transactionid', 'tuple') order by l.granted desc, l.pid"
  pid  |   locktype    |       mode       | granted |   relname
-------+---------------+------------------+---------+-------------
 17418 | relation      | RowExclusiveLock | t       | orders
 17418 | relation      | RowExclusiveLock | t       | orders_pkey
 17418 | transactionid | ExclusiveLock    | t       |
 17428 | tuple         | ExclusiveLock    | t       | orders
 17428 | relation      | RowExclusiveLock | t       | orders
 17428 | relation      | RowExclusiveLock | t       | orders_pkey
 17431 | relation      | RowShareLock     | t       | orders
 17431 | relation      | RowShareLock     | t       | orders_pkey
 17428 | transactionid | ShareLock        | f       |
 17431 | tuple         | ExclusiveLock    | f       | orders
(10 rows)

Breaking the chain

Two functions, both safe, both need superuser or membership of pg_signal_backend (and non-superusers cannot touch superuser sessions):

The root here is idle - there is no query to cancel - so it has to be terminated:

$ sudo -u postgres psql -Atc "select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle in transaction' and usename = 'orders_app'"
t
$ sudo -u postgres psql -c "select pid, state, wait_event_type, left(query, 50) as query from pg_stat_activity where datname = 'orders'"
 pid | state | wait_event_type | query
-----+-------+-----------------+-------
(0 rows)
$ sudo -u postgres psql -d orders -Atc "select status from orders where id = 42"
refunded

The waiters got their locks the moment the root's transaction rolled back, ran, and finished. (Which update won? The queue is first come, first served - and the root's own change was rolled back.) Before you terminate anything in production, save what you saw (the query, the user, the client address, how long it was open) - the team that owns the app needs it to fix the real bug.

Never kill -9 a backend from the shell: the postmaster takes a backend dying from SIGKILL as possible shared-memory corruption and restarts every connection. kill <pid> (SIGTERM) is the same as pg_terminate_backend, but the SQL function checks permissions and is what your runbook should say.

Timeouts: stop it happening again

Set them per role or database rather than for the whole server, so maintenance jobs and pg_dump are not caught:

$ sudo -u postgres psql -c "alter role orders_app set idle_in_transaction_session_timeout = '60s'"
ALTER ROLE
$ sudo -u postgres psql -c "select rolname, rolconfig from pg_roles where rolname = 'orders_app'"
  rolname   |                 rolconfig
------------+-------------------------------------------
 orders_app | {idle_in_transaction_session_timeout=60s}
(1 row)

ALTER ROLE ... SET applies to new sessions of that role. The server logs the ones it ends: FATAL: terminating connection due to idle-in-transaction timeout.

And make waits visible: with log_lock_waits = on the server logs every lock wait longer than deadlock_timeout (1 second) with the PIDs that hold the lock and the queue:

$ sudo -u postgres psql -Atc "show log_lock_waits" -c "show deadlock_timeout"
off
1s

Deadlocks

Two transactions that each hold a lock the other wants can never both finish. PostgreSQL checks for this after a session has waited deadlock_timeout and kills one of them. Two transactions updating the same two orders in opposite order:

$ sudo -u postgres psql -d orders -c "begin" -c "update orders set status = 'paid' where id = 1" -c "select pg_sleep(2)" -c "update orders set status = 'paid' where id = 2" -c "commit" > /tmp/a.out 2>&1 &
[1] 17450
$ sudo -u postgres psql -d orders -c "begin" -c "update orders set status = 'paid' where id = 2" -c "select pg_sleep(2)" -c "update orders set status = 'paid' where id = 1" -c "commit" > /tmp/b.out 2>&1 &
[2] 17453
$ cat /tmp/a.out /tmp/b.out
$ sudo grep -A 4 'deadlock detected' /var/log/postgresql/postgresql-18-main.log | head -n 5

One transaction got ERROR: deadlock detected and rolled back; the other carried on and committed. A deadlock is not an outage - it is a bug in the order the app takes locks. The fix is in the code: always lock rows in the same order (for example by primary key), keep transactions short, and retry on SQLSTATE 40P01.

In an interview: "Requests time out but the database CPU is idle - what do you check?" - locks: pg_stat_activity for sessions active with wait_event_type = Lock and for idle in transaction sessions with old xact_start; pg_blocking_pids(pid) to walk the chain to its root; terminate the root with pg_terminate_backend (cancel will not help an idle session); then prevent it with idle_in_transaction_session_timeout and lock_timeout.

What you can do now

• Explain MVCC: row versions, xmin/xmax, dead tuples, why readers and writers do not block each other.
• Name the lock modes that matter (ACCESS EXCLUSIVE, SHARE, ROW EXCLUSIVE) and what takes them.
• Find a blocking chain with pg_stat_activity, pg_blocking_pids and pg_locks, and break it at the root.
• Choose between pg_cancel_backend and pg_terminate_backend, and set the timeouts that stop it recurring.
• Read a deadlock error and say what the application has to change.