11 January 2012

Serializable Transaction Impact Not Seen by Players (9622)

The 10 January quiz tested your knowledge of serializable transactions and system change numbers. Several players ran the verification code and got "b = 0" for the second and fourth choices (9005 and 9007), which would have made them correct (they were marked as incorrect). Here's the report from one player:

I checked your verification code for the yesterday challenge about isolation level. On my database (Ora 10.2.0.4-64) the choices 9005 and 9007 are working fine and the output is "b = 0". If you run the verification code without the choices 9004 and 9006 it runs without error. Here is my testcase:
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 16:03:28 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table plch_test (a number, b number);
Table created.

SQL> begin
  insert into plch_test values (1, 0);
  insert into plch_test values (2, 0);
  commit;
end;
/ 

PL/SQL procedure successfully completed.

SQL> declare
  b number;
  2    3    procedure xxx
  4    is
  5      pragma autonomous_transaction;
  6    begin
  7      set transaction isolation level read committed;
  8       update plch_test
  9          set b = 1
 10        where a = 2;
 11        commit;
 12        select ORA_ROWSCN into b from plch_test
 13        where a = 1;
 14        dbms_output.put_line('ORA_ROWSCN_XXX = '||b);
 15    end;
 16  begin
 17    select ORA_ROWSCN into b from plch_test
 18    where a = 1;
 19    dbms_output.put_line('ORA_ROWSCN_start = '||b);
 20    set transaction isolation level serializable;
 21    dbms_lock.sleep(10);
 22    xxx;
 23    select ORA_ROWSCN into b from plch_test
 24    where a = 1 for update;
 25    dbms_output.put_line('ORA_ROWSCN_end = '||b);
 26  exception
 27    when others then
 28      dbms_output.put_line('Error');
 29  end;
 30  /
ORA_ROWSCN_start = 855358236
ORA_ROWSCN_XXX = 855358246
ORA_ROWSCN_end = 855358236
As you see, the SCN of the autonomous transaction is higher than the SCN from the select for update at the end. What is your explanation of this?

I have asked, _Nikotin, the author of the quiz to do some research and post his reply here.

10 January 2012

Exploring Mutating Table Errors and FORALL (9619)

The 5 January quiz tested players' knowledge of the fact that the mutating table error (ORA-04091) is raised differently for different ways of performing inserts and with a BEFORE row-level trigger.

Iudith Mentzel took the quiz as a starting point for some very interesting analysis, which I share here.

Hello Steven,

Following the quiz from January 5 about the mutating table error (ORA-04091), there was something in the explanation that arose my curiosity, so I tested it out and found something "half-strange".

Namely, it is the explanation of the correct choice [8740] that says the following:

"Oracle does not raise the mutating table error for the first row inserted. When it attempts to insert the row for the second element in the collection, the mutating table error is raised."

I performed the test below to prove that this is indeed the case and found the following:

CREATE TABLE plch_parts (
   partnum    NUMBER
 , partname   VARCHAR2 (30)
)
/

Table created.

CREATE OR REPLACE TRIGGER plch_parts_bir
   BEFORE INSERT
   ON plch_parts
   FOR EACH ROW
DECLARE
   cnt   NUMBER;
BEGIN
   -- just a control message
   DBMS_OUTPUT.put_line('BEFORE ROW trigger fired for '|| TO_CHAR(:new.partnum) );
   SELECT COUNT (*) INTO cnt FROM plch_parts;
END;
/

Trigger created.

/*
   Here we see that the mutating error happened indeed on the 2-nd row only,
   but it caused a rollback of the 1-st inserted row as well.
   This is usually NOT the case in a FORALL statement failure (for some other error),
   the results of the previous successful iterations are (generally) NOT rolled back
*/

DECLARE
   TYPE plch_parts_t IS TABLE OF plch_parts%ROWTYPE
                           INDEX BY PLS_INTEGER;
   t   plch_parts_t;
   cnt  NUMBER;
BEGIN
   t (1).partnum := 1;
   t (1).partname := 'A';
   t (2).partnum := 2;
   t (2).partname := 'B';

   FORALL i IN INDICES OF t
      INSERT INTO plch_parts
           VALUES t (i);
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(SQLERRM);
        
          /* if the row inserted by the first iteration is not rolled back
             then here we should see "COUNT=1" */

          SELECT COUNT(*) INTO cnt FROM plch_parts;
          DBMS_OUTPUT.put_line('COUNT='||cnt);
END;
/

BEFORE ROW trigger fired for 1
BEFORE ROW trigger fired for 2

ORA-04091: table SCOTT.PLCH_PARTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PLCH_PARTS_BIR", line 7
ORA-04088: error during execution of trigger 'SCOTT.PLCH_PARTS_BIR'
COUNT=0  =====>  this is strange  !!!

PL/SQL procedure successfully completed.

/*
   If we add a SAVE EXCEPTIONS , then the 1-st inserted row is NOT rolled back
   which is the expected behavior.

   However, the error displayed by SQLERRM is ORA-04091 and not the usual ORA-24381,
   which shows that in this case the entire FORALL is handled like a "single multirow INSERT",
   and not like an "array of (separate) INSERTS", as FORALL usually behaves.

   In spite of this, it does preserve the 1-st row inserted,
   so it only behaves "partially" as a FORALL ... SAVE EXCEPTIONS statement.
*/

DECLARE
   TYPE plch_parts_t IS TABLE OF plch_parts%ROWTYPE
                           INDEX BY PLS_INTEGER;

   t   plch_parts_t;
   cnt  NUMBER;
BEGIN
   t (1).partnum := 1;
   t (1).partname := 'A';
   t (2).partnum := 2;
   t (2).partname := 'B';

   FORALL i IN INDICES OF t SAVE EXCEPTIONS
      INSERT INTO plch_parts
           VALUES t (i);
EXCEPTION
     WHEN OTHERS THEN
          /* here we expect ORA-24381, and not ORA-04091,
             if the later is raised for the 2-nd row */
          DBMS_OUTPUT.put_line(SQLERRM);

          /* if the row inserted by the first iteration is not rolled back
             then here we should see "COUNT=1" */

          SELECT COUNT(*) INTO cnt FROM plch_parts;

          DBMS_OUTPUT.put_line('COUNT='||cnt);
          DBMS_OUTPUT.put_line('ERRORS='||SQL%BULK_EXCEPTIONS.COUNT);

          FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
          LOOP
             DBMS_OUTPUT.put_line(
'ERROR('||i||')='||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                 '( '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' )' );
          END LOOP;

END;
/

BEFORE ROW trigger fired for 1
BEFORE ROW trigger fired for 2

ORA-04091: table SCOTT.PLCH_PARTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PLCH_PARTS_BIR", line 7
ORA-04088: error during execution of trigger 'SCOTT.PLCH_PARTS_BIR'

COUNT=1  =====> this is expected, but strange for a non-ORA-24381 error !

ERRORS=1
ERROR(1)=2( 4091 )

PL/SQL procedure successfully completed.

I checked the above in both 11.1.0.7.0 and 11.2.0.1.0 and the behavior is the same. I wonder whether there are other cases for which we can see something similar.