Making self deadlock on the library cache pin

It’s very easy to make your session deadlocked on the library cache pin. Let me prove it.

Step 1. Make a dummy procedure called TEST_PROC1.

TPACK@ukja1120> select * from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

create or replace procedure test_proc1 
is
begin
	null;
end;
/

Step 2. Execute simple PL/SQL which executes TEST_PROC1 and compiles it.

TPACK@ukja1120> begin
  2     test_proc1;
  3
  4     execute immediate 'alter procedure test_proc1 compile';
  5
  6  end;
  7  /

...
(Hang)

Very simple, isn’t it?

ASH cleary shows that the session is waiting for the library cache pin to be released, but it’s being acquried by the session itself.

select * 
from (
	select
		h.session_id as sid,
		to_char(h.sample_time,'mi:ss') as sample_time,
		h.sql_id,
		(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
		event,
		blocking_session as blocker
	from
		v$active_session_history h
	where
		h.session_id = &sid
	order by h.sample_time desc			
) where rownum <= 20	
;					

 SID SAMPL SQL_ID        SQL_TEXT             EVENT         BLOCKER
---- ----- ------------- -------------------- ---------- ----------
 136 49:10                                    library ca        136
                                              che pin

 136 49:09                                    library ca        136
                                              che pin

 136 49:08                                    library ca        136
                                              che pin

 136 49:07                                    library ca        136
                                              che pin

 136 49:06                                    library ca        136
                                              che pin

 136 49:05                                    library ca        136
                                              che pin

 136 49:04                                    library ca        136
                                              che pin

 136 49:03                                    library ca        136
                                              che pin

 136 49:02                                    library ca        136
                                              che pin

 136 49:01                                    library ca        136
                                              che pin

 136 49:00                                    library ca        136
                                              che pin

 136 48:59                                    library ca        136
                                              che pin

 ...

TPack‘s Wait Detail Report gives more detailed information on the deadlocked library cache pin contention.

TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))

NAME                           VALUE
------------------------------ --------------------
SID                            136
Serial#                        2797
SPID                           5148
Program                        sqlplus.exe
Process                        5404:672
Module                         SQL*Plus
SQL ID                         9pbva4bn2m25b
Child No                       0
SQL Text                       alter procedure test
                               _proc1 compile

Status                         ACTIVE
Blocking Instance              1
Blocking Session               136
SQL Exec Start                 2010/09/15 13:45:34
Event                          library cache pin
Seq#                           130
P1(P1raw)                      384372376(0000000016
                               E90E98)

P2(P2raw)                      384372376(0000000016
                               DAB608)

P3(P3raw)                      384372376(00014F8500
                               010003)

Seconds in wait                40
State                          WAITING
Wait Event                     library cache pin
Holder SID                     136
Namespace                      TABLE/PROCEDURE
Object                         TEST_PROC1
Holding Mode                   2(S)

So, this is not a normal situation, isn’t it? What would be the reason of the library cache pin not being released even after the execution?