Posted on

Transaction Backout Does Not Do JSON Part 2

I did some digging on this issue and found the problem was caused when Transaction Backout executed the following statement which failed:

SQL> INSERT INTO json
 2   VALUES(EMPTY_CLOB());
INSERT INTO json
*
ERROR at line 1:
ORA-02290: check constraint (DRH.KEEP_IT_REAL) violated

At this point I wondered where the EMPTY_CLOB insert was coming from so I added an INSERT trigger to catch the values:

CREATE OR REPLACE TRIGGER check
BEFORE INSERT ON json
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO log
 VALUES(:new.always_json);
 COMMIT;
END;

And sure enough a NULL value (EMPTY_CLOB) wound up in the LOG table.

I was now curious if this was a bug or an actual unsupported feature. I got my answer when I tried to backout this INSERT statement.

INSERT INTO json
VALUES(
'
{
"plch_id" : "1",
"plch_desc" : "One"
}'
);

It raised this error:

ORA-55506: Transaction performed unsupported change

And a quick check of Oracle’s Error Message Guide revealed this:

ORA-55506: Transaction performed unsupported change
Cause: A transaction in the dependency DAG performed some
Action: The specified transaction cannot be backed out.

No, that is not a cut and paste error – the Cause is truncated. I assume it was intended to say “…some unsupported DML.”

The Action makes it official though – Transaction Backout does NOT JSON.

Thanks for reading!

PS: DAG is something called a Directed Acyclic Graph. I may blog about that later.

Posted on

Transaction Backout Does Not Do JSON

I was testing transaction backout with non-scalar datatypes. And since I’m doing JSON research I decided to try JSON columns. I discovered that transaction backout does not work with JSON. Not sure just why yet. Here is the test code I used.

SQL> CREATE TABLE JSON
 2   ( always_json CLOB
 3     CONSTRAINT keep_it_real
 4*      CHECK ( always_json IS JSON ) )
Table created.

SQL> INSERT INTO json
 2   VALUES(
 3   '
 4   {
 5      "plch_id" : "1",
 6      "plch_desc" : "One"
 7   }'
 8   );
1 row created.

SQL> CREATE OR REPLACE PACKAGE hold_stuff AS
 2     v_scn NUMBER;
 3   END;
 4   /
Package created.

SQL> BEGIN
 2     hold_stuff.v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 3   END;
 4   /
PL/SQL procedure successfully completed.

SQL> SET TRANSACTION NAME 'DELETE';
Transaction set.

SQL> DELETE json;
1 row deleted.

SQL> COMMIT;
Commit complete.

SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('DELETE');
 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-55511: Flashback Transaction experienced error in executing undo SQL
ORA-02290: check constraint (ORA-02290: check constraint (DRH.KEEP_IT_REAL)
violated
.) violated
ORA-06512: at "SYS.DBMS_FLASHBACK", line 51
ORA-06512: at "SYS.DBMS_FLASHBACK", line 86
ORA-06512: at line 4

I'm unable to see what Oracle used for the undo operation (an INSERT) but it will be interesting once I find it.

Thanks for reading!
Posted on

Transaction Backout Is Not Picky

All of the examples I’ve shown so far on this blog rely on transaction names to specify what to backout. This is simpler than specifying transaction ID’s. But it does have potential drawback. It backs out ALL discovered. Here is an example that executes single INSERTs in 3 separate transactions – notice the transaction names used.

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.

SQL> COMMIT;
Commit complete.

SQL> SET TRANSACTION NAME 'BOB';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(2,'Demo 2');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

SQL> SET TRANSACTION NAME 'INSERT';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(3,'Demo 3');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;

What will happen when I backout the transaction (or transactionS) named INSERT?

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.CASCADE );
 8   END;

What records are left in the DEMO table?

SQL> SELECT *
 2     FROM demo;

DEMO_ID    DEMO_NAME
---------- ---------
         2 Demo 2

Both of the INSERT transactions were backed out. Here is the SQL used for the backout.

delete from DRH.DEMO where DEMO_ID = '3' and DEMO_NAME = 'Demo 3'
delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'

Beware of this behavior! Or. conversely; embrace it in your applications. I’ll discuss more about that in future entries.

Thanks for reading!