Locks - How to detect blocking and waiting queries on Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

When queries are blocked from running due to waiting on locks on resources, it can be difficult to determine which queries are holding the locks and blocking other queries from running. The queries below will help in finding the queries and processes that are blocking.

Environment

Product Version: 5.26

Resolution

Overall count of blocked queries

List a count of the number of queries/sessions waiting on each relation or resource queue:

SELECT db.datname AS db_name,
       l.locktype AS locktype,
       ns.nspname || '.' || cl.relname AS relname,
       rq.rsqname AS res_queue,
       count(*) AS num_waiters
FROM ( select * FROM pg_locks WHERE granted='f') l
LEFT JOIN pg_database db ON ( db.oid = l.database )
LEFT JOIN pg_class cl ON ( cl.oid = l.relation )
LEFT JOIN pg_namespace ns ON ( ns.oid = cl.relnamespace )
LEFT JOIN pg_resqueue rq ON ( rq.oid = l.objid )
GROUP BY 1, 2, 3, 4; 

 

 

Information on locktype = relation

List the waiting queries and the query that is blocking each one:
Note: The query is slightly different for versions 6.x and above 
Note: To detect possible orphan queries or processes, take note of the rows where the "gp_segment_id" is not "-1"

SELECT l1.gp_segment_id,
       l1.relation AS relid,
       db.datname AS "database",
       ns.nspname AS "schema",
       cl.relname AS "relname",
       l1.pid AS w_pid,
       l1.mode AS w_mode,
       l1.mppsessionid AS w_session, -- waiting session
       l2.mppsessionid AS b_session, -- blocking session
       l2.mode AS b_mode
FROM
  (SELECT *
   FROM pg_locks
   WHERE locktype='relation') l1
LEFT JOIN pg_locks l2 ON (l1.relation = l2.relation
                          AND l1.database = l2.database
                          AND l1.gp_segment_id = l2.gp_segment_id
                          AND l1.granted = 'f'
                          AND l2.granted = 't')
LEFT JOIN pg_database db ON (l1.database = db.oid)
LEFT JOIN pg_class cl ON (l1.relation = cl.oid)
LEFT JOIN pg_namespace ns ON (ns.oid = cl.relnamespace)
LEFT JOIN pg_stat_activity act1 ON (l1.mppsessionid = act1.sess_id)
LEFT JOIN pg_stat_activity act2 ON (l2.mppsessionid = act2.sess_id)
WHERE l2.granted = 't'
ORDER BY relid, gp_segment_id ; 

 

 

Information on locktype = resource queue

List the current status of resource queues:
Note: the number of "rsqwaiters" is the number of queries waiting on the resource queue.

SELECT * FROM gp_toolkit.gp_resqueue_status; 

 

List the current queries running in the resource queue:
Note: If the "is_blocked" equals "t", then the query is using a slot in the resource queue but is waiting on a lock for a relation.

GPDB 5.x

SELECT l1.locktype,
       rq.rsqname,
       db.datname AS database,
       l1.pid,
       l1.mppsessionid AS session_id,
       substr(a.current_query, 1, 50) AS holder_query,
       CASE
           WHEN
                  (SELECT count(*)
                   FROM pg_locks l2
                   WHERE l2.mppsessionid = l1.mppsessionid
                     AND GRANTED = FALSE) > 0 THEN TRUE
           ELSE FALSE
       END AS is_blocked,
       now() - a.query_start AS run_duration
FROM
  (SELECT *
   FROM pg_locks
   WHERE locktype = 'resource queue'
     AND GRANTED = TRUE) l1
LEFT JOIN pg_stat_activity a ON (l1.mppsessionid = a.sess_id)
LEFT JOIN pg_resqueue rq ON (l1.objid = rq.oid)
LEFT JOIN pg_database db ON (l1.database = db.oid)
ORDER BY rsqname,
         session_id; 

 

GPDB 6.x

SELECT l1.locktype,
       rq.rsqname,
       db.datname AS database,
       l1.pid,
       l1.mppsessionid AS session_id,
       substr(a.query, 1, 50) AS holder_query,
       CASE
           WHEN
                  (SELECT count(*)
                   FROM pg_locks l2
                   WHERE l2.mppsessionid = l1.mppsessionid
                     AND GRANTED = FALSE) > 0 THEN TRUE
           ELSE FALSE
       END AS is_blocked,
       now() - a.query_start AS run_duration
FROM
  (SELECT *
   FROM pg_locks
   WHERE locktype = 'resource queue'
     AND GRANTED = TRUE) l1
LEFT JOIN pg_stat_activity a ON (l1.mppsessionid = a.sess_id)
LEFT JOIN pg_resqueue rq ON (l1.objid = rq.oid)
LEFT JOIN pg_database db ON (l1.database = db.oid)
ORDER BY rsqname,
         session_id; 

 

List the current queries waiting on the resource queue:

SELECT l1.locktype,
       rq.rsqname,
       db.datname AS database,
       l1.pid,
       l1.mppsessionid AS session_id,
       now() - a.query_start AS wait_duration
FROM
  (SELECT *
   FROM pg_locks
   WHERE locktype = 'resource queue'
     AND GRANTED = FALSE) l1
LEFT JOIN pg_stat_activity a ON (l1.mppsessionid = a.sess_id)
LEFT JOIN pg_resqueue rq ON (l1.objid = rq.oid)
LEFT JOIN pg_database db ON (l1.database = db.oid)
ORDER BY rsqname,
         session_id; 

 

 

Information on locktype = transactionid

SELECT l1.gp_segment_id AS segment_id,
       l1.locktype,
       l1.transactionid AS transaction_id,
       db.datname AS "database",
       l1.pid AS w_pid,
       l1.mode AS w_mode,
       l1.mppsessionid AS w_session, -- waiting session
       l2.mppsessionid AS b_session, -- blocking session
       l2.mode AS b_mode
FROM
  (SELECT *
   FROM pg_locks
   WHERE locktype = 'transactionid') l1
LEFT JOIN pg_locks l2 ON (l1.transactionid = l2.transactionid
                          AND l1.gp_segment_id = l2.gp_segment_id
                          AND l1.granted = 't'
                          AND l2.granted = 'f')
LEFT JOIN pg_database db ON (l1.database = db.oid)
LEFT JOIN pg_stat_activity act1 ON (l1.mppsessionid = act1.sess_id)
LEFT JOIN pg_stat_activity act2 ON (l2.mppsessionid = act2.sess_id)
WHERE l2.granted = 't'
ORDER BY 1;