Deadlock problem 2006-05-10 - By Alessandro Vercelli
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 (See ORA-04020.ora-code.com): 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 (See ORA-0060.ora-code.com), not an ORA-4020 (See ORA-4020.ora-code.com). (ORA-60 (See ORA-60.ora-code.com) is an enqueue
> deadlock, ORA-4020 (See ORA-4020.ora-code.com) 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 (See ORA-0060.ora-code.com), 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@(protected)
> [mailto:oracle-l-bounce@(protected)] 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 (See ora-4020.ora-code.com) (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
|
|
