Deadlock problem
- From: "Alessandro Vercelli" <alever@xxxxxxxxx>
- To: "Oracle Freelists\.org" <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 8 May 2006 19:33:41 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
