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.

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.