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.