RE: Deadlock read

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 9 Nov 2004 11:27:15 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9BC1@AABO-EXCHANGE02.bos.il.pqe>

Chadi,

Ok, look at the deadlock graph. Now, there are two lines, each beginning with a 'TX'. The 'TX' indicates what type of enqueue, in this case, a transaction enqueue. Now, as you read across the line, look at the headers above. So, the first line shows that TX-0056001a-0000008d is being held in 'X' (exclusive) mode by process id 50/session id 368. Now, while it's being held=20 exclusively by that session, it's also being waited on by process id 74, session id 426. So, we've got this enqueue resource being held by one session and waited on by another session.

Now, the second line in the deadlock graph shows another TX enqueue, TX-00660018-0000006f. This enqueue is being held by process 74/session = 476.
(Note that it's the same session that's waiting on the previously = described
enqueue.) Also, this enqueue is being waited on by process 50/session = 368.

So, this is a deadlock. Session 368 holds the first enqueue and waits = on the
second. Session 476 hold the second enqueue, and is waiting on the = first.
Without Oracle's deadlock detection mechanism, these tow sessions would wait forever.

The final bit of info is in the "rows waited on" section. If this were = a heap
table, or if the problem was caused by existence of a bitmap index, this = section
would say "no rows". Since it has row info, I'm going to speculate that = the=20
COLORS table is an IOT (Index organized table).

Bottom line, the problem here is that you're overlapping on the primary = keys=20
you're inserting.

In other words, one session inserts pk=3D1. Then second session inserts = pk=3D2.
Neither has committed. Now, first session tries to insert pk=3D2, = starts waiting
on second session. Finally, second session tries to insert pk=3D1, it = starts waiting
on first session. There's your deadlock.

So, briefly, that's how you read a deadlock graph, and my interpretation = of what's
happening on your system.

Hope that helps,

-Mark
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Chadi Kassan
> Sent: Tuesday, November 09, 2004 10:06 AM
> To: oracle-l_at_freelists.org
> Cc: oracle-l-bounce_at_freelists.org
> Subject: Deadlock read

>=20
>=20

> Hi everyone,

>=20

> Can anyone explain what caused this deadlock or how to read=20
> this kind of
> deadlock (i'm just wondering how it happens with the insert).

>=20

> thank you

>=20
> =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> DEADLOCK DETECTED
> Current SQL statement for this session:
> INSERT INTO COLORS (color_code, clr_id, clr_name, bu_bu_id)=20
> VALUES (:1, :2,
> :3, :4)
> 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=20
> session holds
> waits
> TX-0056001a-0000008d 50 368 X 74 426
> S
> TX-00660018-0000006f 74 426 X 50 368
> S
> session 368: DID 0001-0032-00039980 session 426: DID=20
> 0001-004A-00022694
> session 426: DID 0001-004A-00022694 session 368: DID=20
> 0001-0032-00039980
> Rows waited on:
> Session 426: obj - rowid =3D 000101AA - AAAQGqAAXAAAAkKAAA
> (dictionary objn - 65962, file - 23, block - 2314, slot - 0)
> Session 368: obj - rowid =3D 000101C7 - AAAQHHAAWAAAGANAAA
> (dictionary objn - 65991, file - 22, block - 24589, slot - 0)
> Information on the OTHER waiting sessions:
> Session 426:
> pid=3D74 serial=3D2070 audsid=3D189403 user: 71/ADI_IL_TEST
> O/S info: user: , term: , ospid: 1234, machine: esjmstst01
> program:
> Current SQL Statement:
> INSERT INTO COLORS (color_code, clr_id, clr_name, bu_bu_id)=20
> VALUES (:1,
> :2, :3, :4)
> End of information on OTHER waiting sessions.
> =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D

> --
> http://www.freelists.org/webpage/oracle-l
>=20

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 09 2004 - 10:22:56 CST