Lock Monitoring

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Query < and >= 9.2 to reflect query vs current_query and pid vs procpid.)
Line 40: Line 40:
  
 
* Here's almost quite the same thing but with some more details:
 
* Here's almost quite the same thing but with some more details:
 
+
-- For PostgreSQL Version < 9.2
 
   select bl.pid as blocked_pid, a.usename as blocked_user,
 
   select bl.pid as blocked_pid, a.usename as blocked_user,
 
         ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration,
 
         ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration,
Line 53: Line 53:
 
       on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 
       on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 
   where not bl.granted;
 
   where not bl.granted;
 +
 +
--For PostgreSQL >= 9.2
 +
select bl.pid as blocked_pid, a.usename as blocked_user,
 +
        ka.query as blocking_statement, now() - ka.query_start as blocking_duration,
 +
        kl.pid as blocking_pid, ka.usename as blocking_user, a.query as blocked_statement,
 +
        now() - a.query_start as blocked_duration
 +
from pg_catalog.pg_locks bl
 +
      join pg_catalog.pg_stat_activity a
 +
      on bl.pid = a.pid
 +
      join pg_catalog.pg_locks kl
 +
          join pg_catalog.pg_stat_activity ka
 +
          on kl.pid = ka.pid
 +
      on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 +
  
 
* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful.  Then slow lock acquisition will appear in the database logs for later analysis.
 
* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful.  Then slow lock acquisition will appear in the database logs for later analysis.

Revision as of 19:03, 29 July 2013

  • 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 relation::regclass, * from pg_locks where 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 (these only find row-level locks, not object-level locks):
-- For PostgreSQL Version < 9.2
  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;


-- For PostgreSQL Version >= 9.2
  SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, 
          kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement
   FROM pg_catalog.pg_locks bl
        JOIN pg_catalog.pg_stat_activity a
        ON bl.pid = a.pid
        JOIN pg_catalog.pg_locks kl
             JOIN pg_catalog.pg_stat_activity ka
             ON kl.pid = ka.pid
        ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
   WHERE NOT bl.granted;
  • Here's an alternate view of that same data that includes an idea how old the state is:
   select 
     pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
     pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, 
     age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid 
   from pg_stat_activity,pg_locks left 
     outer join pg_class on (pg_locks.relation = pg_class.oid)  
   where pg_locks.pid=pg_stat_activity.procpid order by query_start;
  • Here's almost quite the same thing but with some more details:

-- For PostgreSQL Version < 9.2

  select bl.pid as blocked_pid, a.usename as blocked_user,
        ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration,
        kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement,
        now() - a.query_start as blocked_duration
 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;

--For PostgreSQL >= 9.2

select bl.pid as blocked_pid, a.usename as blocked_user,
       ka.query as blocking_statement, now() - ka.query_start as blocking_duration,
       kl.pid as blocking_pid, ka.usename as blocking_user, a.query as blocked_statement,
       now() - a.query_start as blocked_duration
from pg_catalog.pg_locks bl
     join pg_catalog.pg_stat_activity a
     on bl.pid = a.pid
     join pg_catalog.pg_locks kl
          join pg_catalog.pg_stat_activity ka
          on kl.pid = ka.pid
     on bl.transactionid = kl.transactionid and bl.pid != kl.pid


  • If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the log_lock_waits and related deadlock_timeout parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.

See also Lock dependency information

Personal tools