Pivotal Knowledge Base

Follow

Script - Database Locks

Goal

Is to provide a simple scripts to help administrator identify locking session information in the database.

Note : Please verify the script on a test cluster , before running it on production .

Solution

Summary of the waiting process.

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

NOTE: Check Oprhan locking query below to identify if this blocker sessionID shown in the below query is actually waiting due to orphan process lock (Under waiters sessionID)

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;

-- Orphan process lock information .

The way to check if the locking issue is caused by orphan process is, run the blocker query first (from above) and then run the query below, if the blockers SessionID from above query is on the waiters section, then blocker from the first query is actually waiting on a segment where it had not a acquired a lock and hence blocking other session, if the blocker SessionID (from above) is on the blocker section in the below query , then the locks are not held by any orphan process.

For more information on the orphan process use the scripts mentioned here

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

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;

Blocker information where LockType = "Transaction ID"

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.transaction=w.transactionid
AND l.granted = true
AND w.granted = false
AND l.transactionid is not NULL
ORDER BY 3;

Resource Queue information where LockType = "Resource Queue"

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;

More on the Resource Queue information check out the scripts mentioned here

Comments

  • Avatar
    Brian Honohan

    The query below gives a quick overview of non-idle queries and their lock and wait status:

    select a.query_start,now() - a.query_start AS query_duration,substr(a.current_query,1,50) AS query_truncated,(select datname from pg_database where oid=b.database) AS "Database Name",b.relation::regclass AS "Relation Name",b.locktype AS "Lock Type",b.mode AS "Lock Mode",a.waiting,a.waiting_reason from pg_stat_activity a, pg_locks b where a.current_query <> '<IDLE>' AND a.sess_id=b.mppsessionid order by a.query_start asc;

     

    Edited by Brian Honohan
Powered by Zendesk