Scripts for identifying locking session information in the database in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article provides scripts to help an administrator identify locking session information in the database.

Note: Verify the script on a test cluster before running it on production.

Environment

Product Version: 6.18

Resolution

Summary of the waiting process

To get a summary of the waiting process, run this query:

Greenplum 5x + 6x:

SELECT
    (select datname from pg_database where oid=a.database) AS "Database Name",
    a.locktype AS "Lock Type",
    a.relation::regclass AS "Relation Name",
    (select rsqname from pg_resqueue where oid=a.objid) AS "Resource Queue",
    count(*) AS "Total Waiters"
FROM pg_locks a
WHERE a.granted='f'
GROUP BY 1,2,3,4;

Lock information where LockType = "Relation"

Blocker information is displayed with this query:

Greenplum 5x:

SELECT
    l.locktype AS "Blocker locktype",
    d.datname AS "Database",
    l.relation::regclass  AS "Blocking Table",
    a.usename AS "Blocking user",
    l.pid AS "Blocker pid",
    l.mppsessionid AS "Blockers SessionID",
    l.mode AS "Blockers lockmode",
    now()-a.query_start AS "Blocked duration",
    substring(a.current_query from 1 for 40) AS "Blocker Query"
FROM
    pg_locks l,
    pg_stat_activity a,
    pg_database d
WHERE l.pid=a.procpid
AND l.database=d.oid
AND l.granted = true
AND relation in ( select relation from pg_locks where granted='f')
ORDER BY 3;

Greenplum 6x:

SELECT
    l.locktype AS "Blocker locktype",
    d.datname AS "Database",
    l.relation::regclass  AS "Blocking Table",
    a.usename AS "Blocking user",
    l.pid AS "Blocker pid",
    l.mppsessionid AS "Blockers SessionID",
    l.mode AS "Blockers lockmode",
    now()-a.query_start AS "Blocked duration",
    substring(a.query from 1 for 40) AS "Blocker Query"
FROM
    pg_locks l,
    pg_stat_activity a,
    pg_database d
WHERE l.pid=a.pid
AND l.database=d.oid
AND l.granted = true
AND relation in ( select relation from pg_locks where granted='f')
ORDER BY 3;

Note: Check the orphan locking query below to identify if this blocker sessionID shown in the query is actually waiting due to an orphan process lock (under the waiters sessionID).

Orphan information

Orphan process lock information is displayed with this query:

Greenplum 5x + 6x:

SELECT 
    w.relation::regclass AS "Table",
    w.mode               AS "Waiters Mode",
    w.pid                AS "Waiters PID",
    w.mppsessionid       AS "Waiters SessionID",
    b.mode               AS "Blockers Mode",
    b.pid                AS "Blockers PID",
    b.mppsessionid       AS "Blockers SessionID",
    (select 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port from gp_segment_configuration c where c.content=b.gp_segment_id and role='p') AS "Blocking Segment"
FROM pg_catalog.pg_locks AS w, pg_catalog.pg_locks AS b 
Where ((w."database" = b."database" AND w.relation  = b.relation)
OR w.transactionid = b.transactionid)
AND w.granted='f'
AND b.granted='t'
AND w.mppsessionid <> b.mppsessionid
AND w.mppsessionid in (SELECT l.mppsessionid FROM pg_locks l WHERE l.granted = true AND relation in ( select relation from pg_locks where granted='f'))
AND w.gp_segment_id = b.gp_segment_id
ORDER BY 1;

 

Waiter information

Waiter information is displayed below: 

Greenplum 5x:

SELECT
    l.locktype AS "Waiters locktype",
    d.datname AS "Database",
    l.relation::regclass  AS "Waiting Table",
    a.usename AS "Waiting user",
    l.pid AS "Waiters pid",
    l.mppsessionid AS "Waiters SessionID",
    l.mode AS "Waiters lockmode",
    now()-a.query_start AS "Waiting duration",
    substring(a.current_query from 1 for 40) AS "Waiters Query"
FROM
    pg_locks l,
    pg_stat_activity a,
    pg_database d
WHERE l.pid=a.procpid
AND l.database=d.oid
AND l.granted = 'f'
ORDER BY 3;

Greenplum 6x:

SELECT
    l.locktype AS "Waiters locktype",
    d.datname AS "Database",
    l.relation::regclass  AS "Waiting Table",
    a.usename AS "Waiting user",
    l.pid AS "Waiters pid",
    l.mppsessionid AS "Waiters SessionID",
    l.mode AS "Waiters lockmode",
    now()-a.query_start AS "Waiting duration",
    substring(a.query from 1 for 40) AS "Waiters Query"
FROM
    pg_locks l,
    pg_stat_activity a,
    pg_database d
WHERE l.pid=a.pid
AND l.database=d.oid
AND l.granted = 'f'
ORDER BY 3;

Blocker information

Blocker information where LockType = "Transaction ID" is displayed below:

Greenplum 5x:

SELECT
    l.locktype AS "Blocker locktype",
    l.relation::regclass  AS "Blocking Table",
    a.usename AS "Blocking user",
    l.pid AS "Blocker pid",
    l.mppsessionid AS "Blockers SessionID",
    l.mode AS "Blockers lockmode",
    now()-a.query_start AS "Blocked duration",
    substring(a.current_query from 1 for 40) AS "Blocker Query"
FROM
    pg_locks l,
    pg_locks w,
    pg_stat_activity a
WHERE l.pid=a.procpid
AND l.transactionid=w.transactionid
AND l.granted = true
AND w.granted = false
AND l.transactionid is not NULL
ORDER BY 3; 

Greenplum 6x:

SELECT
    l.locktype AS "Blocker locktype",
    l.relation::regclass  AS "Blocking Table",
    a.usename AS "Blocking user",
    l.pid AS "Blocker pid",
    l.mppsessionid AS "Blockers SessionID",
    l.mode AS "Blockers lockmode",
    now()-a.query_start AS "Blocked duration",
    substring(a.query from 1 for 40) AS "Blocker Query"
FROM
    pg_locks l,
    pg_locks w,
    pg_stat_activity a
WHERE l.pid=a.pid
AND l.transactionid=w.transactionid
AND l.granted = true
AND w.granted = false
AND l.transactionid is not NULL
ORDER BY 3;

Resource Queue information

Resource Queue information where LockType = "Resource Queue" is displayed below: 

Greenplum 5x + 6x:

SELECT 
   rsqname as "RQname",
   rsqcountlimit as "RQActivestmt-Limit",
   rsqcountvalue as "RQActivestmt-Current",
   rsqcostlimit as "RQCost-Limit",
   rsqcostvalue as "RQCost-Current",
   rsqmemorylimit::bigint as "RQMemory-Limit",
   rsqmemoryvalue::bigint "RQMemory-Current",
   rsqholders as "RQHolders",
   rsqwaiters as "RQWaiters"
FROM gp_toolkit.gp_resqueue_status;

For more Resource Queue information, check out the scripts mentioned in Script for Resource Queue information.