| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: [SPAM] Deadlock problem
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
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
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
Received on Mon May 08 2006 - 12:46:45 CDT
 |
 |