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@(protected)
[mailto:oracle-l-bounce@(protected)
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
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-00040015-0000305b 13 11 X 10 14
X
TX-0007000b-0000309e 10 14 X 13 11
X
session 11: DID 0001-000D-00000001 session 14: DID
0001-000A-00000001
session 14: DID 0001-000A-00000001 session 11: DID
0001-000D-00000001
Rows waited on:
Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session 11: obj
- rowid = 0000147E - AAABR+AAKAAAzEeAAH
The trace file shows clearly that session 11 and 14 are blocking each
other.
Note that <STRING> can be very long, but it's almost certain that this
is not causing the problem.
Database version is 8.0.5 on Solaris 8 sparc.
So, I'm looking at the piece of source containing the affected code (I'm
not the developer neither a skilled one) and I have seen something
strange, that is a sql package containing many procedures with
insert/update statements and none of these insert/update was followed by
a commit; I asked the developer for this matter and she said that a
commit would prevent a possible rollback of database transaction.
Now, my questions are:
1. Is it correct an insert/update without a commit into a sql package?
If yes, when are the inserted/updated data commited?
2. Would this the possible cause of the deadclock, as the table indexes
could be locked by a large number of records inserted/updated?
3. Is this the correct way to get the choice of performing a rollback?
Thanks for you help,
Alessandro
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
