最近在一个项目中调用存储过程报死锁错误,而根据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声明在过程或函数中。自治事务相对主事务是完全独立的。即使外部事务回滚或提交,自治事务中的操作不会影响到外部事务。由于执行自治事务时主事务处于挂起状态,如果自治事务需要的锁资源已经被主事务拥有则会产生死锁报错。
标签:sess,阻塞,排查,死锁,达梦,ID,block,wait From: https://www.cnblogs.com/fangzpa/p/18113007