Category: transaction-backout

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!

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.

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!