设为首页收藏本站

Botang唐波's Oracle Station

查看: 185|回复: 0

课程第22次(2018-03-26星期一)

[复制链接]

623

主题

966

帖子

7054

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7054
发表于 2018-3-26 19:43:16 | 显示全部楼层 |阅读模式
t05310.sql:
  1. undefine s1
  2. create table &&s1( id number ) ;
  3. ! sleep 5
  4. insert into &&s1 values (1) ;
  5. insert into &&s1 values (2) ;
  6. insert into &&s1 values (3) ;
  7. commit;
  8. update &&s1 set id=11 where id=1;
  9. update &&s1 set id=22 where id=2;
  10. update &&s1 set id=33 where id=3;
  11. commit;
  12. update &&s1 set id=222 where id=22;
  13. update &&s1 set id=333 where id=33;
  14. commit;
  15. update &&s1 set id=22 where id=222;
  16. commit;
复制代码

闪回事务又名撤销事务(Transaction Backout),能够撤销一个或多个事务的修改,其功能由一个名为DBMS_FLASHBACK.TRANSACTION_BACKOUT的存储过程实现。该存储过程的工作原理是自动分析重做日志,挖掘出变更前的值用以构建撤销SQL(Undo SQL),然后执行撤销SQL最后达到撤销事务的目的。

为了该功能可以正常使用,至少需要事先启用主键补充日志。另外,为了能够跟踪外键依赖还需要启用外键补充日志。

在继续讨论此功能前,首先应了解一个概念:事务的依赖性。比如,两个事务TX1和TX2,若符合以下3个条件的任意一个就可以认为TX2依赖TX1:

(1)WAW依赖(Write After Write),即在TX1修改了表的某行之后,TX2又修改了同一行。

(2)主键依赖,即在一张拥有主键的表中TX1首先删除了一行,之后TX2又插入了具有相同主键值的另一行。

(3)外建依赖,即由于TX1的修改(insert或update)而产生了新的可被外键参考的字段值,之后TX2修改(insert或update)外键字段时利用了TX1所产生的字段值。

  1. SQL> desc dbms_flashback
  2. PROCEDURE DISABLE
  3. PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
  4. Argument Name                        Type                        In/Out Default?
  5. ------------------------------ ----------------------- ------ --------
  6. QUERY_SCN                        NUMBER                        IN
  7. PROCEDURE ENABLE_AT_TIME
  8. Argument Name                        Type                        In/Out Default?
  9. ------------------------------ ----------------------- ------ --------
  10. QUERY_TIME                        TIMESTAMP                IN
  11. FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
  12. PROCEDURE TRANSACTION_BACKOUT
  13. Argument Name                        Type                        In/Out Default?
  14. ------------------------------ ----------------------- ------ --------
  15. NUMTXNS                        NUMBER                        IN
  16. XIDS                                XID_ARRAY                IN
  17. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  18. SCNHINT                        NUMBER                        IN     DEFAULT
  19. PROCEDURE TRANSACTION_BACKOUT
  20. Argument Name                        Type                        In/Out Default?
  21. ------------------------------ ----------------------- ------ --------
  22. NUMTXNS                        NUMBER                        IN
  23. XIDS                                XID_ARRAY                IN
  24. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  25. TIMEHINT                        TIMESTAMP                IN
  26. PROCEDURE TRANSACTION_BACKOUT
  27. Argument Name                        Type                        In/Out Default?
  28. ------------------------------ ----------------------- ------ --------
  29. NUMTXNS                        NUMBER                        IN
  30. NAMES                                TXNAME_ARRAY                IN
  31. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  32. SCNHINT                        NUMBER                        IN     DEFAULT
  33. PROCEDURE TRANSACTION_BACKOUT
  34. Argument Name                        Type                        In/Out Default?
  35. ------------------------------ ----------------------- ------ --------
  36. NUMTXNS                        NUMBER                        IN
  37. NAMES                                TXNAME_ARRAY                IN
  38. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  39. TIMEHINT                        TIMESTAMP                IN

  40. SQL>
复制代码

TRANSACTION_BACKOUT存储过程的OPTIONS参数就是为了解决事务依赖性问题而存在的,在该参数上管理员可以使用4种撤销事务的方案,假设被撤销的事务是TX1,若其具有依赖事务,则称为TX2:

