查看锁等待sql

witht_wait as(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_namefrom pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),t_run as(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_namefrom pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted),t_overlap as(select r.* from t_wait w join t_run r on(r.locktype is not distinct from w.locktype andr.database is not distinct from w.database andr.relation is not distinct from w.relation andr.page is not distinct from w.page andr.tuple is not distinct from w.tuple andr.virtualxid is not distinct from w.virtualxid andr.transactionid is not distinct from w.transactionid andr.classid is not distinct from w.classid andr.objid is not distinct from w.objid andr.objsubid is not distinct from w.objsubid andr.pid <> w.pid)),t_unionall as(select r.* from t_overlap runion allselect w.* from t_wait w)select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,string_agg('Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||'SQL (Current SQL in Transaction): '||chr(10)||case when query is null then 'NULL' else query::text end,chr(10)||'--------'||chr(10)order by( case modewhen 'INVALID' then 0when 'AccessShareLock' then 1when 'RowShareLock' then 2when 'RowExclusiveLock' then 3when 'ShareUpdateExclusiveLock' then 4when 'ShareLock' then 5when 'ShareRowExclusiveLock' then 6when 'ExclusiveLock' then 7when 'AccessExclusiveLock' then 8else 0end  ) desc,(case when granted then 0 else 1 end)) as lock_conflictfrom t_unionallgroup bylocktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

测试如下：

//session 1
test=# select * from test123;id |               info
----+----------------------------------1 | 101dc9e8c1d68234176830154b085ac72 | a43bf589b17bba8f38f117fda7a52b0d3 | daf21791ed3a9a45cca9ff1523d9090f4 | 92d9d4badb391f7e75d0c65bb720002e5 | f6a528c82a337412dbeff0a037bbb41b6 | 95e830bfd6789ff3b460f293ab1943e57 | a48251f4dc31eaaa8d9d14310fe7a66c8 | 0abd9628ccdeabe85f63de244019591a9 | 42a01ab80a13b64619d8b6371caf670b10 | 0048a0b3a4da91c05b7997b2f9a48156
(10 行记录)test=#
test=#
test=#
test=#
test=#
test=# begin;
BEGIN
test=*# delete from test123 where id=1
DELETE 1//session 2,被hang住了test=# begin;
BEGIN
test=*# truncate table test123;

检测结果：

locktype      | relation
datname       | test
relation      | test123
page          |
tuple         |
virtualxid    |
transactionid |
classid       |
objid         |
objsubid      |
lock_conflict | Pid: 8209                                                                                                                                            +| Lock_Granted: false , Mode: AccessExclusiveLock , FastPath: false , VirtualTransaction: 8/1931 , Session_State: active                               +| Username: sde , Database: test , Client_Addr: 192.168.100.182/32 , Client_Port: 6503 , Application_Name: psql                                        +| Xact_Start: 2025-06-30 10:35:22.978384+08 , Query_Start: 2025-06-30 10:35:32.866765+08 , Xact_Elapse: 00:09:33.288584 , Query_Elapse: 00:09:23.400203+| SQL (Current SQL in Transaction):                                                                                                                    +| truncate table test123;                                                                                                                              +| --------                                                                                                                                             +| Pid: 31733                                                                                                                                           +| Lock_Granted: true , Mode: RowExclusiveLock , FastPath: false , VirtualTransaction: 6/7257 , Session_State: idle in transaction                      +| Username: sde , Database: test , Client_Addr: 192.168.100.182/32 , Client_Port: 13683 , Application_Name: psql                                       +| Xact_Start: 2025-06-30 10:33:56.666653+08 , Query_Start: 2025-06-30 10:34:00.961553+08 , Xact_Elapse: 00:10:59.600315 , Query_Elapse: 00:10:55.305415+| SQL (Current SQL in Transaction):                                                                                                                    +| delete from test123 where id=1;

查看阻塞会话，并生成kill sql

with recursive 
tmp_lock as (select distinct--w.mode w_mode,w.page w_page,--w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,--now()-w.query_start w_locktime,w.query w_queryw.pid as id,--w_pid,r.pid as parentid--r_pid,--r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,--r.relation::regclass,--r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,--r.query_start r_query_start,--now()-r.query_start r_locktime,r.query r_query,from (select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,b.query as query,b.xact_start,b.query_start,b.usename,b.datnamefrom pg_locks a,pg_stat_activity bwhere a.pid=b.pidand not a.granted) w,(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,b.query as query,b.xact_start,b.query_start,b.usename,b.datnamefrom pg_locks a,pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activitywhere a.pid=b.pidand a.granted) rwhere 1=1and r.locktype is not distinct from w.locktypeand r.database is not distinct from w.databaseand r.relation is not distinct from w.relationand r.page is not distinct from w.pageand r.tuple is not distinct from w.tupleand r.classid is not distinct from w.classidand r.objid is not distinct from w.objidand r.objsubid is not distinct from w.objsubidand r.transactionid is not distinct from w.transactionidand r.pid <> w.pid),
tmp0 as (select *from tmp_lock tlunion allselect t1.parentid,0::int4from tmp_lock t1where 1=1and t1.parentid not in (select id from tmp_lock)),
tmp3 (pathid,depth,id,parentid) as (SELECT array[id]::text[] as pathid,1 as depth,id,parentidFROM tmp0where 1=1 and parentid=0unionSELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentidFROM tmp0 t1, tmp3 t0where 1=1 and t1.parentid=t0.id
)
select distinct'/'||array_to_string(a0.pathid,'/') as pathid,a0.depth,a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,--'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,--'select pg_terminate_backend('||a0.id|| ');' as term_pid,case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end  as term_pid,case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end  as cancel_pid,a2.datname,a2.usename,a2.application_name,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state--,a2.backend_start,a2.xact_start,a2.query_start
from tmp3 a0
left outer join (select distinct '/'||id||'/' as prefix_id,idfrom tmp0where 1=1 ) a1
on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
left outer join pg_stat_activity a2 -- select * from pg_stat_activity
on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth;

查询结果如下：

-[ RECORD 1 ]----+------------------------------------
pathid           | /31733
depth            | 1
id               | 31733
parentid         | 0
tree_id          |  31733
term_pid         | select pg_terminate_backend(31733);
cancel_pid       | select cancel_backend(31733);
datname          | test
usename          | sde
application_name | psql
client_addr      | 192.168.100.182
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
-[ RECORD 2 ]----+------------------------------------
pathid           | /31733/8209
depth            | 2
id               | 8209
parentid         | 31733
tree_id          |    8209
term_pid         |
cancel_pid       |
datname          | test
usename          | sde
application_name | psql
client_addr      | 192.168.100.182
wait_event_type  | Lock
wait_event       | relation
state            | active

查询超过60s的sql

selectpg_stat_activity.datname,pg_stat_activity.pid,pg_stat_activity.query,pg_stat_activity.client_addr,clock_timestamp() - pg_stat_activity.query_start
frompg_stat_activity pg_stat_activity
where(pg_stat_activity.state = any (array['active'::text,'idle in transaction'::text]))and (clock_timestamp() - pg_stat_activity.query_start) > '00:00:60'::interval
order by(clock_timestamp() - pg_stat_activity.query_start) desc;

结果如下：

-[ RECORD 1 ]--------------------------------
datname     | test
pid         | 31733
query       | delete from test123 where id=1;
client_addr | 192.168.100.182
?column?    | 00:21:25.624592
-[ RECORD 2 ]--------------------------------
datname     | test
pid         | 8209
query       | truncate table test123;
client_addr | 192.168.100.182
?column?    | 00:19:53.719401