闪回事务
事务A <- 事务B<-事务C
nocascade （默认）什么都不做
cascade X事务CX事务BX事务A
nonconflict_only 拆解事务B 事务C， 仅仅挖掉没有后续影响的行
nocascade_forceX事务A（唯一惧怕的是约束）
--------------------------------------------------------------------------------------------
外键： 父表事务A 插入 子表基于父表做了插入 如果把事务A拿掉........???????
唯一/主键： 事务A删除一行，后续被人插入了相同，如果把事务A拿掉........??????
select* from v$database;
alter database add supplemental log data;
alter database add supplemental log data ( primary key ) columns;
select* from v$database;
alter database add supplemental log data;
alter database add supplemental log data ( primary key ) columns;
alter database add supplemental log data ( foreign key ) columns;
实验用的脚本：
undefine s1
create table &&s1( id number ) ;
! sleep 5
insert into &&s1 values (1) ;
insert into &&s1 values (2) ;
insert into &&s1 values (3) ;
commit;
update &&s1 set id=11 where id=1;
update &&s1 set id=22 where id=2;
update &&s1 set id=33 where id=3;
commit;
update &&s1 set id=222 where id=22;
update &&s1 set id=333 where id=33;
commit;
update &&s1 set id=22 where id=222;
commit;
原表：
11
22
333
-----------------------nocascade --------------------------
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 2
-----------------------nonconflict_only--------------------------
begin
dbms_flashback.transaction_backout(NUMTXNS=>1,
XIDS=> XID_ARRAY('9C002000030A0000'),
OPTIONS=>dbms_flashback.nonconflict_only);
end;
/
select* from dba_flashback_txn_state;
9B00200068030000 9C002000030A0000 NONCONFLICT_ONLY SYS
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="9B00200068030000" NAME="_SYS_COMP_TXN_1458624_TIM_1612273446">
<目标事务TRANSACTION XID="9C002000030A0000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="no">
update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAWvpAADAAAACjAAC'
</USQL>
<USQL exec="no">
update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAWvpAADAAAACjAAB'
</USQL>
<USQL exec="yes">
update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAWvpAADAAAACjAAA'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="9C001500050A0000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="no">
update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAWvpAADAAAACjAAC'
</USQL>
<USQL exec="no">
update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAWvpAADAAAACjAAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="9C001700010A0000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="no">
update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAWvpAADAAAACjAAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAWvpAADAAAACjAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
1
22
333
--------------------------------------cascade-----------------------------
begin
dbms_flashback.transaction_backout(NUMTXNS=>1,
XIDS=> XID_ARRAY('9C0019006E0A0000'),
OPTIONS=>dbms_flashback.cascade);
end;
/
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="9B001300EF030000" NAME="_SYS_COMP_TXN_612809_TIM_1612274796">
<<font color="Red">目标事务</font>TRANSACTION XID="9C0019006E0A0000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
update "HR"."T_CASCADE2" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAWv4AADAAAACvAAC'
</USQL>
<USQL exec="yes">
update "HR"."T_CASCADE2" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAWv4AADAAAACvAAB'
</USQL>
<USQL exec="yes">
update "HR"."T_CASCADE2" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAWv4AADAAAACvAAA'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="9B001600AF030000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
update "HR"."T_CASCADE2" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAWv4AADAAAACvAAC'
</USQL>
<USQL exec="yes">
update "HR"."T_CASCADE2" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAWv4AADAAAACvAAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="9D001A0009000000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
update "HR"."T_CASCADE2" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAWv4AADAAAACvAAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."T_CASCADE2" set "ID" = '222' <span style="background-color: Yellow;">where "ID" = '22' </span>and ROWID = 'AAAWv4AADAAAACvAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE2" set "ID" = '33' <span style="background-color: Yellow;">where "ID" = '333' </span>and ROWID = 'AAAWv4AADAAAACvAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE2" set "ID" = '22' <span style="background-color: Yellow;">where "ID" = '222' </span>and ROWID = 'AAAWv4AADAAAACvAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE2" set "ID" = '3' <span style="background-color: Yellow;">where "ID" = '33'</span> and ROWID = 'AAAWv4AADAAAACvAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE2" set "ID" = '2' <span style="background-color: Yellow;">where "ID" = '22'</span> and ROWID = 'AAAWv4AADAAAACvAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE2" set "ID" = '1' <span style="background-color: Yellow;">where "ID" = '11'</span> and ROWID = 'AAAWv4AADAAAACvAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
1
2
3
------------------nocascade_force------------------------
begin
dbms_flashback.transaction_backout (NUMTXNS=>1,
XIDS=> XID_ARRAY('040011000D070000'),
OPTIONS=>dbms_flashback.nocascade_force);
end;
/
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="01000F0006070000">
<TRANSACTION XID="040011000D070000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="<font color="Red">yes</font>">
update "HR"."T_CASCADE3" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAWyFAADAAAAC3AAC'
</USQL>
<USQL exec="<font color="Red">yes</font>">
update "HR"."T_CASCADE3" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAWyFAADAAAAC3AAB'
</USQL>
<USQL exec="<font color="Red">yes</font>">
update "HR"."T_CASCADE3" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAWyFAADAAAAC3AAA'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="0600030049070000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>no
<USQL exec="<font color="SeaGreen">no</font>">
update "HR"."T_CASCADE3" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAWyFAADAAAAC3AAC'
</USQL>
<USQL exec="<font color="SeaGreen">no</font>">
update "HR"."T_CASCADE3" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAWyFAADAAAAC3AAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="01000F0003070000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="<font color="SeaGreen">no</font>">
update "HR"."T_CASCADE3" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAWyFAADAAAAC3AAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '3' <span style="background-color: Yellow;">where "ID" = '33' </span>and ROWID = 'AAAWyFAADAAAAC3AAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '2' <span style="background-color: Yellow;">where "ID" = '22' </span>and ROWID = 'AAAWyFAADAAAAC3AAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '1' <span style="background-color: Yellow;">where "ID" = '11' </span>and ROWID = 'AAAWyFAADAAAAC3AAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
1
2
333
-------------------------------------------两个事务的案例
1
222
333
begin
dbms_flashback.transaction_backout (NUMTXNS=>2,
XIDS=> XID_ARRAY('0300130063070000','060003004F070000'),
OPTIONS=>dbms_flashback.nocascade_force);
end;
/
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="0400050012070000" NAME="_SYS_COMP_TXN_1682353_TIM_1612442861">
<TRANSACTION XID="060003004F070000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
update "HR"."T_CASCADE4" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAWyGAADAAAAC/AAC'
</USQL>
<USQL exec="yes">
update "HR"."T_CASCADE4" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAWyGAADAAAAC/AAB'
</USQL>
<USQL exec="yes">
update "HR"."T_CASCADE4" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAWyGAADAAAAC/AAA'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="050002000D070000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="no">
update "HR"."T_CASCADE4" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAWyGAADAAAAC/AAC'
</USQL>
<USQL exec="no">
update "HR"."T_CASCADE4" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAWyGAADAAAAC/AAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="0300130063070000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
update "HR"."T_CASCADE4" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAWyGAADAAAAC/AAB'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."T_CASCADE4" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAWyGAADAAAAC/AAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE4" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAWyGAADAAAAC/AAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE4" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAWyGAADAAAAC/AAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_CASCADE4" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAWyGAADAAAAC/AAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
页:
[1]