使用dbms_flashback.transaction_backout闪回事务的四种可选项介绍

Flashback transaction通过dbms_flashback.transaction_backout实现事务的闪回，考虑到被闪回的事务与其它事务间可能存在不同程度的依赖关系，oracle提供了四种闪回选项：

NOCASCADE：若检测到Transaction之间存在依赖关系，则无法进行回退

NOCASCADE_FORCE：若Transaction间仅存在” Write-after-write dependency”类型的依赖关系，则可以实施强行回退；若遇到除” Write-after-write dependency”以外的其它类型的依赖关系，则无法进行回退

CASCADE：对存在依赖关系的Transaction实施连带回退

NONCONFLICT_ONLY：仅回退Transaction里不存在依赖关系的SQL，会破坏事务的原子性

事务间存在的依赖关系主要有以下三种：

Write-after-write dependency：

Transaction 1 changes a row of a table, and later transaction 2 changes the same row.

Primary key dependency：

A table has a primary key constraint on column c. In a row of the table, column c has the value v. Transaction 1 deletes that row, and later transaction 2 inserts a row into the same table, assigning the value v to column c

Foreign key dependency：

In table b, column b1 has a foreign key constraint on column a1 of table a. Transaction 1 changes a value in a1, and later transaction 2 changes a value in b1.

下面针对这三种依赖关系分别应用四种闪回选项，观察不同的闪回效果

*********************************************************

*                                     Write-after-write dependency                                    *

*********************************************************

drop table t0519_5;

create table t0519_5 (id number,c2 varchar2(2));

--transaction 1:

insert into t0519_5 values(1,‘aa‘);

commit;

--transaction 2:

insert into t0519_5 values(2,‘bb‘);

insert into t0519_5 values(4,‘dd‘);

commit;

--transaction 3:

update t0519_5 set c2=‘cc‘ where c2=‘bb‘;

commit;

select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

               4 dd

---row versions

col versions_starttime format a25

col versions_endtime format a25

set linesize 130

select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from t0519_5 versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V               ID C2

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

19-MAY-15 04.04.57 PM                               000A000A00016D4E U                2 cc

19-MAY-15 04.04.57 PM                               000A002000016CD0 I                4 dd

19-MAY-15 04.04.57 PM     19-MAY-15 04.04.57 PM     000A002000016CD0 I                2 bb

19-MAY-15 04.04.39 PM                               000A001100016CEF I                1 aa

////////// flashback transaction 2 with NOCASCADE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A002000016CD0‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NOCASCADE);

end;

/

declare

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

               4 dd

////////// flashback transaction 2 with NOCASCADE_FORCE  ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A002000016CD0‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NOCASCADE_FORCE);

end;

/

---flashback后的结果

SCOTT@tstdb1-SQL> select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

---rollback flashback transaction操作，表中的数据恢复原状

rollback;

select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

               4 dd

Transaction 2里的两条insert语句回退了，Transaction 3里的update虽然是基于Transaction 2里第一条insert的结果，update的结果还是被保留下来了，可以看出NOCASCADE_FORCE在处理具有"Write-after-write dependency"依赖关系的记录时不会考虑记录间的依赖关系仅回退指定transaction的操作

////////// flashback transaction 2 with CASCADE  ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A002000016CD0‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.CASCADE);

end;

/

select * from t0519_5;

              ID C2

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

               1 aa

---rollback flashback transaction操作，表中的数据恢复原状

rollback;

select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

               4 dd

要回退的是Transaction 2，因为Transaction 3依赖于Transaction 2，所以Transaction 3随着Transaction 2一起被回退，并且回退的顺序应该是先回退Transaction 3再回退Transaction 2。可见CASCADE会把与要回退的Transaction存在依赖关系的Transaction一起回退

////////// flashback transaction 2 with NONCONFLICT_ONLY ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A002000016CD0‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NONCONFLICT_ONLY);

end;

/

select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

---rollback flashback transaction操作，表中的数据恢复原状

rollback;

