CASCADE Mode

In my previous blog entry I introduced the NOCASCADE mode. Thus you probably guessed there would be a CASCADE mode as well. And you would be correct. I won’t show all of the SQL for the test case because that is available in the CASCADE blog entry. I’ll just highlight the change and show the results here.

SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('INSERT');
 3   BEGIN
 4     DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 5                                         names => v_names,
 6                                         scnhint => hold_stuff.v_scn,
 7                                         options => DBMS_FLASHBACK.CASCADE );
 8   END;
 9 /

PL/SQL procedure successfully completed.

SQL> SELECT demo_name cascade
 2 FROM demo;

no rows selected

Specifying CASCADE mode allowed the initial INSERT transaction to be backed out. But not in the way you might think. Here are the SQL statements used to back it out.

update DRH.DEMO set DEMO_NAME = 'Demo 1' where DEMO_ID = '1' and DEMO_NAME = 'Demo One'
delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'

The backout function worked backwards from the current state of the record (identified by the primary key – DEMO_ID) and backed out every transaction all the way back to the initial INSERT. The first backout SQL updated the DEMO_NAME back to ‘DEMO 1’ to ensure the final backout would find the record in the same state it was when the transaction originally occurred.

So be careful when doing CASCADE backouts because if the desired transaction is far back in the past the backout may take some time.

Thanks for reading.

NOCASCADE Mode

There are 4 modes that TRANSACTION_BACKOUT can be run in. The mode setting determines how the backout reacts to subsequent transactions that operate on any of the records it touches. The default mode is NOCASCADE which implements the following behaviour.

If any of the records touched by the transaction being backed out were subsequently touched by other transactions then do not perform the backout.

Not only does it not perform the backout – it errors out as shown in this example:

-- hold the current SCN
SQL> BEGIN
 2     hold_stuff.v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 3   END;
 4 /

PL/SQL procedure successfully completed.

-- define and execute an INSERT transaction
SQL> SET TRANSACTION NAME 'INSERT';

Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(1,'Demo 1');
 4     COMMIT;
 5   END;
 6 /

PL/SQL procedure successfully completed.

-- define and execute an update of the inserted record
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

SQL> UPDATE demo
 2 SET demo_name = 'Demo One';

1 row updated.

SQL> COMMIT;

Commit complete.

-- verify the updated record
SQL> SELECT demo_name
 2     FROM demo;

DEMO_NAME
 ------------------------------
 Demo One

-- attempt to backout the initial INSERT transaction
SQL> DECLARE
 2      v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('INSERT');
 3   BEGIN
 4      DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 5                                          names => v_names,
 6                                          scnhint => hold_stuff.v_scn );
 7   END;
 8 /
 DECLARE
 *
 ERROR at line 1:
 ORA-55504: Transaction conflicts in NOCASCADE mode
 ORA-06512: at "SYS.DBMS_FLASHBACK", line 51
 ORA-06512: at "SYS.DBMS_FLASHBACK", line 86
 ORA-06512: at line 4

As soon as the subsequent UPDATE transaction was discovered by the backout process it failed with the perfectly worded “Transaction conflicts in NOCASCADE mode” message.

Undo SQL

The TRANSACTION_BACKOUT feature executes SQL to backout or undo  the specific changes from the desired transactions. For example the UPDATE in the previous post is undone with this SQL statement.

update "DRH"."DEMO"
set "DEMO_NAME" = 'Demo 1'
where "DEMO_ID" = '1'
  and "DEMO_NAME" = 'Demo One';

The undo SQL identifies the specific record by including the primary key column (DEMO_ID) and ensures the correct record state with the before-update value of ‘Demo One’. This helps to ensure that changes done by other transactions are not undone as well. For example if the DEMO_NAME value had been changed to ‘Demo Demo’ by another transaction then this undo would have no effect.