利用触发器解决更新主键冲突的问题

Posted on 2012/04/20 by yangtingkun

有朋友问我能否用触发器实现更新数据时，如果发现主键已经存在，则将冲突的主键更新为当前记录之前的主键值。
简单的说，如果表中存在主键为1和2的记录，如果一条UPDATE语句将1更新为2，那么想要实现的功能是为了确保这个UPDATE可以执行成功，在后台自动将ID为2的记录更新为1。
这个功能应该可以实现，但是直接在触发器中肯定无法实现，因为这是一个标准的变异表问题。而且即使是采用自治事务的方式，也需要小心，因为要修改原有的记录就必须通过UPDATE实现，而这个UPDATE又会导致触发器的触发，如果处理不当，就会导致循环触发。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30), CONSTRAINT PK_T PRIMARY KEY (ID));
TABLE created.
SQL> CREATE OR REPLACE PROCEDURE P_UPDATE_T (P_NEW NUMBER, P_OLD NUMBER) AS
  2  BEGIN
  3     FOR I IN (SELECT ID FROM T WHERE ID = P_NEW) LOOP
  4             UPDATE T SET ID = P_OLD WHERE ID = I.ID;
  5     END LOOP;
  6  END;
  7  /
PROCEDURE created.
SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB WHERE ROWNUM < 5;
4 ROWS created.
SQL> COMMIT;
Commit complete.
SQL> CREATE OR REPLACE TRIGGER TRI_B_T 
  2  BEFORE UPDATE ON T
  3  FOR EACH ROW
  4  BEGIN
  5     P_UPDATE_T(:NEW.ID, :OLD.ID);
  6  END;
  7  /
TRIGGER created.
SQL> SELECT * FROM T;
        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
         2 BRIDGE_CROSSING
         3 C_NORMAL
         4 C_SINGLE
SQL> UPDATE T SET ID = 3 WHERE ID = 2;
UPDATE T SET ID = 3 WHERE ID = 2
       *
ERROR at line 1:
ORA-04091: TABLE TEST.T IS mutating, TRIGGER/FUNCTION may NOT see it
ORA-06512: at "TEST.P_UPDATE_T", line 3
ORA-06512: at "TEST.TRI_B_T", line 2
ORA-04088: error during execution OF TRIGGER 'TEST.TRI_B_T'
SQL> CREATE OR REPLACE PROCEDURE P_UPDATE_T (P_NEW NUMBER, P_OLD NUMBER) AS
  2  PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4     FOR I IN (SELECT ID FROM T WHERE ID = P_NEW) LOOP
  5             UPDATE T SET ID = P_OLD WHERE ID = I.ID;
  6     END LOOP;
  7     COMMIT;
  8  END;
  9  /
PROCEDURE created.
SQL> UPDATE T SET ID = 3 WHERE ID = 2;
UPDATE T SET ID = 3 WHERE ID = 2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting FOR resource
ORA-06512: at "TEST.P_UPDATE_T", line 5
ORA-06512: at "TEST.TRI_B_T", line 2
ORA-04088: error during execution OF TRIGGER 'TEST.TRI_B_T'
ORA-06512: at "TEST.P_UPDATE_T", line 5
ORA-06512: at "TEST.TRI_B_T", line 2
ORA-04088: error during execution OF TRIGGER 'TEST.TRI_B_T'

正如前面提到的，如果直接修改则碰到变异表问题，如果想要通过自治事务解决变异表的问题，又会导致自身的死锁问题。
为了解决这个问题，需要仿照变异表的常规处理方法来解决。常规处理方法是通过一个包记录变量，加上BEFORE和AFTER两个行级触发器以及一个语句级触发器的共同配合。但是一般而言，变异表的处理并不会引入本身记录的更新，而当前还需要解决更新当前表引发的无限循环问题。

SQL> CREATE OR REPLACE PACKAGE P_VAR AS
  2  G_STATUS NUMBER := 0;
  3  END;
  4  /
Package created.
SQL> CREATE OR REPLACE FUNCTION F_GET_T (P_ID NUMBER) RETURN NUMBER AS
  2  PRAGMA AUTONOMOUS_TRANSACTION;
  3  V_RESULT NUMBER;
  4  BEGIN
  5     SELECT COUNT(*) INTO V_RESULT FROM T WHERE ID = P_ID;
  6     RETURN V_RESULT;
  7  END;
  8  /
FUNCTION created.
SQL> CREATE OR REPLACE TRIGGER TRI_B_T
  2  BEFORE UPDATE ON T
  3  FOR EACH ROW
  4  DECLARE
  5  V_RESULT NUMBER;
  6  BEGIN
  7     V_RESULT := F_GET_T(:NEW.ID);
  8     IF V_RESULT = 1 THEN
  9             :NEW.ID := -:NEW.ID;
 10             P_VAR.G_STATUS := 1;
 11     END IF;
 12  END;
 13  /
TRIGGER created.
SQL> CREATE OR REPLACE TRIGGER TRI_A_T
  2  AFTER UPDATE ON T
  3  FOR EACH ROW
  4  DECLARE
  5  V_JOB NUMBER;
  6  BEGIN
  7     IF P_VAR.G_STATUS = 1 THEN
  8             DBMS_JOB.SUBMIT(V_JOB, 'BEGIN
  9             UPDATE T SET ID = ' || :OLD.ID || ' WHERE ID = ' || (-1 * :NEW.ID) || ';
 10             COMMIT;
 11             UPDATE T SET ID = ' || (-1 * :NEW.ID) || ' WHERE ID = ' || :NEW.ID || ';
 12             COMMIT; 
 13             END;', SYSDATE);
 14             P_VAR.G_STATUS := 0;
 15     END IF;
 16  END;
 17  /
TRIGGER created.
SQL> SELECT * FROM T;
        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
         2 BRIDGE_CROSSING
         3 C_NORMAL
         4 C_SINGLE
SQL> UPDATE T SET ID = 3 WHERE ID = 2;
1 ROW updated.
SQL> SELECT * FROM T;
        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
        -3 BRIDGE_CROSSING
         3 C_NORMAL
         4 C_SINGLE
SQL> SELECT WHAT FROM USER_JOBS;
WHAT
-----------------------------------------------------------------------------------
BEGIN
                UPDATE T SET ID = 2 WHERE ID = 3;
                COMMIT;
                UPDATE T SET ID = 3 WHERE ID = -3;
                COMMIT;
                END;
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM T;
        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
         3 BRIDGE_CROSSING
         2 C_NORMAL
         4 C_SINGLE

最终问题通过利用包变量记录是否存在冲突记录，配合异步JOB的方式对冲突记录进行修改的方式避免了无限UPDATE的情况。
当然上面只是一个示例，对于需要同时更新多条记录的情况，需要在包中构建一个类似映射表的结构，从而记录每条更新的ID是否会出现NEW.ID存在的情况。
而且在提交的JOB中也应该对代码做更多的保护，比如添加异常处理等。
此外，这个问题的最大缺点在于，只有COMMIT后才能达到预期目标，而在更新之后且没有提交之前，更新的结果并不是UPDATE语句要完成的操作，而只是一个中间结果。
事实上，这个问题本来就不应该由触发器来解决，对于更新现有ID到一个已经存在的ID的操作，应该通过一段PL/SQL代码来实现，这样才能更好的考虑并发，事务一致性和事务完整性。

This entry was posted in ORACLE and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *