Ora 04020 - deadlock detected while trying to lock object
Alan Formstone
Looking at the V$LOCK view shows the following:
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ----------
---------- ----------
20083414 20083424 2 MR 10 0 4 0
0 0
2008339C 200833AC 2 MR 1 0 4 0
0 0
20083360 20083370 2 MR 2 0 4 0
0 0
20083324 20083334 2 MR 3 0 4 0
0 0
200832E8 200832F8 2 MR 4 0 4 0
0 0
200832AC 200832BC 2 MR 5 0 4 0
0 0
20083270 20083280 2 MR 6 0 4 0
0 0
20083234 20083244 2 MR 7 0 4 0
0 0
200831F8 20083208 2 MR 8 0 4 0
0 0
200831BC 200831CC 2 MR 9 0 4 0
0 0
20083450 20083460 3 RT 1 0 6 0
0 0
200834C8 200834D8 5 TS 2 41943042 3 0
968 0
12 rows selected.
I restarted the database and the TS lock had disappeared. We reran the
application and about a minute later this LOCK had reappeared.
The v$sysstats shows the following lock information :
SQL> select * from v$sysstat where name like '%lock%';
STATISTIC# NAME
CLASS VALUE
---------- ----------------------------------------------------------------
---------- ----------
24 enqueue deadlocks
4 0
28 global lock sync gets
32 0
29 global lock async gets
32 0
30 global lock get time
32 0
31 global lock sync converts
32 0
32 global lock async converts
32 0
33 global lock convert time
32 0
34 global lock releases
32 0
............
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
Thomas Kyte
(if that email address didn't require changing)
On Mon, 25 Oct 1999 15:50:37 -0400, you wrote:
>An application is trying to insert into a table and gets the above error.
>
are you using declaritive integrity? do you have your foreign keys indexed?
See the URL in my signature for a short paper "Unindexed Foreign Keys -- why
they might be bad and how to detect them"
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Alan Formstone
Thomas Kyte wrote in message ...
>
>are you using declaritive integrity? do you have your foreign keys
indexed?
>See the URL in my signature for a short paper "Unindexed Foreign Keys --
why
>they might be bad and how to detect them"
>See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
>Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
>
All the foriegn Keys are indexed, a point i didn't mention earlier is that
the failing statement is an insert.
Also there are no triggers on this table.
Thanks,
Alan
karsten_s...@my-deja.com
Any other statements in the insert-transaction ?
perhaps you locked another object in the same transaction and somebody
else is doing it the other way round ?
Is someboy doing ddl at the same time ?
Karsten
In article <3814...@news.lhsgroup.com>,
Sent via Deja.com http://www.deja.com/
Before you buy.
Steve Adams
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"
<aform...@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]
Alan Formstone
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...@news.eagles.bbs.net.au>...
Steve Adams
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"