This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find a list of blocked and deadlock queries by other blocking queries of Greenplum Database Server.
You can visit below article to know more about, what is a lock, deadlock, block and timeout?
Database Theory: What is Timeout, Lock, Block, and Deadlock in a Database System?
If two queries are trying to get the same resource, both are depended on each other and generates block state first and then deadlock.
In the below script, you can find a list of blocked queries and blocker queries with a value of time age.
You can find a process_id of blocker query which we can kill for avoiding the deadlock situation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT kl.pid as blocking_pid ,ka.usename as blocking_user ,ka.current_query as blocking_query ,bl.pid 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 bl.pid = 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 bl.page is not distinct from kl.page 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 bl.pid <> kl.pid JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.procpid WHERE kl.granted and not bl.granted ORDER BY a.query_start; |