select * from t0519_5;

              ID C2

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

               1 aa

               2 cc

               4 dd

Transaction 2里的两条insert语句，Transaction 3里的update语句依赖于Transaction 2里的第1条insert语句，所以仅回退Transaction 2里的第2条insert，NONCONFLICT_ONLY挑战了事务的原子性

*********************************************************

*                                             Primary key dependency                                     *

*********************************************************

drop table t0519_6;

alter database add supplemental log data (primary key) columns;   <---必须打开primary key supplemental logging，否则执行dbms_flashback.transaction_backout时会出现ORA-55511: Flashback Transaction experienced error in executing undo SQL

create table t0519_6 (id number,c2 varchar2(2) primary key);

--transaction 1:

insert into t0519_6 values(1,‘aa‘);

insert into t0519_6 values(2,‘bb‘);

commit;

--transaction 2:

insert into t0519_6 values(4,‘dd‘);

delete t0519_6 where c2=‘bb‘;

commit;

--transaction 3:

insert into t0519_6 values(5,‘bb‘);

commit;

select * from t0519_6;

              ID C2

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

               1 aa

               4 dd

               5 bb

---row versions

col versions_starttime format a25

col versions_endtime format a25

set linesize 130

select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from t0519_6 versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V               ID C2

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

19-MAY-15 05.03.51 PM                               000A000A00016D63 I                5 bb

19-MAY-15 05.03.39 PM                               000A000E00016CAF D                2 bb

19-MAY-15 05.03.39 PM                               000A000E00016CAF I                4 dd

19-MAY-15 05.03.15 PM     19-MAY-15 05.03.39 PM     000A000100016C9C I                2 bb

19-MAY-15 05.03.15 PM                               000A000100016C9C I                1 aa

////////// flashback transaction 2 with NOCASCADE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000E00016CAF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NOCASCADE);

end;

/

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

select * from t0519_6;

              ID C2

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

               1 aa

               4 dd

               5 bb

检测到有依赖关系的transaction，无法使用NOCASCADE进行回退

////////// flashback transaction 2 with NOCASCADE_FORCE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000E00016CAF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NOCASCADE_FORCE);

end;

/

*

ERROR at line 1:

ORA-55511: Flashback Transaction experienced error in executing undo SQL

ORA-00001: unique constraint (ORA-00001: unique constraint (SCOTT.SYS_C0015342) violated

.) violated

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

select * from t0519_6;

              ID C2

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

               1 aa

               4 dd

               5 bb

NOCASCADE_FORCE仅能回退存在"Write-after-write dependency"依赖关系的Transaction，如果依赖关系来自于primary key或者foreign key则无法实现回退

////////// flashback transaction 2 with CASCADE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000E00016CAF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.CASCADE);

end;

/

select * from t0519_6;

        ID C2

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

         2 bb

         1 aa

---rollback flashback transaction操作，表中的数据恢复原状

SCOTT@tstdb1-SQL> rollback;

Rollback complete.

SCOTT@tstdb1-SQL> select * from t0519_6;

        ID C2

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

         1 aa

         4 dd

         5 bb        

要回退的是Transaction 2，但Transaction 3依赖于Transaction 2，Cascade能够将有连带关系的Transaction一起回退掉

////////// flashback transaction 2 with NONCONFLICT_ONLY ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000E00016CAF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NONCONFLICT_ONLY);

end;

/

select * from t0519_6;

        ID C2

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

         1 aa

         5 bb

Transaction 3里的insert依赖于Transaction 2里的delete，所以仅回退了Transaction 2里的insert，NONCONFLICT_ONLY在flashback时会避开有依赖关系的行

*********************************************************

*                                                  Foreign key dependency                                *

*********************************************************

drop table t0516_7_chd;

drop table t0516_7_par;

create table t0516_7_par(pid number,pc2 varchar2(2) primary key);

create table t0516_7_chd(cid number,cc2 varchar2(2) references t0516_7_par(pc2));

