达梦执行存储过程报死锁问题分析排查方法
最近在一个项目中调用存储过程报死锁错误，而根据DEADLOCK_HISTORY也无法看出是哪个表产生了死锁，下面模拟一下环境做测试
drop TABLE if EXISTS test;
CREATE TABLE test (id int);
BEGIN
for i in 1 ..100 loop
insert into test VALUES (i);
end loop;
commit;
end;
CREATE or REPLACE PROCEDURE pt1 as
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE TEST set id=10 where id<20;
commit;
end;
CREATE or REPLACE PROCEDURE pt2 as
BEGIN
UPDATE TEST set id=2 where id<25 and id>5;
end;
CREATE or REPLACE PROCEDURE pt3 as
BEGIN
pt2();
pt1();
end;
exec pt3;
对于存储过程，根据V$DEADLOCK_HISTORY只能知道调用这个存储产生了死锁，无法定位具体哪个表产生了死锁。
由于一执行就报死锁错误。特别是一些比较复杂的存储过程，没有时间打开窗口去排查哪些表产生了阻塞。达梦DEADLOCK_CHECK_INTERVAL参数可以控制死锁检测时间，执行下面语句之后，1分钟之后才会报死锁错误
sp_set_para_value(1,'DEADLOCK_CHECK_INTERVAL',60000);
这段时间就可以根据相关视图查询那个表产生了阻塞
select sysdate as stat_dt,
a.WAIT_TIME/1000.0 as 阻塞时长_s
,block_sess.APPNAME as 阻塞者程序名
,BLOCK_SESS.CLNT_IP as 阻塞者ip
,BLOCK_SESS.SQL_TEXT as 阻塞者当前执行的sql
,BLOCK_SESS.SESS_ID as 阻塞者会话id
,BLOCK_SESS.CURR_SCH as 阻塞者登录名
,BLOCK_SESS.TRX_ID as 阻塞者事务号
,(
select listagg2(top_sql_text||char(10))
from V$SQL_HISTORY sh
where sh.TRX_ID=block_sess.trx_id and sh.sess_id=block_sess.sess_id
) as 阻塞者当前事务执行过的sql --该视图保留记录数有限，如果存在长时间未提交的事务，可能导致不全，只做参考
,wait_sess.APPNAME as 被阻塞者程序名
,wait_SESS.CLNT_IP as 被阻塞者ip
,wait_SESS.SQL_TEXT as 被阻塞者当前执行的sql
,wait_SESS.SESS_ID as 被阻塞者会话id
,wait_SESS.CURR_SCH as 被阻塞者登录名
,wait_SESS.TRX_ID as 被阻塞者事务号
,cast((
select listagg2(block_obj.name||':'||block_lock.lmode||'('||block_lock.ltype||')'||char(10))
from v$lock block_lock
join sysobjects block_obj on block_obj.id= block_lock.table_ID
where block_trx.ID= block_lock.trx_ID and
BLOCK_LOCK.IGN_FLAG<>1 --只看不可忽略的锁
) as varchar(100)) as 阻塞者持有的锁
,cast((
select listagg2(wait_obj.name||':'||wait_lock.lmode||'('||wait_lock.ltype||')'||char(10))
from v$lock wait_lock
join sysobjects wait_obj on wait_obj.id= wait_lock.table_ID
where wait_trx.ID= wait_lock.trx_ID
and wait_LOCK.IGN_FLAG<>1 --只看不可忽略的锁
and WAIT_LOCK.BLOCKED=1 --被阻塞者等待持有的锁
)as varchar(100)) as 被阻塞者等待持有的锁
from SYS."V$TRXWAIT" A
join SYS."V$TRX" block_trx on a.WAIT_FOR_ID= BLOCK_TRX.ID
join SYS."V$TRX" wait_trx on a.ID= wait_TRX.ID
join v$sessions block_sess on block_sess.sess_ID= BLOCK_TRX.sess_ID
join v$sessions wait_sess on wait_sess.sess_ID= wait_TRX.sess_ID
上面示例为什么会产生死锁，原因是采用了自治事务，自治事务使用PRAGMA AUTONOMOUS_TRANSACTION声明在过程或函数中。自治事务相对主事务是完全独立的。即使外部事务回滚或提交，自治事务中的操作不会影响到外部事务。由于执行自治事务时主事务处于挂起状态，如果自治事务需要的锁资源已经被主事务拥有则会产生死锁报错。
