NOCASCADE_FORCE Mode

Last but definitely not least is the NOCASCADE_FORCE mode. It implements the following rule when backing out transactions.

Backout the desired transaction on an as-is, where-is basis.

Here’s an example:

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

PL/SQL procedure successfully completed.

-- Perform a single insert in a 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.

-- Update the inserted record in another transaction
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 record
SQL> SELECT demo_name
 2     FROM demo;

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

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.NOCASCADE_FORCE );
 8   END;
 9 /

PL/SQL procedure successfully completed.

-- Check the record
SQL> SELECT demo_name
 2    FROM demo;

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

1 row selected.

Along the way the backout process assembled the following SQL to potentially apply.

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

But it only executed this one:

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

But that record (DEMO_ID=1) no longer existed in that state (DEMO_NAME=’Demo 1′) so nothing was deleted.

Thanks for reading.

NONCONFLICT_ONLY Mode

The third mode available for backing out transactions is NOCONFLICT_ONLY mode. It implements the following rule:

Only backout transactions for records that were not touched by any subsequent transactions.

Here’s an example that inserts 2 records in one transaction and then updates one of them in another. Then an attempt is made to backout the initial insert transaction.

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

PL/SQL procedure successfully completed.

-- insert two records in a transaction
SQL> SET TRANSACTION NAME 'INSERT';

Transaction set.

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

PL/SQL procedure successfully completed.

-- Update 1 of the records in another transaction
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

SQL> UPDATE demo
 2   SET demo_name = 'Demo One'
 3   WHERE demo_id = 1;

1 row updated.

SQL> COMMIT;

Commit complete.

-- Verify the records
SQL> SELECT demo_name
 2     FROM demo;

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

-- backout the INSERT using NOCONFLICT_ONLY mode
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.NONCONFLICT_ONLY );
 8   END;
 9 /

PL/SQL procedure successfully completed.

-- Now check the records
SQL> SELECT demo_name
 2     FROM demo;

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

1 row selected.

Because the record with DEMO_ID=1 had been updated it was not processed at all by the backout. Only the record with DEMO_ID=2 was backed out.

The backout process actually generated the following SQL statements to potentially use in the backout.

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

But after applying the NONCONFLICT_ONLY mode it only executed this one.

delete from DRH.DEMO where DEMO_ID = '2' and DEMO_NAME = 'Demo 2'

Thanks for reading!

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.