Transaction lock waits

Locking performance issues will often be evident by an excess of clients that are waiting for a lock to be granted. If you join two pg_locks entries together with a matching pair of pg_stat_activity ones, it's possible to find out various information about both the locker process that currently holds the lock, and the locked one stuck waiting for it:

    SELECT
      locked.pid AS locked_pid,
      locker.pid AS locker_pid,
      locked_act.usename AS locked_user,
      locker_act.usename AS locker_user,
      locked.virtualtransaction,
      locked.transactionid,
      locked.locktype
    FROM
      pg_locks locked,
      pg_locks locker,
      pg_stat_activity locked_act,
      pg_stat_activity locker_act
    WHERE
      locker.granted=true AND
      locked.granted=false AND
     locked.pid=locked_act.procpid ...

Get PostgreSQL 10 High Performance now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.