Deadlock problem

From: Alessandro Vercelli <alever_at_libero.it>
Date: Mon, 8 May 2006 19:33:41 +0200
Message-Id: <IYYJG5$F47D45EBE5421C8FCDE8E2F3EC06389D@libero.it>

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

Received on Mon May 08 2006 - 12:33:41 CDT