Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: [SPAM] Deadlock problem

RE: [SPAM] Deadlock problem

From: Bobak, Mark <>
Date: Mon, 8 May 2006 13:46:45 -0400
Message-ID: <>

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 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-----

[] On Behalf Of Alessandro Vercelli Sent: Monday, May 08, 2006 1:34 PM
To: Oracle
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
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:


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
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,



-- Received on Mon May 08 2006 - 12:46:45 CDT

Original text of this message