Re: how to determine cause of deadlock

You can get 'no row' deadlocks when the INITRANS value on a table is too low. If the block is full and has INITRANS=1, and you get two processes trying to change the block, then the second has to wait for the first to commit so that it can acquire the 'ITL' (interested transaction list) slot. This is a little unlikely with inserts only.

Check the initrans on FEATURE, its indexes, and any parent tables and their indexes.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

gdas wrote in message <2b0a7fb2.cfadcd78_at_usw-ex0101-006.remarq.com>...


>Thanks for the tip about checking the udump file...OK, so now

>I'm completely confused.  The deadlock is occurring on an

>insert???  I have absolutely no idea what is being contended

>for? I'm not an expert in this stuff, so if anyone looking at

>this excerpt from the udump file has any tips about what to

>check, I'd appreciate it:

>

>I apologize in advance if the formatting doesn't come through.

>

>*** SESSION ID:(45.1044) 2000.07.13.14.05.22.894

>ksqded1:  deadlock detected via did

>DEADLOCK DETECTED

>Current SQL statement for this session:

>insert into feature

>(feature.transform_id,feature.feature_id,feature.unique_id,featur

>e.feature,feature.type,feature.freq) values(:1,:2,:3,:4,:5,:6)

>The following deadlock is not an ORACLE error. It is a

>deadlock due to user error in the design of an application

>or from issuing incorrect ad-hoc SQL. The following

>information may aid in determining the deadlock:

>Deadlock graph:

>                       ---------Blocker(s)--------  ---------

>Waiter(s)---------

>Resource Name          process session holds waits  process

>session holds waits

>TX-00060004-00003155        41      45     X             15

>26           S

>TX-0012000b-00002802        15      26     X             41

>45           S

>session 45: DID 0001-0029-00000002 session 26: DID 0001-

>000F-00000002

>session 26: DID 0001-000F-00000002 session 45: DID 0001-

>0029-00000002

>Rows waited on:

>Session 26: no row

>Session 45: no row

>===================================================

>

>

>

>-----------------------------------------------------------

>

>Got questions?  Get answers over the phone at Keen.com.

>Up to 100 minutes free!

>http://www.keen.com

>