1 Reply Latest reply: Jun 15, 2011 9:57 AM by Ben Speckhard-Oracle RSS

    EnableVersioning causes serializable transaction to fail.

    866478
      I was wondering if anybody could provide any insight into why as soon as I EnableVersioning I get ORA-08177 errors on the exact same transaction that succeeds immediately beforehand. This is in a test database where there are no other users or sessions accessing my schema. The Oracle version is 11.1.7.0 on 64-bit Solaris host.

      To wit:

      -- First the Successful case with versioning disabled.

      SQL> EXECUTE DBMS_WM.DisableVersioning ('cmpnt_type, cmpnt_type_owners, cmpnt, cmpnt_owners, cmpnt_type_prop, cmpnt_prop, cmpnt_type_prop_def, cmpnt_type_prop_dom, cmpnt_type_prop_dim, cmpnt_type_prop_req, cmpnt_type_prop_owners, cmpnt_type_prop_cats, category_sets, cmpnt_prop_fk_cmpnt, cmpnt_prop_bool, cmpnt_prop_integer, cmpnt_prop_float, cmpnt_prop_string, cmpnt_prop_date, cmpnt_prop_blob, zones, zone_links, segments');

      PL/SQL procedure successfully completed.

      SQL> insert into cmpnt_type_prop select cmpnt_type_prop_id.nextval, 94000, 'Bogus', 'float',null,null,null,null ,0 from dual;

      1 row created.

      SQL> select cmpnt_type_prop_id from cmpnt_type_prop where name='Bogus';

      CMPNT_TYPE_PROP_ID

      2100

      SQL> commit;

      Commit complete.

      SQL> set transaction isolation level serializable;

      Transaction set.

      SQL> insert into cmpnt_prop (cmpnt_type_prop_id, cmpnt_id, set_by, modify_date,comments)
      2 select 2100, cmpnt_id, 'theo',sysdate,'auto set' from cmpnt
      3 where cmpnt_type_id in
      4 (select cmpnt_type_id from cmpnt_type START WITH cmpnt_type_id=94000 CONNECT BY parent_cmpnt_type_id= PRIO
      R cmpnt_type_id) and not exists (select cmpnt_id from cmpnt_prop where cmpnt_id=cmpnt.cmpnt_id and cmpnt_type_p
      rop_id=2100);

      2514 rows created.

      SQL> rollback;

      Rollback complete.


      -- Then the failure case as soon as versioning is enabled.

      SQL> EXECUTE DBMS_WM.EnableVersioning ('cmpnt_type, cmpnt_type_owners, cmpnt, cmpnt_owners, cmpnt_type_prop, cmpnt_prop, cmpnt_type_prop_def, cmpnt_type_prop_dom, cmpnt_type_prop_dim, cmpnt_type_prop_req, cmpnt_type_prop_owners, cmpnt_type_prop_cats, category_sets, cmpnt_prop_fk_cmpnt, cmpnt_prop_bool, cmpnt_prop_integer, cmpnt_prop_float, cmpnt_prop_string, cmpnt_prop_date, cmpnt_prop_blob, zones, zone_links, segments', 'VIEW_WO_OVERWRITE');

      PL/SQL procedure successfully completed.


      SQL> insert into cmpnt_type_prop select cmpnt_type_prop_id.nextval, 94000, 'Bogus2', 'float',null,null,null,null ,0 from dual;

      1 row created.

      SQL> select cmpnt_type_prop_id from cmpnt_type_prop where name='Bogus2';

      CMPNT_TYPE_PROP_ID
      2101


      SQL> commit;

      Commit complete.

      SQL> set transaction isolation level serializable;

      Transaction set.

      SQL> insert into cmpnt_prop (cmpnt_type_prop_id, cmpnt_id, set_by, modify_date,comments)
      2 select 2101, cmpnt_id, 'theo',sysdate,'auto set' from cmpnt
      3 where cmpnt_type_id in
      4 (select cmpnt_type_id from cmpnt_type START WITH cmpnt_type_id=94000 CONNECT BY parent_cmpnt_type_id= PRIOR cmpnt_type_id) and not exists (select cmpnt_id from cmpnt_prop where cmpnt_id=cmpnt.cmpnt_id and cmpnt_type_prop_id=2101);
      (select cmpnt_type_id from cmpnt_type START WITH cmpnt_type_id=94000 CONNECT BY parent_cmpnt_type_id= PRIOR cmpnt_type_id) and not exists (select cmpnt_id from cmpnt_prop where cmpnt_id=cmpnt.cmpnt_id and cmpnt_type_prop_id=2101)
      *
      ERROR at line 4:
      ORA-08177: can't serialize access for this transaction
      ORA-06512: at "CED2_OWNER.OVM_INSERT_239", line 6
      ORA-04088: error during execution of trigger 'CED2_OWNER.OVM_INSERT_239'


      SQL> rollback;

      Rollback complete.

      SQL> set transaction isolation level serializable;

      Transaction set.

      SQL> insert into cmpnt_prop (cmpnt_type_prop_id, cmpnt_id, set_by, modify_date,comments)
      2 select 2101, cmpnt_id, 'theo',sysdate,'auto set' from cmpnt
      3 where cmpnt_type_id in
      4 (select cmpnt_type_id from cmpnt_type START WITH cmpnt_type_id=94000 CONNECT BY parent_cmpnt_type_id= PRIOR cmpnt_type_id) and not exists (select cmpnt_id from cmpnt_prop where cmpnt_id=cmpnt.cmpnt_id and cmpnt_type_prop_id=2101);
      insert into cmpnt_prop (cmpnt_type_prop_id, cmpnt_id, set_by, modify_date,comments)
      *
      ERROR at line 1:
      ORA-08177: can't serialize access for this transaction
      ORA-06512: at "CED2_OWNER.OVM_INSERT_239", line 6
      ORA-04088: error during execution of trigger 'CED2_OWNER.OVM_INSERT_239'


      SQL> rollback;

      Rollback complete.