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@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 -- //www.freelists.org/webpage/oracle-l