top of page
Волнистый абстрактный фон

PostgreSQL: database locks

Locks in PostgreSQL database (GreenPlum)

Information on object locks (SQL type => PostgreSQL (relation))



The window displays information about object locks imposed on them when executing sql commands by PostgreSQL database users when executing queries/commands.

List of lock

select l.locktype,
 from pg_locks l 
   left join pg_database d 
       on l.database=d.oid
   left join pg_class c 
       on l.relation=c.oid
   left join pg_class cl 
       on l.classid=cl.oid
order by 2 


select * from pg_stat_activity where pid=$$PID

Information on session locks in PostgreSQSL (SQL type => PostgreSQL (session))



The window displays information about sessions whose work is blocked by other sessions in the database PostgreSQL

List of lock

SELECT     AS pid,
        blocked_activity.usename  AS blocked_user,     AS blocking_pid,
        blocking_activity.usename AS blocking_user,
        blocked_activity.query    AS blocked_statement,
        blocking_activity.query   AS current_statement_in_blocking_process,
        blocked_activity.application_name AS blocked_application,
        blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
   JOIN pg_catalog.pg_stat_activity blocked_activity  
        ON =
   JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
       AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
       AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
       AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
       AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
       AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
       AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
       AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
       AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
       AND !=    
   JOIN pg_catalog.pg_stat_activity blocking_activity 
        ON =
  WHERE NOT blocked_locks.GRANTED

Information on session locks in GreenPlum (SQL type => GreenPlum (session))



The window displays information about sessions whose work is blocked by other sessions in the GreenPlum database

List of lock

SELECT as blocking_pid     
   ,ka.usename as blocking_user
   ,ka.current_query as blocking_query
   , as blocked_pid
   ,a.usename as blocked_user
   ,a.current_query as blocked_query 
   ,to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
   ON = a.procpid                            
JOIN pg_catalog.pg_locks kl
   ON bl.locktype = kl.locktype
   and bl.database is not distinct from kl.database
   and bl.relation is not distinct from kl.relation
   and is not distinct from
   and bl.tuple is not distinct from kl.tuple
   and bl.transactionid is not distinct from kl.transactionid
   and bl.classid is not distinct from kl.classid
   and bl.objid is not distinct from kl.objid   
   and bl.objsubid is not distinct from kl.objsubid
   and <>
JOIN pg_catalog.pg_stat_activity ka                    
   ON = ka.procpid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start

bottom of page