新随笔  :: 订阅 订阅  :: 管理

[转][工地][存]Oracle触发器死锁问题解决

Posted on 2014-05-05 16:34  redcoatjk  阅读(597)  评论(0编辑  收藏  举报

摘自http://blog.itpub.net/12932950/viewspace-607691/

这两天一直在因为系统初期设计原因导致的一个触发器问题。
问题如下:
有表T,有客户编号、账户编号及地址三个字段(为方便起见以最少字段描述)。
一个客户编号下可能存在多个账户编号(3个或4个)。
假设客户编号C0下有A1、A2、A3三个账户编号。
        现在对账户编号A1的地址字段进行了更新,要求通过触发器同时更新客户编号C0下的另外两个账户A2和A3的地址字段。

通过实际的触发,发现存在着一个非常致命的问题:
       由于指定的是自治事务触发器(即指定了pragma autonomous_transaction),假设为T1。每次更新表时触发都是独立的。因此就产生了死循环和死锁的问题。
因为更新了A1,则T1触发。T1内去找到同个客户编号下的A2和A3。然后先更新A2,此时便又触发了T1。然后又找到同个客户编号下的A1和A3。然后先更新A1,而由于先前第一次更新已经锁住了A1,再次更新就会导致死锁。因为先前的A1更新需要现在的A1先完成。而现在的A1更新却被先前的A1更新阻塞了。
        从上面可以看出,如果可以找到这样一个办法,将锁住的行找出来,然后在每次更新之前都判断一下该行是否被锁住。如果锁住则跳到下一条记录进行处理。没锁住则继续更新。
       实际上这只是理想的情况,的确可以通过查询v$lock v$session v$locked_object查到被锁住的行:
select ta.account_no, o.object_name, ta.rowid
 from v$session s, ttest ta, user_objects o, v$locked_object lo
where lo.SESSION_ID = s.SID
  and lo.OBJECT_ID = o.object_id
  and dbms_rowid.rowid_object(ta.rowid) = o.data_object_id
  and o.object_id = s.ROW_WAIT_OBJ#
  and dbms_rowid.rowid_relative_fno(ta.rowid) =  s.ROW_WAIT_FILE#
  and dbms_rowid.rowid_block_number(ta.rowid) = s.ROW_WAIT_BLOCK#
  and dbms_rowid.rowid_row_number(ta.rowid) = s.ROW_WAIT_ROW#
但是通过实际调试发现。这个记录的并不是当时被锁住的所有记录。而是最后一次被锁住的一条记录。什么意思呢?也就是说当时可能有几条记录被锁住。但是这个SQL所查出来的是执行所有锁行操作的语句后,最后一次被锁住的行。也就是失去了即时判断的可能性。
       想通过另外的方法去找到这个被锁住的行我网上也搜索过无数遍了。都没有实际解决方案。可能这么做的人很少吧。
       最后想到了一个办法,就是通过临时表的方法去记录被更新的行。一旦触发T1,就往临时表记录这一行的rowid和对应的锁定标识。然后在更新的时候判断
是否已经存在于该表中。如果有则不更新,没有则往下更新。更新并提交后该行
锁标识清零。进入下一次循环。
       这个临时表是会话级的。因此在触发器循环触发过程中都是处于同一个数据环境下。方便了对指定行的加锁与解锁(其实是设定行锁定状态)。
下一篇准备讲述此次解决死锁问题而学习到的关于Oracle锁的一些方面的知识。
(备注:如果不指定自治事务触发器,则无法修改触发器触发所在的表)