Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-lock~40 min

A schema migration made the whole application stop, and the table it locked was not busy

Reported symptoms

  • ●At 14:06 the application begins timing out on every request that touches the customers table, and the error rate goes to one hundred per cent for those endpoints in under twenty seconds
  • ●Endpoints that do not touch customers are unaffected, which initially suggests an application-level fault in one service
  • ●The database reports normal CPU, normal I/O, and no increase in connection count beyond the queries that are now waiting
  • ●pg_stat_activity shows forty-one sessions in state active with wait_event_type Lock, all of them ordinary SELECT statements against customers
  • ●The team identifies the oldest waiting SELECT and terminates it; another takes its place immediately and the outage continues
  • ●A deployment of a schema migration is running in a separate pipeline, and its ALTER TABLE has been executing for four minutes with no output
  • ●At 14:19 somebody terminates the migration, the queue drains in under a second, and the application recovers before anybody agrees on what happened

Evidence

  • · pg_locks on the customers relation shows one granted AccessShareLock, one ungranted AccessExclusiveLock, and thirty-nine ungranted AccessShareLock rows
  • · The single granted lock belongs to a session whose query is a reporting SELECT started at 14:02 and whose state is idle in transaction
  • · pg_blocking_pids reports the ALTER TABLE blocked by the reporting session, and every waiting SELECT blocked by the ALTER TABLE rather than by the reporting session
  • · The log records process N still waiting for AccessExclusiveLock on relation ... after 1000.114 ms with DETAIL naming the holder and a Wait queue that lengthens on each subsequent line
  • · The reporting session has been idle in transaction since 14:02:41, having completed its SELECT and never committed
  • · The migration statement is ALTER TABLE customers ADD COLUMN loyalty_tier text, which the deployment pipeline runs with no lock_timeout set
  • · The customers table received 14 writes in the hour before the incident, so it was not under write pressure of any kind
  • · log_lock_waits was off on this cluster, so the wait queue lines quoted above were only available after it was enabled during the incident
Diagnosis and resolutionclick to reveal

Root cause

A reporting query held an `AccessShareLock` on `customers` inside a transaction it never closed. A migration then requested `AccessExclusiveLock` on the same table and joined the queue behind it. Every query arriving afterwards joined the queue behind the migration. The mechanism is the lock manager's queueing rule. A lock request that conflicts with a request already waiting does not jump ahead of it, even if it would be compatible with everything currently held. `AccessShareLock` does not conflict with `AccessShareLock`, so the forty-one waiting `SELECT` statements could all have been granted alongside the reporting query. They were not, because `AccessExclusiveLock` was ahead of them in the queue and conflicts with both. Without that rule, an exclusive request on a busy table would be starved indefinitely: a steady trickle of overlapping readers means there is never an instant with no readers, and the `ALTER` would wait forever. The rule is correct and it is the reason a single waiting migration converts into a total stop on that table. So the table was not busy and nothing was contending for it. One transaction that had finished its work four minutes earlier and not committed, plus one migration that was willing to wait indefinitely, was sufficient. Two things made it worse than it needed to be. The migration ran with no `lock_timeout`, so it was prepared to hold the head of that queue for as long as the reporting transaction lasted. And `log_lock_waits` was off, so the server's own running commentary on the queue — which names the holder and lists the waiters in order — did not exist until somebody enabled it thirteen minutes in.

Remediation

Identify the head of the chain, not the sessions that are complaining. Two conditions define it: **blocked by nobody, and blocking somebody**. ```sql SELECT a.pid, a.usename, a.state, now() - a.xact_start AS xact_age, left(a.query, 60) AS query, (SELECT count(*) FROM pg_stat_activity b WHERE a.pid = ANY(pg_blocking_pids(b.pid))) AS blocking_count FROM pg_stat_activity a WHERE cardinality(pg_blocking_pids(a.pid)) = 0 AND EXISTS (SELECT 1 FROM pg_stat_activity b WHERE a.pid = ANY(pg_blocking_pids(b.pid))) ORDER BY xact_age DESC; ``` On a chain like this that returns one row out of forty-two blocked sessions, and it will not be one of the sessions anybody has reported. Decide which of two things to end. Terminating the **migration** releases the forty waiting readers immediately and abandons the deployment, which is usually the right first move because it restores service without discarding anybody's data. Terminating the **reporting session** lets the migration acquire its lock, complete, and then release everything — which is right only if the migration is short and you are certain of it. Terminating individual waiting `SELECT` statements achieves nothing. They are victims, another arrives immediately, and each one killed is a user-visible error for no benefit. Once service is restored, enable `log_lock_waits` — it should not have been off — and re-run the migration with a `lock_timeout`: ```sql SET lock_timeout = '3s'; ALTER TABLE customers ADD COLUMN loyalty_tier text; ``` Retry on failure. Most retries succeed within a minute or two, because the transactions that block them are transient.

