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

浏览数：34 / 时间：2015年06月12日

摘自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锁的一些方面的知识。
（备注：如果不指定自治事务触发器，则无法修改触发器触发所在的表）