RE: Deadlock problem

Sorry for the wrong trace file: here is the correct one:

• SESSION ID:(29.650) 2006.05.10.03.58.18.000 A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock: ORA-04020: deadlock detected while trying to lock object SYSUTIL_OWNER.ADFDATASETDESCRIPTOR

  ---

  object waiting waiting blocking blocking handle session lock mode session lock mode -------- -------- -------- ---- -------- -------- ---- 9bd59b80 9c774d98 9e0422bc X 9c774d98 9e042e74 S

  ---

  • DUMP OF WAITING AND BLOCKING LOCKS ----------

    ---

    • WAITING LOCK -------------

      ---

      SO: 9e0422bc, type: 23, owner: 9dfbe6b8, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=9e0422bc handle=9bd59b80 request=X call pin=0 session pin=0 user=9c774d98 session=9c774d98 count=0 flags=[00] savepoint=17051 LIBRARY OBJECT HANDLE: handle=9bd59b80 name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR hash=c41c8eb9 timestamp=06-16-2003 08:48:33 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3 lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0] pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08] ref=9bd59b88[9a25b544,9bd5b670] LIBRARY OBJECT: object=9bd22008 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      • -------- -------- ------ ---- ------ 0 9ba8e9b4 9bd2214c I/P/A 0 NONE 2 9bd22198 9a251648 I/P/A 1 NONE 3 9a2553e8 9a254fc8 I/-/A 0 NONE 8 9bd2209c 9bb43f78 I/-/A 0 NONE 10 9bd220fc 9bb3ebec I/-/A 0 NONE
    • BLOCKING LOCK ------------

      ---

      SO: 9e042e74, type: 23, owner: 9c7da4d0, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=9e042e74 handle=9bd59b80 mode=S call pin=9e036e90 session pin=0 user=9c774d98 session=9c774d98 count=2 flags=PNC/[04] savepoint=248 LIBRARY OBJECT HANDLE: handle=9bd59b80 name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR hash=c41c8eb9 timestamp=06-16-2003 08:48:33 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3 lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0] pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08] ref=9bd59b88[9a25b544,9bd5b670] LIBRARY OBJECT: object=9bd22008 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      • -------- -------- ------ ---- ------ 0 9ba8e9b4 9bd2214c I/P/A 0 NONE 2 9bd22198 9a251648 I/P/A 1 NONE 3 9a2553e8 9a254fc8 I/-/A 0 NONE 8 9bd2209c 9bb43f78 I/-/A 0 NONE 10 9bd220fc 9bb3ebec I/-/A 0 NONE

        ---

        This lock request was aborted.

The indexes are periodically rebuilded.

Thanks for your help,

Alessandro

> Hi Alessandro,
>
> First off, this is an ORA-0060, not an ORA-4020. (ORA-60 is an enqueue
> deadlock, ORA-4020 is a library cache deadlock.)
>
> Second, it's a TX (transaction enqueue) deadlock.
>
> Third, the mode held by the blocker and the mode held by the waiter are
> 'X' (exclusive).
>
> Fourth, the statement encountering the deadlock is an UPDATE.
>
> So, the combination of the above info tells me that this is a row-level
> application deadlock. You've got two concurrent sessions, one session
> updates row X and does not commit. Another session updates row Y and
> does not commit. Then, the first session tries updating row Y and
> starts waiting on the second session, and finally, the second session
> tries to update row X and starts waiting on the first session. In this
> state, the sessions would wait forever, so, Oracle detects a deadlock,
> raises ORA-0060, and statement level rollback occurs.
>
> I don't know anything about your application, however, the cleanest
> solution would be to examine the application logic, and alter as
> necessary to ensure that two concurrent sessions do not attempt to
> update the same set of rows. Also, another possiblility, which would
> still suffer from slowdowns due to TX enqueue waits, but not deadlocks,
> would be to ensure that the order that rows are updated is the same in
> all the concurrent sessions.
>
> Hope that helps,
>
> -Mark
>
>
>
>
> --
> Mark J. Bobak
> Senior Oracle Architect
> ProQuest Information & Learning
>
> For a successful technology, reality must take precedence over public
> relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alessandro Vercelli
> Sent: Monday, May 08, 2006 1:34 PM
> To: Oracle Freelists.org
> Subject: [SPAM] Deadlock problem
> Importance: Low
>
> Hi all,
> I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
> if messed) shows:
>
> ksqded1: deadlock detected via did
> DEADLOCK DETECTED
> Current SQL statement for this session:
> Update <TABLE> set <FIELD1>='<VALUE>' where <FIELD2> like '<STRING>%'
> The following deadlock is not an ORACLE error. It is a deadlock due to

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