Thursday, May 5, 2011

ORA-04027: self-deadlock during automatic validation

Sometimes ago I hit an error:

   ORA-04027: self-deadlock during automatic validation for object

I wonder why there is a self-deadlock in a single session (no autonomous transaction involved). Then I tried to extract a short test case to reproduce it as follows.

drop table testt1;
drop package testp1;
drop procedure proc1;

create table testt1 (a number(2));
insert into testt1 values (12);
commit;

create or replace package testp1 as
  function f1 return number;
  procedure p1;
end testp1;
/

create or replace procedure proc1              
as
  procedure prt(p_name varchar2) as
  begin
      for c in (select p_name || object_name || '(' || object_type || ')' || status s
                from   dba_objects
                where  object_name in ('TESTP1', 'PROC1'))
      loop
        dbms_output.put_line(c.s);
      end loop; 
  end;
begin
  prt('Before Alter: ');
  execute immediate 'alter table testt1 modify (a number(2))';
  prt('After Alter:  ');
  update testt1 set a=testp1.f1;
end proc1;
/

create or replace package body testp1 as
  function f1 return number as
    begin
      return 10;
    end;
   
  procedure p1 is
    begin
      proc1;
    end;
end testp1;
/

Now if I run:

SQL> exec proc1;

Before Alter: TESTP1(PACKAGE BODY)VALID
Before Alter: TESTP1(PACKAGE)VALID
Before Alter: PROC1(PROCEDURE)VALID
After Alter: TESTP1(PACKAGE BODY)INVALID
After Alter: TESTP1(PACKAGE)VALID
After Alter: PROC1(PROCEDURE)INVALID
BEGIN proc1; END;

*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of TESTP1
ORA-04027: self-deadlock during automatic validation for object PROC1
ORA-06512: at "PROC1", line 16
ORA-06512: at line 1

There are two invalids: TESTP1(PACKAGE BODY) and PROC1(PROCEDURE). You can make both valid by:


alter PACKAGE TESTP1 COMPILE BODY;

but whenever you call proc1, they are again invalid.

The dependency is TESTP1(PACKAGE BODY) on PROC1, and PROC1 & TESTP1(PACKAGE BODY) on TESTP1(PACKAGE).

Here is an attempt to make some reasoning.

When running "exec proc1;", proc1 is pinned, after "alter table testt1", proc1 is invalid, which in turn, causes the invalid of testp1 body due to dependency (the pinned version of proc1 is still valid since it is the directly called unit), then proc1 runs to the update statement, which requires a valid testp1 body, thus it demands an X-lock on testp1 body, which again asks for an X-lock of proc1 (due to dependency). Since proc1 is already pinned (Share-lock) by its own, it is not possible to allocate an X-lock to itself. So a self-deadlock is generated during validation of proc1.
                       
The code was tested on 10gr2 and 11gr2. Toggling 11gr2 hidden parameters:
_disable_fast_validate(TRUE, FALSE), _ignore_fg_deps  (TABLES, PLSQL, ALL, NONE)
seems having no influence on the behavior.