Verification

`SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock'` returns zero. `pg_locks` for the affected relation shows no ungranted rows. Application error rates return to baseline, and the endpoints that were failing respond within their normal latency band. The migration, when re-run with `lock_timeout`, either completes in milliseconds or fails fast with `canceling statement due to lock timeout` — and in the failing case, `pg_stat_activity` shows no queue formed behind it. `SHOW log_lock_waits` returns `on`, and a deliberately created wait produces the expected `still waiting for` and `Wait queue:` lines in the log.

Prevention

**Set `lock_timeout` on every statement that takes AccessExclusiveLock.** `ALTER TABLE`, `CREATE INDEX` without `CONCURRENTLY`, `REINDEX` without `CONCURRENTLY`, `VACUUM FULL`, `TRUNCATE`, `DROP`. A few seconds, with a retry. This converts a total outage into a failed deployment that retries and succeeds. Enforce it in the migration tooling rather than relying on each author to remember, and treat a migration file that overrides it as a change requiring review. **Enable `log_lock_waits`.** It costs one log line per wait exceeding `deadlock_timeout` and it produces the holder, the queue in order, and the statement. Without it, a lock incident leaves no evidence at all once the sessions end. **Set `idle_in_transaction_session_timeout`.** The reporting session had finished its query four minutes before the migration arrived. A timeout in the minutes would have released the lock before there was anything to block. **Alert on `pg_stat_activity` sessions with `wait_event_type = 'Lock'` and a wait longer than a few seconds.** This fires in seconds, where an application error-rate alert fires after users have noticed. **Put the root-of-chain query in the runbook.** The instinct during a lock incident is to kill the sessions that are complaining, and that instinct is precisely wrong.

Reported symptoms

At 14:06 every request touching the customers table starts timing out. The error rate on those endpoints reaches 100% in under twenty seconds. Endpoints that do not touch customers are fine, which initially points everybody at one application service.

The database looks healthy: normal CPU, normal I/O, no connection spike beyond the queries now piling up. pg_stat_activity shows forty-one sessions active with wait_event_type = Lock, all of them ordinary SELECT statements against customers.

The team terminates the oldest waiting SELECT. Another takes its place immediately.

A schema migration is running in a separate pipeline. Its ALTER TABLE has been executing for four minutes with no output. At 14:19 somebody terminates it, the queue drains in under a second, and the application recovers before anyone has agreed on what happened.

Evidence provided

Read-only / Safeone granted lock, forty-one waiting
$ psql -c "SELECT pid, mode, granted FROM pg_locks WHERE relation='customers'::regclass AND locktype='relation' ORDER BY granted DESC, pid;"
  pid  |        mode         | granted 
-------+---------------------+---------
22104 | AccessShareLock     | t
22887 | AccessExclusiveLock | f
22903 | AccessShareLock     | f
22904 | AccessShareLock     | f
22906 | AccessShareLock     | f
 ... 36 further AccessShareLock rows, all granted = f

Illustrative output

Read-only / Safethe chain, and it does not point where the reports point
$ psql -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query,40) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0 ORDER BY pid;"
  pid  | blocked_by |                  query                   
-------+------------+------------------------------------------
22887 | {22104}    | ALTER TABLE customers ADD COLUMN loyalt
22903 | {22887}    | SELECT id, email FROM customers WHERE i
22904 | {22887}    | SELECT count(*) FROM customers WHERE cr
22906 | {22887}    | SELECT id, email FROM customers WHERE i
 ... 37 further rows, all blocked_by {22887}

Illustrative output

Session 22104 is idle in transaction, having completed a reporting SELECT at 14:02:41 and never committed. The customers table took 14 writes in the preceding hour.

