Re: how to determine cause of deadlock

In article <2b0a7fb2.cfadcd78_at_usw-ex0101-006.remarq.com>,   gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> 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
> ===================================================
>

Hello gdas,

Being relatively new to Oracle, I can only offer a guess based on similar experiences with a Sybase db:

Process 41 of Session 45 holds an exclusive-lock? on object_addressed_by(DID 0001-0029-00000002) and is waiting for a shared-lock? on object_addressed_by(DID 0001-000F-00000002).

Process 15 of Session 26 holds an exclusive-lock? on object_addressed_by(DID 0001-000F-00000002) and is waiting for a shared-lock? on object_addressed_by(DID 0001-0029-00000002).

In essence, the two processes are preventing each other from getting the shared-lock? they require to complete their tasks - the result being a deadlock.

Possibly the shared-lock? is a pre-cursor to an exclusive-lock? to enable the updating of an index? or something..really guessing here!

A guess at the "object_addressed_by" stuff: Database ref: 0001
Schema refs: 000F & 0029
Object refs: 00000002

Under Sybase "select object_name(00000002)" would give you the object name. Under Oracle you would probably get this information by joining some view columns - see earlier thread.

Hope this is of some help - but remember that I'm only guessing here and could be leading you astray!

Good luck!

--
Feel the power of the source...


Sent via Deja.com http://www.deja.com/
Before you buy.