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.