Sperrwartezustände im Detail nach Objekt und Sperrmodus untersuchen (pg_locks)

Befehl

SELECT DISTINCT
       w.pid AS waiting_pid,
       w.locktype,
       coalesce(w.relation::regclass::text, 'xid ' || w.transactionid, w.virtualxid, w.objid::text) AS target,
       w.mode AS wanted_mode,
       b.pid AS blocker_pid,
       b.mode AS blocker_mode,
       b.granted AS blocker_holds,
       now() - wa.state_change AS waiting_for,
       left(wa.query, 60) AS waiting_query,
       left(ba.query, 60) AS blocker_query
FROM pg_locks w
JOIN pg_locks b
  ON b.pid = ANY (pg_blocking_pids(w.pid))
 AND b.locktype = w.locktype
 AND b.database IS NOT DISTINCT FROM w.database
 AND b.relation IS NOT DISTINCT FROM w.relation
 AND b.page IS NOT DISTINCT FROM w.page
 AND b.tuple IS NOT DISTINCT FROM w.tuple
 AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid
 AND b.transactionid IS NOT DISTINCT FROM w.transactionid
 AND b.classid IS NOT DISTINCT FROM w.classid
 AND b.objid IS NOT DISTINCT FROM w.objid
 AND b.objsubid IS NOT DISTINCT FROM w.objsubid
JOIN pg_stat_activity wa ON wa.pid = w.pid
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT w.granted
ORDER BY waiting_for DESC, waiting_pid;

Was es tut

Zeigt für jede nicht gewährte Sperranforderung (granted = false) das Zielobjekt (Tabelle, Transaction ID usw.), den angeforderten Modus, die blockierende PID und deren Modus, ob der Blocker die Sperre tatsächlich hält, und die ungefähre Wartezeit.

Wann einsetzen

Um zu verstehen, welcher Konflikt aus Tabelle und Sperrmodus die Blockierung verursacht; z. B. eine DDL, die auf AccessExclusiveLock wartet, oder eine Zeilensperre, die als Wait auf transactionid erscheint.

Vorsicht

Da der Abgleich pg_blocking_pids() nutzt, werden nur tatsächlich kollidierende Anforderungen gelistet. blocker_holds = false bedeutet, dass der Blocker die Sperre noch nicht hält, aber mit einem kollidierenden Modus weiter vorn in der Warteschlange steht (Lock Queue). locktype = 'transactionid' ist ein Konflikt auf Zeilenebene: Der Wartende wartet, bis die Transaktion endet, die die Zeile geändert hat; die Zeile selbst ist in pg_locks nicht sichtbar. pg_locks kann auf großen Systemen Tausende Zeilen enthalten; nicht häufig ausführen. waiting_for ist ein Näherungswert auf Basis von state_change.