通过函数定位DML锁等待

--创建函数，注意此函数只能定位由于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