ORA-04091: 表 [blah] 正在发生变化，触发器/函数可能看不到它

Question

我最近开始开发一个大型复杂的应用程序，由于此错误，我刚刚被分配了一个错误：有

ORA-04091: table SCMA.TBL1 is mutating, trigger/function may not see it
ORA-06512: at "SCMA.TRG_T1_TBL1_COL1", line 4
ORA-04088: error during execution of trigger 'SCMA.TRG_T1_TBL1_COL1'

问题的触发器看起来像

    create or replace TRIGGER TRG_T1_TBL1_COL1
   BEFORE  INSERT OR UPDATE OF t1_appnt_evnt_id ON TBL1
   FOR EACH ROW
   WHEN (NEW.t1_prnt_t1_pk is not  null)
   DECLARE
        v_reassign_count number(20);
   BEGIN
       select count(t1_pk) INTO v_reassign_count from TBL1
              where  t1_appnt_evnt_id=:new.t1_appnt_evnt_id and t1_prnt_t1_pk is not null;
       IF (v_reassign_count > 0) THEN
           RAISE_APPLICATION_ERROR(-20013, 'Multiple reassignments not allowed');
       END IF;
   END;

表有一个主键“t1_pk”，一个“约会”事件编号” t1_appnt_evnt_id 和另一列“t1_prnt_t1_pk”可能会或可能 不包含另一行的t1_pk。

看来触发器正在试图确保没有其他人拥有 相同的 t1_appnt_evnt_id 已引用此行引用的同一行 引用另一行（如果此行引用另一行）。

DBA 对错误报告的评论说“删除触发器，并在代码中执行检查”，但不幸的是他们在 Hibernate 之上有一个专有的代码生成框架，所以我什至不知道它实际上在哪里被写出来，所以我希望有一种方法可以让这个触发器发挥作用。 有没有？

Answer 1

我想我不同意你对触发器试图做什么的描述
做。 在我看来，它是为了强制执行这个业务规则：
给定 t1_appnt_event 值，只有一行可以有非 NULL 值
一次 t1_prnt_t1_pk。 （它们在第二列中是否具有相同的值并不重要。）

有趣的是，它是为 UPDATE OF t1_appnt_event 定义的，但不是为其他列定义的，所以我认为有人可以通过更新第二列来打破规则，除非该列有单独的触发器。

可能有一种方法可以创建一个基于函数的索引来强制执行此规则，以便您可以完全摆脱触发器。 我想出了一种方法，但它需要一些假设：

• 该表有一个数字主键
• 主键和 t1_prnt_t1_pk 都始终为正数

如果这些假设成立，您可以创建一个像这样的函数：

dev> create or replace function f( a number, b number ) return number deterministic as
  2  begin
  3    if a is null then return 0-b; else return a; end if;
  4  end;

和一个像这样的索引：

CREATE UNIQUE INDEX my_index ON my_table
  ( t1_appnt_event, f( t1_prnt_t1_pk, primary_key_column) );

因此，PMNT 列为 NULL 的行将出现在索引中，并将主键的倒数作为第二个值，因此它们永远不会相互冲突。 非 NULL 的行将使用该列的实际（正）值。 发生约束冲突的唯一方法是两行的两列中具有相同的非 NULL 值。

这可能过于“聪明”，但它可能会帮助您解决问题。

Paul Tomblin 的更新：我对 igor 在评论中提出的原始想法进行了更新：

 CREATE UNIQUE INDEX cappec_ccip_uniq_idx 
 ON tbl1 (t1_appnt_event, 
    CASE WHEN t1_prnt_t1_pk IS NOT NULL THEN 1 ELSE t1_pk END);

Answer 2

我同意 Dave 的观点，即可以而且应该使用内置约束（例如唯一索引（或唯一约束））来实现所需的结果。

如果您确实需要解决变异表错误，通常的方法是创建一个包，其中包含一个包范围的变量，该变量是一个可用于识别更改的行的表（我认为 ROWID 是可能，否则你必须使用PK，我目前不使用Oracle，所以我无法测试它）。 然后，FOR EACH ROW 触发器用该语句修改的所有行填充此变量，然后有一个 AFTER every 语句触发器读取这些行并验证它们。

