Compile blocked package/procedure/function

Recent blog topic (Unlocking the locked table) remind me to write another (and think more mysterious) blocking problem-how to compile blocked package/procedure/function?

Problem arise when you have to compile package/procedure/function on database where many users in the same time use the same package/procedure/function.

Problem

Let us suppose that some user has started his session and execute test procedure from package tools. Because procedure test is some bigger transaction it takes longer time to finish.

A little bit later developer has tried to compile the same package (in both cases picture shows session_id and serial# for more easy reference later):

Unfortunately developer compile is blocked … and might be very dangerous!

To make situation more real, let us suppose that soon after developer tried to compile the package, another user has started from package tools any procedure/function (unimportant which one!!):

 

He is blocked also! Why?

Cause of the problem

First let us find that problem is real. Look for waiter sessions, sessions who are "handicapped":

select to_char(SESSION_ID,'99999') sid ,     
   substr(LOCK_TYPE,1,30) Type,     
   substr(lock_id1,1,20) Object_Name,     
   substr(mode_held,1,4) HELD,     
   substr(mode_requested,1,4) REQ,     
   lock_id2 lock_addr     
FROM dba_lock_internal     
WHERE     
   mode_requested <> 'None'     
   and mode_requested <> mode_held     
;     

SID    TYPE                           OBJECT_NAME  HELD REQ  LOCK_ADDR     
------ ------------------------------ ------------ ---- ---- ----------------
   125 Table/Procedure/Type Definitio DAMIRV.TOOLS None Shar 000007FF10E0A298     
   118 Table/Procedure/Type Definitio DAMIRV.TOOLS None Excl 000007FF10E0A298  
   

Our session (sid) 118 is blocked because it holds less lock rights then requested (held=None and requested=Exclusive). Exclusive lock is always needed for compiling package/procedure/function. And this is explanation why developer is waiting for compile-his session cannot obtain exclusive lock on package tools.

Third session (125) is another execution of same package (same LOCK_ADDR) but as mentioned before, this execution came after our recompile request has been placed, and this session (HELD=None) so it is not blocking us!

To find blocker, use lock_id2 (presented through alias LOCK_ADDR for easy reference) from same view dba_lock_internal as before, with changed where condition:

select to_char(SESSION_ID,'99999') sid ,     
   substr(LOCK_TYPE,1,30) Type,     
   substr(lock_id1,1,20) Object_Name,     
   substr(mode_held,1,4) HELD,     
   substr(mode_requested,1,4) REQ,     
   lock_id2 lock_addr     
FROM dba_lock_internal     
WHERE     
     lock_id2='000007FF10E0A298'     
  and session_id != 118     
order by sid     
;     

SID    TYPE                           OBJECT_NAME  HELD REQ  LOCK_ADDR     
------ ------------------------------ ------------ ---- ---- ----------------
   122 Table/Procedure/Type Definitio DAMIRV.TOOLS Null None 000007FF10E0A298     
   122 Table/Procedure/Type Definitio DAMIRV.TOOLS Shar None 000007FF10E0A298     
   125 Table/Procedure/Type Definitio DAMIRV.TOOLS None Shar 000007FF10E0A298     

Now it is more then clear that session 122 holds "Share" lock type, what is blocking session 118 (us). Even more, if here exist any other session that hold any type of lock (except "null" or "None"), this session will be blocking our session as well!

Other session (125, the one that has been executed after our compile request has been placed) is waiting to place the "Share" lock. Because session 125 cannot perform lock as requested, this session is blocked also. And it has been blocked by our session which waits also for other session! Ant this will happened to all sessions that started to use tools package after our compile request!

Now you see why this blocked package compile is dangerous action-it may blocked very large part of database if we want to compile very popular object! Consequence is that users would soon bag that all in database is hanged! And it is!

So DBA has to react very quick to prevent that!

Solution

According mentioned, let us find more information about session 122 (to be sure we will not kill "good guy"):

SQL> col iid for 990
SQL> col sid_serial for a11;
SQL> col sql_text for a80;
SQL> SELECT
  2         t.sql_text,
  3         s.inst_id iid,
  4         lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial       
  5    FROM gv$sqlarea t,
  6         gv$session s
  7   WHERE t.address = s.sql_address
  8     AND t.hash_value = s.sql_hash_value
  9     AND s.inst_id=&1
10     AND s.SID = &2
11     AND s.sql_hash_value != 0
12  order by 2,3
13  ;
Enter value for 1: 1
old   9:    AND s.inst_id=&1
new   9:    AND s.inst_id=1
Enter value for 2: 122
old  10:    AND s.SID = &2
new  10:    AND s.SID = 122

SQL_TEXT                  IID  SID_SERIAL
------------------------- ---- -----------
BEGIN tools.test; END;       1   122,  192 

This is really our blocker session so we have to kill it. Here is an example of my script which is working on "RAC" and "non RAC" (iid=1) environment and this is the reason why iid is also parameter:

SQL> alter system disconnect session '122,192' immediate;     
alter system disconnect session '122,192' immediate     
*     
ERROR at line 1:     
ORA-00031: session marked for kill     

SQL>     

When you kill session 122 and perform the same query as before, no rows should be shown:

SQL> select to_char(SESSION_ID,'99999') sid ,     
  2     substr(LOCK_TYPE,1,30) Type,     
  3     substr(lock_id1,1,20) Object_Name,     
  4     substr(mode_held,1,4) HELD,     
  5     substr(mode_requested,1,4) REQ,     
  6     lock_id2 lock_addr     
  7  FROM dba_lock_internal     
  8  WHERE     
  9     mode_requested <> 'None'     
10     and mode_requested <> mode_held     
11  ;     

no rows selected     

SQL>

If there exists more records (more users has started to use package tools before our compile request) then you have to kill them all!

For the end

I know that many of you might say that developers should not have access on production instance-true! But sometimes is that just a nice wish.

Other thing is that some quick bug fixing (why they happened...another discussion!) always happened exactly in shown way...so prevent that is sometimes more then fairy tale!

Cheers!

P.S.
Prove that "print_record" procedure (in third session-waiting) is one easy and quick finishing action, is explained in my topic Columns to rows (print_record)

P.S. II
Fully automated procedure (kills sessions automatically) can be found Compile blocked package/procedure/function II