PostgreSQL 10 High Performance - Third Edition

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