Re: Ora 04020 - deadlock detected while trying to lock object

Hi Alan,

That output shows no library cache lock waiters, which means that the deadlock did not exist at the time that the query was run.

If you are unable to catch the output from DBA_KGLLOCK at the right moment, it will be necessary to use an event setting to diagnose this. If it is a self-deadlock, as I suspect, then a processstate dump will be adequate, if not, then an entire systemstate dump will be needed.

If you have access to the code, modify the insert session to do the following:
  alter session set max_dump_file_size = unlimited;   alter session set events
    '4020 trace name processstate forever, level 10'; Otherwise you can put the equivalent settings in the init.ora file. If you are keen to get a resolution quickly, you may want to go straight to a systemstate dump, but be warned it will be LARGE.

Analyzing the dump is non-trivial unfortunately. You will probably have to get Oracle Support to do it for you. I could do it too, but I would want to charge you for it. ;-)

As to further information on library cache locks and pins, there are a few pages in my book that may help build a general understanding. The book is "Oracle8i Internal Services for Waits, Latches, Locks and Memory", O'Reilly & Associates, ISBN: 156592598X. RRP is $19.95, but you can do better than that at most on the book-seller web sites.

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/

On Thu, 28 Oct 1999 10:44:09 -0400, "Alan Formstone" <aformstone_at_us.lhsgroup.com> wrote:

>Steve,
>
>Thanks very much, it seems we have a new direction. I installed the views
>and have been searching around for documentation to explain the DBA_KGLLOCK.
>I haven't been able to find anymore than the sentence 'KGL lock
>information'.
>
>The problem remains..... Here are the contents of DBA_KGLLOCK :
>
>KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL
>-------- -------- ---------- ---------- ----
>20046964 204D8BBC 1 0 Lock
>20046964 204DB668 1 0 Lock
>200479BC 2022B3D0 1 0 Lock
>200479BC 20226A34 1 0 Lock
>200481E8 2052148C 1 0 Lock
>200481E8 20528004 1 0 Lock
>20046964 2021BBFC 1 0 Lock
>20046964 2021B588 1 0 Lock
>20047190 203D7E18 1 0 Lock
>20047190 203D5ADC 1 0 Lock
>20047190 203BB860 1 0 Lock
>20047190 203BA378 1 0 Lock
>200481E8 20209CAC 1 0 Lock
>200481E8 20209DC4 1 0 Lock
>20047190 204CB944 1 0 Lock
>20047190 204CB9F4 1 0 Lock
>200481E8 205404F8 1 0 Lock
>200481E8 2054983C 1 0 Lock
>200479BC 2022B3D0 2 0 Pin
>200481E8 20209CAC 2 0 Pin
>
>20 rows selected.
>
>Any ideas or the location of any documentation on this view would be
>appreciated. I'll even buy books!
>
>Thanks,
>Alan
>
>
>Steve Adams wrote in message <3817cb53.30269985_at_news.eagles.bbs.net.au>...
>>Hi Alan,
>>
>>This error message relates to library cache lock deadlocks, not
>>enqueue lock deadlocks.
>>
>>V$LOCK is only appropriate for enqueue locking issues.
>>
>>To investigate library cache locking issues, you need to use
>>DBA_KGLLOCK which is created by the catblock.sql script.
>>
>>If the problem is still reproducible, you may want to post the output
>>here.
>>
>>Regards,
>>Steve Adams
>>http://www.ixora.com.au/
>>http://www.oreilly.com/catalog/orinternals/
>>http://www.christianity.com.au/
>>
>>
>>On Mon, 25 Oct 1999 15:50:37 -0400, "Alan Formstone"
>><aformstone_at_us.lhsgroup.com> wrote:
>>
>>>An application is trying to insert into a table and gets the above error.
>>>
>>[snip]
>>>
>>>I restarted the database and the TS lock had disappeared. We reran the
>>>application and about a minute later this LOCK had reappeared.
>>>
>>[snip]
>>>
>>>Any idea where else I could look to find out more about this problem. I've
>>>rescanned the server concepts manual with little joy, and have now run out
>>>of ideas.
>>>
>>>The other V$ views concerning locks (like '%LOCK%') are empty.
>>>
>>>Any help or pointers would be gratefully appreciated
>>
>
>
Received on Thu Oct 28 1999 - 15:14:49 CDT