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!