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.

Undo SQL

The TRANSACTION_BACKOUT feature executes SQL to backout or undo  the specific changes from the desired transactions. For example the UPDATE in the previous post is undone with this SQL statement.

update "DRH"."DEMO"
set "DEMO_NAME" = 'Demo 1'
where "DEMO_ID" = '1'
  and "DEMO_NAME" = 'Demo One';

The undo SQL identifies the specific record by including the primary key column (DEMO_ID) and¬†ensures the correct record state with the before-update value of ‘Demo One’. This helps to ensure that changes done by other transactions are not undone as well. For example if the DEMO_NAME value had been changed to ‘Demo Demo’ by another transaction then this undo would have no effect.

Transaction Backout Introduction

Oracle’s TRANSACTION_BACKOUT function does just what it’s name implies – it backs out committed transactions. Here is an example:

-- Create a simple demo table
SQL> CREATE TABLE demo
 2 ( demo_id NUMBER NOT NULL PRIMARY KEY,
 3   demo_name VARCHAR2(30) NOT NULL UNIQUE );

Table created.

-- Insert a record
SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(1,'Demo 1');
 4     COMMIT;
 5   END;
/

PL/SQL procedure successfully completed.

-- Create package to hold the SCN value later
SQL> CREATE OR REPLACE PACKAGE hold_stuff AS
 2     v_scn NUMBER;
 3   END;
 4 /

Package created.

-- Save the current SCN into the package value
SQL> BEGIN
 2     hold_stuff.v_scn := 
 3        DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 4 END;
 5 /

PL/SQL procedure successfully completed.

-- Set the name of the transaction for later backout
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

-- Update the record in the table and commit
SQL> UPDATE demo
 2   SET demo_name = 'Demo One';

1 row updated.

SQL> COMMIT;

Commit complete.

-- Verify the demo name has been changed
SQL> SELECT demo_name
 2     FROM demo;

DEMO_NAME
------------------------------
Demo One

-- Now backout the update transaction using the
-- transaction name and SN value
SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY :=
 3        SYS.TXNAME_ARRAY('UPDATE');
 4 BEGIN
 5   DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 6                     names => v_names,
 7                     scnhint => hold_stuff.v_scn );
 8 END;
 9 /

PL/SQL procedure successfully completed.

-- Verify the demo name was changed
SQL> SELECT demo_name
 2 FROM demo;

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

This is just an introduction. In the next few blog entries I’ll explain the functionality in much more detail.

Thanks for reading.