(1)NOCASCADE,TX1不可以被任何其他事务依赖(即TX2不存在),否则撤销操作报错。

(2)CASCADE,将TX1连同TX2一起撤销。

(3)NOCASCADE_FORCE,忽略TX2,直接执行TX1的撤销SQL将TX1撤销,如果没有约束上的冲突,操作将成功,否则约束报错导致撤销操作失败。

(4破坏事务TX1的原子性)NONCONFILICT_ONLY,在不影响TX2的前提下,撤销TX1的修改。与NOCASCADE_FORCE的不同点在于会首先过滤一下TX1的撤销SQL(补偿事务),确保它们不会作用在TX2修改的行上。

  1. 若采用NOCASCADE结果是抛出错误“ORA-55504: Transaction conflicts in NOCASCADE mode”,表内容依然是:

  2. ID
  3. ------
  4.   11
  5.   22
  6. 333
复制代码
NOCASCADE:
  1. select  *  from flashback_transaction_query
  2. where table_name='T_NOCASCADE'  and logon_user='HR'  order by 2 ;

  3. begin
  4.   dbms_flashback.transaction_backout(numtxns => 1,
  5.                                                                 xids =>  xid_array('0E0007002C000000'),
  6.                                                                 options => dbms_flashback.nocascade);
  7. end;
复制代码
CASCADE:
报告的过程:
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="0F0017002C000000">
  3.         <TRANSACTION XID="13000D002D000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="yes">
  8.                  update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQAAAEAAAAIkAAC'
  9.                 </USQL>
  10.                 <USQL exec="yes">
  11.                  update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  12.                 </USQL>
  13.                 <USQL exec="yes">
  14.                  update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQAAAEAAAAIkAAA'
  15.                 </USQL>
  16.         </UNDO_SQL>
  17.         <DEPENDENT_XIDS>
  18.                 <TRANSACTION XID="0E0004002D000000">
  19.                 <CHARACTERISTICS>
  20.                 </CHARACTERISTICS>
  21.                 <UNDO_SQL>
  22.                         <USQL exec="yes">
  23.                          update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQAAAEAAAAIkAAC'
  24.                         </USQL>
  25.                         <USQL exec="yes">
  26.                          update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQAAAEAAAAIkAAB'
  27.                         </USQL>
  28.                 </UNDO_SQL>
  29.                 <DEPENDENT_XIDS>
  30.                         <TRANSACTION XID="110019002C000000">
  31.                         <CHARACTERISTICS>
  32.                         </CHARACTERISTICS>
  33.                         <UNDO_SQL>
  34.                                 <USQL exec="yes">
  35.                                  update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  36.                                 </USQL>
  37.                         </UNDO_SQL>
  38.                         <DEPENDENT_XIDS>
  39.                         </DEPENDENT_XIDS>
  40.                         </TRANSACTION>
  41.                 </DEPENDENT_XIDS>
  42.                 </TRANSACTION>
  43.         </DEPENDENT_XIDS>
  44.         </TRANSACTION>
  45. <EXECUTED_UNDO_SQL>
  46. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  47. </EXEC_USQL>
  48. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQAAAEAAAAIkAAC'
  49. </EXEC_USQL>
  50. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQAAAEAAAAIkAAB'
  51. </EXEC_USQL>
  52. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQAAAEAAAAIkAAC'
  53. </EXEC_USQL>
  54. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  55. </EXEC_USQL>
  56. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQAAAEAAAAIkAAA'
  57. </EXEC_USQL>
  58. </EXECUTED_UNDO_SQL>
  59. </COMP_XID_REPORT>
复制代码
  1. select  *  from flashback_transaction_query
  2. where table_name='T_CASCADE'  and logon_user='HR'  order by 2 ;

  3. begin
  4.   dbms_flashback.transaction_backout(numtxns => 1,
  5.                                                                 xids =>  xid_array('13000D002D000000'),
  6.                                                                 options => dbms_flashback.cascade);
  7. end;

  8. select  * from hr.t_cascade;

  9. select  * from dba_flashback_txn_report;
  10.       
  11. commit;      
