NOCASCADE_FORCE vs NONCONFLICT_ONLY

In this post I’ll explain how values that get updated back to how the look at the originating transaction are handled. I’ll use three transactions – one inserts a record then a second transaction updates it and then – this is the important part – a third transaction updates the record back to the originally inserted values.

Here are the SQL transactions:

-- Insert Demo 1
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 to Demo One
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

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

1 row updated.

SQL> COMMIT;

-- Update back to Demo 1
SQL> SET TRANSACTION NAME 'UPDATE2';

Transaction set.

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

1 row updated.

SQL> COMMIT;

And here is the backout in NOCASCADE_FORCE 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.NOCASCADE_FORCE );
 8 END;
 9 /

And the results:

SQL> SELECT demo_name
 2     FROM demo;

no rows selected

And the backout in NONCONFLICT_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 /

And the results:

SQL> SELECT demo_name
 2     FROM demo;

DEMO_NAME
---------------------
Demo 1

Both methods assembled the following SQL statements to potentially use for the backout:

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'
update DRH.DEMO set DEMO_NAME = 'Demo One' where "DEMO_ID = '1' and DEMO_NAME = 'Demo 1'

Here is where these two modes diverge – NONCONFLICT_ONLY did not actually execute any of these SQL statements because of the row conflict between the INSERT and the first UPDATE.┬áThe NOCASCADE_FORCE mode however simply tried to delete the inserted row (DEMO_ID=1 and DEMO__NAME=Demo 1) with the first SQL:

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

This just happened to match how the row looked after both updates so the record was deleted. Be very careful of this when backing out transactions.

Thanks for reading!