Lock Monitoring

From PostgreSQL Wiki

• Looking at pg_locks shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems:

 select relname,pg_locks.* from pg_class,pg_locks where relfilenode=relation and not granted;

• Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in pg_stat_activity
• The following query may be helpful to see what processes are blocking SQL statements:

 select bl.pid as blocked_pid, a.usename as blocked_user, 
        kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement
 from pg_catalog.pg_locks bl
      join pg_catalog.pg_stat_activity a
      on bl.pid = a.procpid
      join pg_catalog.pg_locks kl
           join pg_catalog.pg_stat_activity ka
           on kl.pid = ka.procpid
      on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 where not bl.granted;