Quiz: Backout And Constraints

When backing out transactions from the past using undo SQL statement it is important to consider the current data – especially if the undo SQL violates database constraints.

Consider this example scenario that inserts 2 records in one transaction and then deletes them both in a second transaction. Then in a third transaction it re-inserts the first row.

What will happen when we try to backout the deletion (second transaction)?

-- insert 2 records
SET TRANSACTION NAME 'INSERT';
INSERT INTO demo
VALUES(1,'Demo 1');
INSERT INTO demo
VALUES(2,'Demo 2');
COMMIT;

-- delete both records
-- this is the transaction to be backed out later
SET TRANSACTION NAME 'DELETE';
DELETE demo;
COMMIT;

-- insert the first record again
SET TRANSACTION NAME 'INSERT2';
INSERT INTO demo
VALUES(1,'Demo 1');
COMMIT;

Will the backout fail when it tries to re-insert the row with DEMO_ID=1 because it violates the primary key constraint. Will it throw this error message?

ERROR at line 1:
ORA-55511: Flashback Transaction experienced error in executing undo SQL
ORA-00001: unique constraint (ORA-00001: unique constraint (DRH.SYS_C0010742) violated.) violated
ORA-06512: at "SYS.DBMS_FLASHBACK", line 51
ORA-06512: at "SYS.DBMS_FLASHBACK", line 86
ORA-06512: at line 4

It’s interesting to note the extra “violated” at the end of the message.

Is the error message thrown by all 4 modes of backout transaction? How many rows will be in the table afterwards? I’ll give you a hint – its not always 1 row.

DBMS_FLASHBACK.NOCASCADE        = error message? 1 row?
DBMS_FLASHBACK.NOCASCADE_FORCE  = error message? 1 row?
DBMS_FLASHBACK.NONCONFLICT_ONLY = error message? 1 row?
DBMS_FLASHBACK.CASCADE          = error message? 1 row?

In my next blog post I’ll explain the answer.

Thanks for reading.

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!