复制代码
NOCASCADE_FORCE:
报告:
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="120014002E000000" NAME="_SYS_COMP_TXN_5112210_TIM_1522066588">
  3.         <TRANSACTION XID="0D0010002F000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="yes">
  8.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQBAAEAAAAIsAAC'
  9.                 </USQL>
  10.                 <USQL exec="yes">
  11.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQBAAEAAAAIsAAB'
  12.                 </USQL>
  13.                 <USQL exec="yes">
  14.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQBAAEAAAAIsAAA'
  15.                 </USQL>
  16.         </UNDO_SQL>
  17.         <DEPENDENT_XIDS>
  18.                 <TRANSACTION XID="100001002E000000">
  19.                 <CHARACTERISTICS>
  20.                 </CHARACTERISTICS>
  21.                 <UNDO_SQL>
  22.                         <USQL exec="no">
  23.                          update "HR"."T_NOCASCADE_FORCE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQBAAEAAAAIsAAC'
  24.                         </USQL>
  25.                         <USQL exec="no">
  26.                          update "HR"."T_NOCASCADE_FORCE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQBAAEAAAAIsAAB'
  27.                         </USQL>
  28.                 </UNDO_SQL>
  29.                 <DEPENDENT_XIDS>
  30.                         <TRANSACTION XID="110018002D000000">
  31.                         <CHARACTERISTICS>
  32.                         </CHARACTERISTICS>
  33.                         <UNDO_SQL>
  34.                                 <USQL exec="no">
  35.                                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQBAAEAAAAIsAAB'
  36.                                 </USQL>
  37.                         </UNDO_SQL>
  38.                         <DEPENDENT_XIDS>
  39.                         </DEPENDENT_XIDS>
  40.                         </TRANSACTION>
  41.                 </DEPENDENT_XIDS>
  42.                 </TRANSACTION>
  43.         </DEPENDENT_XIDS>
  44.         </TRANSACTION>
  45. <EXECUTED_UNDO_SQL>
  46. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQBAAEAAAAIsAAC'
  47. </EXEC_USQL>
  48. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQBAAEAAAAIsAAB'
  49. </EXEC_USQL>
  50. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQBAAEAAAAIsAAA'
  51. </EXEC_USQL>
  52. </EXECUTED_UNDO_SQL>
  53. </COMP_XID_REPORT>
复制代码
  1. select  *  from flashback_transaction_query
  2. where table_name='T_NOCASCADE_FORCE'  and logon_user='HR'  order by 2 ;

  3. begin
  4.   dbms_flashback.transaction_backout(numtxns => 1,
  5.                                                                 xids =>  xid_array('0D0010002F000000'),
  6.                                                                 options => dbms_flashback.nocascade_force);
  7. end;

  8. select  * from hr.t_nocascade_force;

  9. select  * from dba_flashback_txn_report;
  10.       
  11. commit;     
复制代码
NONCONFLICT_ONLY:
报告:
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="0C0010002E000000" NAME="_SYS_COMP_TXN_5112210_TIM_1522067033">
  3.         <TRANSACTION XID="1400130032000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="no">
  8.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQCAAEAAAAI0AAC'
  9.                 </USQL>
  10.                 <USQL exec="no">
  11.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQCAAEAAAAI0AAB'
  12.                 </USQL>
  13.                 <USQL exec="yes">
  14.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQCAAEAAAAI0AAA'
  15.                 </USQL>
  16.         </UNDO_SQL>
  17.         <DEPENDENT_XIDS>
  18.                 <TRANSACTION XID="0D00170030000000">
  19.                 <CHARACTERISTICS>
  20.                 </CHARACTERISTICS>
  21.                 <UNDO_SQL>
  22.                         <USQL exec="no">
  23.                          update "HR"."T_NONCONFLICT_ONLY" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQCAAEAAAAI0AAC'
  24.                         </USQL>
  25.                         <USQL exec="no">
  26.                          update "HR"."T_NONCONFLICT_ONLY" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQCAAEAAAAI0AAB'
  27.                         </USQL>
  28.                 </UNDO_SQL>
  29.                 <DEPENDENT_XIDS>
  30.                         <TRANSACTION XID="0F001F0031000000">
  31.                         <CHARACTERISTICS>
  32.                         </CHARACTERISTICS>
  33.                         <UNDO_SQL>
  34.                                 <USQL exec="no">
  35.                                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQCAAEAAAAI0AAB'
  36.                                 </USQL>
  37.                         </UNDO_SQL>
  38.                         <DEPENDENT_XIDS>
  39.                         </DEPENDENT_XIDS>
  40.                         </TRANSACTION>
  41.                 </DEPENDENT_XIDS>
  42.                 </TRANSACTION>
  43.         </DEPENDENT_XIDS>
  44.         </TRANSACTION>
  45. <EXECUTED_UNDO_SQL>
  46. <EXEC_USQL>update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQCAAEAAAAI0AAA'
  47. </EXEC_USQL>
  48. </EXECUTED_UNDO_SQL>
  49. </COMP_XID_REPORT>
