Transaction Backout Is Not Picky

All of the examples I’ve shown so far on this blog rely on transaction names to specify what to backout. This is simpler than specifying transaction ID’s. But it does have potential drawback. It backs out ALL discovered. Here is an example that executes single INSERTs in 3 separate transactions – notice the transaction names used.

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.

SQL> COMMIT;
Commit complete.

SQL> SET TRANSACTION NAME 'BOB';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(2,'Demo 2');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

SQL> SET TRANSACTION NAME 'INSERT';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(3,'Demo 3');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;

What will happen when I backout the transaction (or transactionS) named INSERT?

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;

What records are left in the DEMO table?

SQL> SELECT *
 2     FROM demo;

DEMO_ID    DEMO_NAME
---------- ---------
         2 Demo 2

Both of the INSERT transactions were backed out. Here is the SQL used for the backout.

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

Beware of this behavior! Or. conversely; embrace it in your applications. I’ll discuss more about that in future entries.

Thanks for reading!