Answer: Backout And Constraints

And here are the results. Two modes raised the error during backout and 2 succeeded.

NOCASCADE
Raised Error

NOCASCADE_FORCE
Raised Error

CASCADE
No Error
2 Records Afterwards
DEMO_ID DEMO_NAME
---------- ---------
 2 Demo 2
 1 Demo 1
SQL Executed
delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'
insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('2','Demo 2')
insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('1','Demo 1')
Explanation
Because this is cascade mode it starts with the latest transaction - the re-insert of record 1.
This is backed out with the delete SQL. Then the delete transaction is backed out with 2 inserts.

NONCONFLICT_ONLY
No Error
2 Records Afterwards
DEMO_ID DEMO_NAME
---------- ---------
1 Demo 1
2 Demo 2
SQL Executed
insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('2','Demo 2')
Explanation
This much simpler - it just inserts the row with DEMO_ID=2.

How many did you get right?

Thanks for reading!

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