Viewing Backout SQL

The SQL generated for a backout transaction is available in the USER_FLASHBACK_TXN_REPORT. It’s in XML format but here is a query I concocted to extract it.

SQL> SELECT REPLACE(EXTRACTVALUE(column_value,'/USQL'),CHR(9),'') oh_yeah
 2     FROM ( SELECT column_value
 3              FROM TABLE( SELECT XMLSEQUENCE(XMLTYPE(xid_report).EXTRACT('/COMP_XID_REPORT/TRANSACTION/UNDO_SQL/USQL')) xmlcol
 4                            FROM user_flashback_txn_report
 5                           WHERE compensating_xid = ( SELECT xid
 6                                                        FROM v$transaction )))
 7    WHERE INSTR(XMLTYPE.GETCLOBVAL(column_value),'exec="yes"') > 0
 8 /

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

There’s a lot going on here so I’ll try to explain.

1. The innermost select queries the UNDO SQL for the currently executed (but not committed) transaction. The result is in XML format
2. The XMLSEQUENCE function translates the XML into a varray of records which are passed out to the outermost select
3. The EXTRACTVALUE function extracts the UNDO_SQL (/USQL) value and displays it

Queries like this increase my respect for XML developers because that seems like a lot of work to extract some text. XML also gives me a new-found respect for the forward slash key – it must get very tired when doing XML.

Thanks for reading

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!