复制代码
  1. select  *  from flashback_transaction_query
  2. where table_name='T_NONCONFLICT_ONLY'  and logon_user='HR'  order by 2 ;

  3. begin
  4.   dbms_flashback.transaction_backout(numtxns => 1,
  5.                                                                 xids =>  xid_array('1400130032000000'),
  6.                                                                 options => dbms_flashback.nonconflict_only);
  7. end;

  8. select  * from hr.t_nonconflict_only;

  9. select  * from dba_flashback_txn_report;
  10.       
  11. commit;                                                   
复制代码

主键(插入-删除-再插入):





  1. SQL> create table t05310_b ( a  number  constraint pk_t05310_b primary key  ) ;

  2. Table created.

  3. SQL> insert into t05310_b  values ( 1 )  ;  

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> delete from t05310_b where a=1;

  8. 1 row deleted.

  9. SQL> commit;

  10. Commit complete.

  11. SQL> insert into t05310_b  values (1) ;

  12. 1 row created.

  13. SQL> commit;

  14. Commit complete.

  15. SQL>
复制代码
用NOCASCADE_FORCE(只怕约束):
Screenshot-Error.png

如果用NONCONFLICT_ONLY什么也没做:
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="1000180030000000">
  3.         <TRANSACTION XID="12001A0030000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="no">
  8.                  insert into "HR"."T05310_B"("A") values ('1')
  9.                 </USQL>
  10.         </UNDO_SQL>
  11.         <DEPENDENT_XIDS>
  12.                 <TRANSACTION XID="1000090030000000">
  13.                 <CHARACTERISTICS>
  14.                 </CHARACTERISTICS>
  15.                 <UNDO_SQL>
  16.                         <USQL exec="no">
  17.                          delete from "HR"."T05310_B" where "A" = '1'
  18.                         </USQL>
  19.                 </UNDO_SQL>
  20.                 <DEPENDENT_XIDS>
  21.                 </DEPENDENT_XIDS>
  22.                 </TRANSACTION>
  23.         </DEPENDENT_XIDS>
  24.         </TRANSACTION>
  25. <EXECUTED_UNDO_SQL>
  26. </EXECUTED_UNDO_SQL>
  27. </COMP_XID_REPORT>
复制代码

外键:

  1. SQL> create table t05310_c ( a  number constraint pk_t05310_c primary key ) ;

  2. Table created.

  3. SQL> insert into t05310_c values ( 1 ) ;

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> create table t05310_d ( a number constraint fk_t05310_d references t05310_c ) ;

  8. Table created.

  9. SQL> update t05310_c set a=100 where a=1;

  10. 1 row updated.

  11. SQL> commit;

  12. Commit complete.

  13. SQL> insert into t05310_d values ( 1) ;
  14. insert into t05310_d values ( 1)
  15. *
  16. ERROR at line 1:
  17. ORA-02291: integrity constraint (HR.FK_T05310_D) violated - parent key not
  18. found


  19. SQL> insert into t05310_d values ( 100) ;

  20. 1 row created.

  21. SQL> commit;

  22. Commit complete.

  23. SQL>
复制代码

Screenshot-Error-1.png
以上错误是因为没有外键补充日志。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-4-20 15:01 , Processed in 0.165250 second(s), 27 queries .

快速回复 返回顶部 返回列表