Re: deadlock trace interpretation

I was going to send you a simple example of making this happen, but I just tested it on 9.2 and got an even funnier result.

create or replace proc_a as
begin

    dbms_lock.sleep(10);
    execute immediate 'alter proc_b compile'; end;
/

create or replace proc_b as
begin

    dbms_lock.sleep(10);
    execute immediate 'alter proc_a compile'; end;
/

Then from session 1 issue

    execute proc_a
and from session 2

    execute proc_b

I was expecting a 3-second deadlock - but got a 5 minute timeout with error message 4021: but you get the idea. Whilst the wait was going on, v$session_wait showed 'library cache pin'.

There are various (usually buggy) things that cause objects to become invalid and require revalidation.

But it is possible for a session to be executing some code which is pinning an object in share mode, and since the recompile needs a pin in exclusive mode you get a wait - once you get a wait, you can usually engineer a deadlock.

The waiting/blocking sessions are probably the addr value from V$session. (Looks like you have a 64bit platform). The object handle is one of the raw columns from (I think) v$db_object_cache - possible something like kglhdpar.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Ted Chyn wrote in message
<44a19320.0210040923.52b02e78_at_posting.google.com>...


>all:

>I have follwing deadlock trace file.

>

>my question:

>1. what is ddl and parse locks ?

>

>2. how to translate object handle (c000000020a80868),

>                     waiting session(c0000000044de890),

>                    block session(c000000006f10a70)

>  to object_id or object name and more meaningful session number ?

>

>

>thnx in advance

>ted chyn