oracle出现ORA-04091错误时使用自治事务治标不治本

经常会有人提出这样的问题:“有一个这样的问题，有一个表A有 a,b,c,d四个字段，修改一条记录d的值为2的倍数，希望把该记录插入相同结构的表B中。并删除A表的这条记录。”

相关SQL：

--一张表

create table FOO
(
  A NUMBER(10),
  B NUMBER(10),
  C NUMBER(10),
  D NUMBER(10)
)
;

--基于这张表的触发器

CREATE OR REPLACE TRIGGER tri_foo
AFTER INSERT OR UPDATE ON foo
FOR EACH ROW
DECLARE
  N_NUM NUMBER(5);
BEGIN
  IF MOD(TRUNC(:NEW.D),2) =0 THEN
      DELETE FROM FOO WHERE FOO.D = :NEW.D;
  END IF;
END;

  这个函数在执行的时候会报告这样的错误：“ORA-04091:表SCOTT.FOO 发生了变化，触发器/函数不能读它”。网上有很多关于该错误的解决方案，其中，有很大一部分是增加PRAGMA AUTONOMOUS_TRANSACTION语句，设置该触发器为自治事务，然后避免该错误。增加之后确实不报错了，而且貌似可以执行了，但是事真的如此么？修改之后的触发器如下：

CREATE OR REPLACE TRIGGER tri_foo
AFTER INSERT OR UPDATE ON foo
FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
  N_NUM NUMBER(5);
BEGIN
  IF MOD(TRUNC(:NEW.D),2) =0 THEN
      DELETE FROM FOO WHERE FOO.D = :NEW.D;
  END IF;
  COMMIT;
END;

让我们通过真实的数据来验证这个解决方案的不可行：

ChenZw> insert into foo values(1,1,1,2);

已创建 1 行。

ChenZw> select * from foo;

         A          B          C          D

---------- ---------- ---------- ----------

         1          1          1          2

已选择 1 行。

--看,(1,1,1,2) 这条数据居然是可以插入的！

 

ChenZw> insert into foo values(2,2,2,2);

已创建 1 行。

ChenZw> select * from foo;

         A          B          C          D

---------- ---------- ---------- ----------

         1          1          1          2

         2          2          2          2

已选择2行。

-- 吆吆,我又插入了一条(2,2,2,2),是不是触发器失效了呀?

 

ChenZw> commit;

提交完成。

 

ChenZw> insert into foo values(3,3,3,2);

已创建 1 行。

-- 再插入一条数据

 

ChenZw> commit;

提交完成。

ChenZw> select * from foo;

         A          B          C          D

---------- ---------- ---------- ----------

         3          3          3          2

已选择 1 行。

 

--这里,为什么? 为什么把我之前的数据都给我删掉了,而不是刚刚插入的那条?

 

 

总结一下,这里插入的执行顺序应该是这样的:

1）INSERT开启一个新的事务，写入一条(3,3,3,2)数据,但是该操作尚未提交。

2）触发器语句块执行，开启一个自治事务，删除表中的数据，但是WHERE FOO.D = :NEW.D能够圈到的包含(1,1,1,2),(2,2,2,2)两条数据，由于(3,3,3,2)与触发器不在一个事务中,并且未提交,所以触发器语句块看不到当前的数据。

3）触发器删除语句执行完成，然后提交。

4）INSERT语句的事务完成，提交。

 

作者 陈字文（热衷于PM\ORACLE\JAVA等，欢迎同行交流):ziwen#163.com 扣扣:4零9零2零1零零

 

所以有两点建议：

1）对于INSERT一条语句到数据库中，不要希望能通过触发器来删除该语句。

2）尝试从业务端来解决这个需求。