log_lock_waits was off. After it was enabled during the incident:

Read-only / Safethe queue, growing, in the server's own words
$ grep -E 'still waiting|Wait queue' /var/log/postgresql/postgresql-18-main.log | tail -3
2026-08-28 14:19:02.118 UTC [22887] LOG:  process 22887 still waiting for AccessExclusiveLock on relation 41209 of database 16401 after 1000.114 ms
2026-08-28 14:19:02.118 UTC [22887] DETAIL:  Process holding the lock: 22104. Wait queue: 22887.
2026-08-28 14:19:02.118 UTC [22887] STATEMENT:  ALTER TABLE customers ADD COLUMN loyalty_tier text;

Illustrative output

Work the evidence before reading on

  1. Forty-one SELECT statements are blocked. SELECT does not conflict with SELECT. What is blocking them?
  2. pg_blocking_pids says the readers are blocked by 22887, not by 22104. Which one do you act on?
  3. The table took 14 writes in an hour. Does that make a lock incident more or less likely, or neither?
  4. Terminating the oldest waiting SELECT did nothing. Why was that predictable?

Root cause

The queue, not the lock

AccessShareLock — what a plain SELECT takes — does not conflict with AccessShareLock. Session 22903 could have been granted its lock immediately alongside 22104. It was not.

The lock manager does not allow a request to jump ahead of an incompatible request that is already waiting. Session 22887 asked for AccessExclusiveLock first. Everything arriving afterwards queues behind it, whether or not it conflicts with what is currently held.

The victims are not the cause

pg_blocking_pids reports the readers as blocked by 22887, the migration, not by 22104, the reporting session. That is accurate: the proximate blocker is the migration.

But the root of the chain is 22104, and it is the only session that is blocking somebody while being blocked by nobody. That pair of conditions is what identifies it:

SELECT a.pid, a.usename, a.state, now() - a.xact_start AS xact_age,
       left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) = 0
  AND EXISTS (SELECT 1 FROM pg_stat_activity b
              WHERE a.pid = ANY(pg_blocking_pids(b.pid)))
ORDER BY xact_age DESC;

On this incident that returns one row out of forty-two blocked sessions, and it is a session nobody had reported, running nothing, that had finished its work four minutes earlier.

Two things made it worse than necessary

The migration ran with no lock_timeout. It was therefore prepared to hold the head of that queue for as long as the reporting transaction lasted, which had no upper bound.

And log_lock_waits was off, so for the first thirteen minutes the server’s own commentary — which names the holder, lists the queue in order, and quotes the statement — did not exist.

Resolution

Restore service first. Terminating the migration releases the forty readers immediately and costs only a deployment that can be retried:

SELECT pg_terminate_backend(22887);

Terminating the reporting session instead lets the migration proceed, complete, and release everything. That is right only when you know the migration is fast — and ADD COLUMN with no default is, in PostgreSQL 11 and later, a catalog-only change that takes milliseconds once it has its lock.

Either is defensible. Terminating individual waiting readers is not.

Then fix the two contributing conditions:

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

And re-run the migration properly:

SET lock_timeout = '3s';
ALTER TABLE customers ADD COLUMN loyalty_tier text;

Verification

SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock' returns zero.

pg_locks for customers has no granted = f rows.

Application error rates return to baseline and latency returns to its normal band.

The re-run migration either completes in milliseconds or fails fast with canceling statement due to lock timeout, and in the failing case no queue forms.

SHOW log_lock_waits returns on, and a deliberately created wait produces the still waiting for and Wait queue: lines.

Prevention

lock_timeout on every statement that takes AccessExclusiveLock, enforced in the migration tooling. This is the single change that prevents the class, and enforcing it centrally means it does not depend on each migration’s author.

log_lock_waits = on. One log line per wait longer than deadlock_timeout, and it is the only evidence that survives the sessions ending.

idle_in_transaction_session_timeout. The reporting session had been idle in transaction for four minutes before the migration arrived. A timeout in the minutes removes the holder before there is anything to hold up.

Alert on lock waits, not on application errors. A session waiting on wait_event_type = 'Lock' for more than a few seconds is detectable in seconds; a 100% error rate is detectable after users have noticed.

Put the root-of-chain query in the runbook, because the natural instinct in a lock incident is to kill what is complaining, and that is exactly backwards.