像这样的东西（语法可能是错误的，我已经有几年没有使用 Oracle 了）

CREATE OR REPLACE PACKAGE trigger_pkg;
   PROCEDURE before_stmt_trigger;
   PROCEDURE for_each_row_trigger(row IN ROWID);
   PROCEDURE after_stmt_trigger;
END trigger_pkg;

CREATE OR REPLACE PACKAGE BODY trigger_pkg AS
   TYPE rowid_tbl IS TABLE OF(ROWID);
   modified_rows rowid_tbl;

   PROCEDURE before_stmt_trigger IS
   BEGIN
      modified_rows := rowid_tbl();
   END before_each_stmt_trigger;

   PROCEDURE for_each_row_trigger(row IN ROWID) IS
   BEGIN
      modified_rows(modified_rows.COUNT) = row;
   END for_each_row_trigger;

   PROCEDURE after_stmt_trigger IS
   BEGIN
      FOR i IN 1 .. modified_rows.COUNT LOOP
         SELECT ... INTO ... FROM the_table WHERE rowid = modified_rows(i);
         -- do whatever you want to
      END LOOP;
   END after_each_stmt_trigger;
END trigger_pkg;

CREATE OR REPLACE TRIGGER before_stmt_trigger BEFORE INSERT OR UPDATE ON mytable AS
BEGIN
   trigger_pkg.before_stmt_trigger;
END;

CREATE OR REPLACE TRIGGER after_stmt_trigger AFTER INSERT OR UPDATE ON mytable AS
BEGIN
   trigger_pkg.after_stmt_trigger;
END;

CREATE OR REPLACE TRIGGER for_each_row_trigger
BEFORE INSERT OR UPDATE ON mytable
WHEN (new.mycolumn IS NOT NULL) AS
BEGIN
   trigger_pkg.for_each_row_trigger(:new.rowid);
END;

Answer 3

对于任何基于触发器（或基于应用程序代码）的解决方案，您需要
设置锁定以防止多用户环境中的数据损坏。
即使您的触发器有效，或者被重写以避免变异表
问题，它不会阻止 2 个用户同时更新
t1_appnt_evnt_id 为非 t1_appnt_evnt_id 的行上的相同值
null：假设当前没有 t1_appnt_evnt_id=123 的行并且
t1_prnt_t1_pk 不为空：

Session 1> update tbl1 
           set t1_appnt_evnt_id=123 
           where t1_prnt_t1_pk =456;
           /* OK, trigger sees count of 0 */

Session 2> update tbl1
           set t1_appnt_evnt_id=123
           where t1_prnt_t1_pk =789;
           /* OK, trigger sees count of 0 because 
              session 1 hasn't committed yet */

Session 1> commit;

Session 2> commit;

您现在的数据库已损坏！

避免这种情况的方法（在触发器或应用程序代码中）是锁定
在执行检查之前，t1_appnt_evnt_id=123 引用的表中的父行：

select appe_id 
into   v_app_id
from parent_table
where appe_id = :new.t1_appnt_evnt_id
for update;    

现在会话 2 的触发器必须等待会话 1 提交或回滚，然后才能执行检查。

实施 Dave Costa 的索引会更简单、更安全！

最后，我很高兴没有人建议将 PRAGMA AUTONOMOUS_TRANSACTION 添加到您的触发器中：这经常在论坛上建议，并且只要变异表问题消失即可起作用 - 但它使数据完整性问题变得更糟！ 所以只是不要...

Answer 4

我在使用 Hibernate 时也遇到了类似的错误。 通过使用冲洗会话

getHibernateTemplate().saveOrUpdate(o);
getHibernateTemplate().flush();

为我解决了这个问题。 （我没有发布我的代码块，因为我确信所有内容都已正确编写并且应该可以工作 - 但直到我添加了前面的flush() 语句后才开始工作）。 也许这可以帮助某人。