闪回事务处理-主外键约束

准备实验表

SQL> create table t_p(id number primary key,name varchar2(10));

 

Table created.

 

SQL> create table t_f(fid number,foreign key(fid) references t_p(id));

 

Table created.

 

T_P表

id number primary key

name varchar2(10)

 

T_F表

fid number references t_p(id)

 

准备事务

事务一（0A001C0046020000）

SQL> insert into t_p values(1,'a');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from t_p;

 

ID NAME

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

1 a

 

事务二（03001C00CF020000）

SQL> update t_p set name='a1';

 

1 row updated.

 

SQL> insert into t_p values(2,'b');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from t_p;

 

ID NAME

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

1 a1

2 b

 

事务三（05001300CB020000）

SQL> insert into t_f values(2);

 

1 row created.

 

SQL> insert into t_p values(3,'c');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from t_p;

 

ID NAME

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

1 a1

2 b

3 c

 

SQL> select * from t_f;

 

FID

----------

2

 

 

分析

欲闪回事务二

事务二，包含了两条DML

其中，insert 2 b这条记录

被事务三引用，insert 2这条记录通过主外键关系这种方式引用了

 

闪回，使用常规的手段，行不行？

cascade?

nonconflict_only?

 

 

尝试nocascade?

1 declare

2 v_xid xid_array;

3 begin

4 v_xid := sys.xid_array('03001C00CF020000');

5 dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);

6* end;

SQL> /

declare

*

ERROR at line 1:

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

ORA-02292: integrity constraint (ORA-02292: integrity constraint (SCOTT.SYS_C0011074)

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 5

 

尝试cascade

1 declare

2 v_xid xid_array;

3 begin

4 v_xid := sys.xid_array('03001C00CF020000');

5 dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);

6* end;

SQL> /

declare

*

ERROR at line 1:

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

ORA-02292: integrity constraint (ORA-02292: integrity constraint (SCOTT.SYS_C0011074)

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 5

 

尝试nonconflict_only

1 declare

2 v_xid xid_array;

3 begin

4 v_xid := sys.xid_array('03001C00CF020000');

5 dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.NONCONFLICT_ONLY);

6* end;

SQL> /

declare

*

ERROR at line 1:

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

ORA-02292: integrity constraint (ORA-02292: integrity constraint (SCOTT.SYS_C0011074)

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 5

 

 

思考为什么不行

此次与上一例有何不同

表与表之间存在引用

闪回事务，不论使用哪种方式，只能在同一个张表上，执行

这种多表的例子，若想闪回，如何处理？

SQL> select xid,operation,undo_sql from flashback_transaction_query

2 where table_name='T_P' order by START_TIMESTAMP;

 

XID OPERATION UNDO_SQL

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

0A001C0046020000 INSERT delete from "SCOTT"."T_P" where ROWID = 'AAASPBAAE

AAAAIUAAA';

 

03001C00CF020000 INSERT delete from "SCOTT"."T_P" where ROWID = 'AAASPBAAE

AAAAIUAAB';

 

03001C00CF020000 UPDATE update "SCOTT"."T_P" set "NAME" = 'a' where ROWID

= 'AAASPBAAEAAAAIUAAA';

 

05001300CB020000 INSERT delete from "SCOTT"."T_P" where ROWID = 'AAASPBAAE

AAAAIUAAC';

 

SQL> select xid,operation,undo_sql from flashback_transaction_query

2 where table_name='T_F' order by START_TIMESTAMP;

 

XID OPERATION UNDO_SQL

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

05001300CB020000 INSERT delete from "SCOTT"."T_F" where ROWID = 'AAASPDAAE

AAAAIkAAA';

 

对于主外键这种约束造成的闪回事务间冲突

无法使用单表上闪回事务处理的那些选项

必须，在两张表上，关于要闪回的事务相关的所有有冲突的事务，同步的进行闪回

在本例中，即同时，在t_p和t_f上，进行与事务二有关的所有事务的闪回

t_f上，有一个事务，是insert 2

这个事务，依赖于事务二，所以要对该表上这个事务也进行闪回

1 declare

2 v_xid xid_array;

3 begin

4 v_xid := sys.xid_array('03001C00CF020000','05001300CB020000');

5 dbms_flashback.transaction_backout(2,v_xid);

6* end;

SQL> /

 

PL/SQL procedure successfully completed.

 

SQL> select * from scott.t_p;

 

ID NAME

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

1 a

 

SQL> select * from scott.t_f;

 

no rows selected