--创建函数，注意此函数只能定位由于dml操作所引起的锁等待，对于ddl引起的锁等待，此sql无法完全定位
CREATE OR REPLACE FUNCTION report_lock(refcursor, refcursor)
RETURNS SETOF refcursor AS
$BODY$
declare
v_activity_count integer;
v_cur_relation_info record;
v_cur_tuple_info record;
begin
select count(distinct pl.pid) into v_activity_count from pg_stat_activity pa,pg_locks pl
where pl.pid=pa.pid and pl.pid <> pg_backend_pid();
raise notice '截止到目前有与锁相关连接数量为：%个',v_activity_count;
for v_cur_relation_info in
select pn.nspname,relname,count(*) lock_count,relation
from pg_locks pl ,pg_class pc,pg_namespace pn
where pl.relation =pc.oid and relname !~ '^pg_' and locktype='relation' and pn.oid = pc.relnamespace
group by pn.nspname,relname,relation
loop
raise notice '占有锁所在的表为%.%,与其相关的连接数量为：%个 ',v_cur_relation_info.nspname,v_cur_relation_info.relname,v_cur_relation_info.lock_count;
for v_cur_tuple_info in
select page,tuple,count(*) tuple_count from pg_locks pl
where pl.relation=v_cur_relation_info.relation and pl.locktype='tuple' and pl.page is not null and pl.tuple is not null
group by page,tuple
loop
raise notice ' 等待中更新或删除此元组的连接数量为：%个，查询元组的伪sql如下：select * from %.% where ctid=''(%,%)'';'
,v_cur_tuple_info.tuple_count,v_cur_relation_info.nspname,v_cur_relation_info.relname,v_cur_tuple_info.page,v_cur_tuple_info.tuple;
end loop;
end loop;
open $1 for
SELECT
'select pg_terminate_backend('||procpid||');' kill_prod,
procpid,
start,
now() - start AS lap,
pa.waiting,
substr(current_query,1,200) current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_xact_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S ,pg_stat_activity pa
WHERE
current_query <> '<IDLE>' and procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>'idle'
ORDER BY
lap DESC;
return next $1;
raise notice '##################################################################################';
raise notice '阻塞间关系阅读方式：第1列与第2列是直接阻塞关系，第1列与第2,3,4...列之间为间接阻塞关系';
raise notice '阻塞间关系阅读方式：第2列与第3列是直接阻塞关系，第2列与第3,4,5...列之间为间接阻塞关系';
open $2 for
WITH w1 AS
--查出显示的具有直接等待关系的进程
( SELECT kl.pid AS parent_pid,bl.pid AS child_pid,cast(kl.pid||'>'||bl.pid AS varchar(1000)) AS pid_rec
FROM pg_catalog.pg_locks bl --等待锁
JOIN pg_catalog.pg_locks kl --持有锁
ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid WHERE NOT bl.granted and bl.mode='ShareLock' and kl.mode='ExclusiveLock')
--查出等待同一条数据的进程
,w2 as (select ARRAY_TO_STRING(ARRAY_AGG(pid),',') pid_string,ARRAY_AGG(pid) pid_array from pg_locks where locktype='tuple' group by page,tuple having count(*)>=2)
--查出显示和隐式的具有直接等待关系的进程
,w3 as (
select parent_pid,regexp_split_to_table(pid_string,',')::integer child_pid,cast(parent_pid||'>'||regexp_split_to_table(pid_string,',') AS varchar(1000)) AS pid_rec from w1,w2 where w1.child_pid = any(w2.pid_array)
union
select * from w1)
--查出所有等待关系(包括间接的)的进程
SELECT pid_rec as 阻塞间关系
--使用递归查询
FROM (WITH RECURSIVE w4 AS
( SELECT child_pid, parent_pid, pid_rec
FROM w3
UNION ALL
SELECT w3.child_pid, w3.parent_pid,cast(w4.pid_rec||'>'||w3.child_pid AS varchar(1000)) AS pid_rec
FROM w3
INNER JOIN w4 ON w3.parent_pid = w4.child_pid)
SELECT pid_rec
FROM w4) w5
ORDER BY array_length(regexp_split_to_array(pid_rec,'>'),1) DESC;
return next $2;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION report_lock(refcursor, refcursor)
OWNER TO postgres;
--查找系统中所有与锁有关连接的信息，（包括ddl语句）
SELECT
'select pg_terminate_backend('||procpid||');' kill_prod,
procpid,
start,
now() - start AS lap,
pa.waiting,
substr(current_query,1,200) current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_xact_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S ,pg_stat_activity pa
WHERE
current_query <> '<IDLE>' and procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>'idle'
ORDER BY
lap DESC;
--查询函数的锁信息
begin;
select report_lock('a','b');
fetch all in b;
fetch all in a;
commit;
--实际测试
--session1，执行一条更新，但不提交
postgres=# begin;
BEGIN
postgres=# update t1 set id=id+1 where id<10;
UPDATE 3
postgres=# select now();
now
-------------------------------
2016-03-25 14:55:27.924359+08
(1 row)
--session2,先执行删除，后执行更新，其被session1阻塞
postgres=# begin;
BEGIN
postgres=# delete from t1 where id=16;
DELETE 1
postgres=# update t1 set id=id+5 where id<=12;
--session3,执行更新，被session2阻塞
postgres=# begin;
BEGIN
postgres=# update t1 set id=id+1 where id=16;
--现构成阻塞链：session1阻塞session2,session2阻塞session3
--实际使用效果如下
postgres=# begin;
BEGIN
postgres=# select report_lock('a','b');
NOTICE: 截止到目前有与锁相关连接数量为：3个
NOTICE: 占有锁所在的表为public.t2,与其相关的连接数量为：3个
NOTICE: 等待中更新或删除此元组的连接数量为：1个，查询元组的伪sql如下：select * from public.t2 where ctid='(833,51)';
NOTICE: 等待中更新或删除此元组的连接数量为：1个，查询元组的伪sql如下：select * from public.t2 where ctid='(833,43)';
NOTICE: 占有锁所在的表为public.t3,与其相关的连接数量为：3个
NOTICE: 占有锁所在的表为public.t1,与其相关的连接数量为：3个
NOTICE: ##################################################################################
NOTICE: 阻塞间关系阅读方式：第1列与第2列是直接阻塞关系，第1列与第2,3,4...列之间为间接阻塞关系
NOTICE: 阻塞间关系阅读方式：第2列与第3列是直接阻塞关系，第2列与第3,4,5...列之间为间接阻塞关系
report_lock
-------------
a
b
(2 rows)
postgres=# fetch all in b;
阻塞间关系
-------------------
28121>28183>28190
28183>28190
28121>28183
(3 rows)
postgres=# fetch all in a;
kill_prod | procpid | start | lap | waiting | current_query
-------------------------------------+---------+-------------------------------+-----------------+---------+-------------------------------------
select pg_terminate_backend(28121); | 28121 | 2016-03-25 14:55:27.924359+08 | 00:01:54.639125 | f | select now();
select pg_terminate_backend(28183); | 28183 | 2016-03-25 14:55:34.451774+08 | 00:01:48.11171 | t | update t1 set id=id+5 where id<=12;
select pg_terminate_backend(28190); | 28190 | 2016-03-25 14:56:05.956339+08 | 00:01:16.607145 | t | update t1 set id=id+1 where id=16;
(3 rows)
postgres=# commit;
COMMIT