Oracle11新特性——撤銷事務（三）

打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g提供了撤銷事務的功能，可以撤銷一個已經提交的事務。

這一篇介紹撤銷事務與外來鍵約束的關係。

Oracle11新特性——撤銷事務（一）：http://yangtingkun.itpub.net/post/468/419695

Oracle11新特性——撤銷事務（二）：http://yangtingkun.itpub.net/post/468/443057

上面一篇文章簡單介紹了撤銷事務的幾種選項，這幾種選項是對關聯事務而言的，對於事務之前的主外來鍵約束，結果又是不同的。

先建立測試環境：

SQL> CREATE TABLE T_PRIMARY (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已建立。

SQL> CREATE TABLE T_FOREIGN (FID NUMBER, FOREIGN KEY (FID) REFERENCES T_PRIMARY);

表已建立。

SQL> INSERT INTO T_PRIMARY VALUES (1, 'A');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> UPDATE T_PRIMARY SET NAME = 'A1';

已更新 1 行。

SQL> INSERT INTO T_PRIMARY VALUES (2, 'B');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> INSERT INTO T_FOREIGN VALUES (2);

已建立 1 行。

SQL> INSERT INTO T_PRIMARY VALUES (3, 'C');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> CONN SYS/test@172.0.2.61/TEST11G.NETDB AS SYSDBA已連線。
SQL> COL OPERATION FORMAT A10
SQL> COL UNDO_SQL FORMAT A85
SQL> SET LINES 120 PAGES 100
SQL> SELECT XID, OPERATION, UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE TABLE_NAME = 'T_PRIMARY';

XID OPERATION UNDO_SQL
---------------- ---------- ------------------------------------------------------------------------
000500590000035B INSERT delete from "YANGTK"."T_PRIMARY" where ROWID = 'AAARuLAAFAAACY9AAB';
000500590000035B UPDATE update "YANGTK"."T_PRIMARY" set "NAME" = 'A' where ROWID = 'AAARuLAAFAAA
0009000500000363 INSERT delete from "YANGTK"."T_PRIMARY" where ROWID = 'AAARuLAAFAAACY9AAC';
000A00680000037C INSERT delete from "YANGTK"."T_PRIMARY" where ROWID = 'AAARuLAAFAAACY9AAA';

下面開始進行撤銷測試：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*第 1 行出現錯誤:
ORA-55511: 閃回事務處理在執行還原 SQL 時出錯
ORA-02292: 違反完整約束條件 (ORA-02292: 違反完整約束條件 (YANGTK.SYS_C0011048) - 已找到子記錄
.) - 已找到子記錄
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

由於要撤銷的事務中包括對主表記錄的插入，而這條記錄被子表引用，因此撤銷肯定會出現錯誤。嘗試使用CASCADE方式進行撤銷：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.CASCADE);
6 END;
7 /
DECLARE
*第 1 行出現錯誤:
ORA-55511: 閃回事務處理在執行還原 SQL 時出錯
ORA-02292: 違反完整約束條件 (ORA-02292: 違反完整約束條件 (YANGTK.SYS_C0011048) - 已找到子記錄
.) - 已找到子記錄
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

CASCADE方式只對關聯事務有效，而對這種主外來鍵關係無效，因此，如果撤銷事務操作會違反主外來鍵約束，那麼即使使用CASCADE方式也沒有作用。

同樣的，另外兩種方式NONCONFLICT_ONLY和NOCASCADE_FORCE也沒有作用：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NONCONFLICT_ONLY);
6 END;
7 /
DECLARE
*第 1 行出現錯誤:
ORA-55511: 閃回事務處理在執行還原 SQL 時出錯
ORA-02292: 違反完整約束條件 (ORA-02292: 違反完整約束條件 (YANGTK.SYS_C0011048) - 已找到子記錄
.) - 已找到子記錄
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NOCASCADE_FORCE);
6 END;
7 /
DECLARE
*第 1 行出現錯誤:
ORA-55511: 閃回事務處理在執行還原 SQL 時出錯
ORA-02292: 違反完整約束條件 (ORA-02292: 違反完整約束條件 (YANGTK.SYS_C0011048) - 已找到子記錄
.) - 已找到子記錄
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

唯一的方法是包括參考主鍵資訊的外來鍵事務一起進行撤銷：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0009000500000363', '000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(2, V_XID);
6 END;
7 /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM YANGTK.T_PRIMARY;

ID NAME
---------- ------------------------------
1 A

感覺這一點上撤銷事務的實現有點問題，主外來鍵約束事務的撤銷應該可以仿效關聯事務的方式實現。否則撤銷事務在實際工作中就會很難使用。