alter database add supplemental log data (foreign key) columns;    <--必须开启foreign key supplemental logging才能追踪foreign key的依赖关系

--Transaction 1

insert into t0516_7_par values(1,‘aa‘);

commit;

--Transaction 2

insert into t0516_7_par values(3,‘cc‘);

insert into t0516_7_par values(2,‘bb‘);

commit;

--Transaction 3

insert into t0516_7_chd values(200,‘bb‘);

commit;

---row versions

col versions_starttime format a25

col versions_endtime format a25

set linesize 130

select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from t0516_7_par versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V              PID PC

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

19-MAY-15 06.22.06 PM                               000A000600016CBF I                2 bb

19-MAY-15 06.22.06 PM                               000A000600016CBF I                3 cc

19-MAY-15 06.22.01 PM                               000A001D00016CD5 I                1 aa

////////// flashback transaction 2 with NOCASCADE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000600016CBF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NOCASCADE);

end;

/

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

SCOTT@tstdb1-SQL> select * from t0516_7_par;

             PID PC

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

               1 aa

               3 cc

               2 bb

SCOTT@tstdb1-SQL> select * from t0516_7_chd;

             CID CC

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

             200 bb

检测到依赖关系，所以无法回退

////////// flashback transaction 2 with NOCASCADE_FORCE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000600016CBF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NOCASCADE_FORCE);

end;

/

*

ERROR at line 1:

ORA-55511: Flashback Transaction experienced error in executing undo SQL

ORA-02292: integrity constraint (ORA-02292: integrity constraint (SCOTT.SYS_C0015348) violated - child record found

.) violated - child record found

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

SCOTT@tstdb1-SQL> select * from t0516_7_par;

             PID PC

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

               1 aa

               3 cc

               2 bb

SCOTT@tstdb1-SQL> select * from t0516_7_chd;

             CID CC

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

             200 bb

Transaction 3的insert与Transaction 2的第2条insert存在外键约束，NOCASCADE_FORCE遇到由constraint引起的依赖关系无法实现强行回退，NOCASCADE_FORCE只能回退Write-after-write dependency类型的依赖关系

////////// flashback transaction 2 with CASCADE ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000600016CBF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.CASCADE);

end;

/

SCOTT@tstdb1-SQL> select * from t0516_7_par;

             PID PC

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

               1 aa

SCOTT@tstdb1-SQL> select * from t0516_7_chd;

no rows selected

---rollback 将表里的记录恢复原状

SCOTT@tstdb1-SQL> rollback;

Rollback complete.

SCOTT@tstdb1-SQL> select * from t0516_7_par;

       PID PC

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

         1 aa

         3 cc

         2 bb

SCOTT@tstdb1-SQL> select * from t0516_7_chd;

       CID CC

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

       200 bb

Transaction 3依赖于Transaction 2里的第二条insert，Transaction 2、Transaction 3全量回退

////////// flashback transaction 2 with NONCONFLICT_ONLY ///////////

declare

v_xid SYS.XID_ARRAY:=SYS.XID_ARRAY(hextoraw(‘000A000600016CBF‘));

begin

SYS.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns=>1,xids=>v_xid,options=>dbms_flashback.NONCONFLICT_ONLY);

end;

/

SCOTT@tstdb1-SQL> select * from t0516_7_par;

       PID PC

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

         1 aa

         2 bb

SCOTT@tstdb1-SQL> select * from t0516_7_chd;

       CID CC

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

       200 bb

---rollback 将表里的记录恢复原状

SCOTT@tstdb1-SQL> rollback;

Rollback complete.

SCOTT@tstdb1-SQL> select * from t0516_7_par;

       PID PC

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

         1 aa

         3 cc

         2 bb

SCOTT@tstdb1-SQL> select * from t0516_7_chd;

       CID CC

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

       200 bb

仅回退了Transaction 2里的第1条insert，Transaction 2里的第2条insert和Transaction 3保持原状

四种闪回选项在各种依赖关系下是否能够成功闪回的总结：