RE: Deadlock problem

  • To: <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <alever@xxxxxxxxx>, "Oracle Freelists.org" <Oracle-L@xxxxxxxxxxxxx>
  • Date: Mon, 8 May 2006 13:52:50 -0400

If it were FKs that are missing indexes, waits would be on a TM enqueue,
not a TX enqueue.

-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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mercadante, Thomas F
(LABOR)
Sent: Monday, May 08, 2006 1:51 PM
To: alever@xxxxxxxxx; Oracle Freelists.org
Subject: RE: Deadlock problem

Allesandro,

There were two theories concerning where a commit statement should be
issued from.

The first theory I heard from Oracle when PL/SQL first came out was that
packages should not have commit statements in them - that the
application should issue the commit when all of the pieces of work were
completed.  It was thought that the application would better know when a
commit should be issued.

The other theory was to put all of the work in the PL/SQL packages and
let it control everything and either report back success (commit) or
failure (rollback) to the application.

Today, either way works just fine in my view.

As for your problem, dig a little deeper.  Most deadlocks that I've seen
are caused by foreign key constraints and missing indexes.  So look at
the tables involved and look for the table being updated being
referenced by another table via a FK.  Simply adding indexes to the
foreign key columns solves this problem.

And remember - this is an application problem.  Somebody might have to
fix some code!

Good Luck.

Tom

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alessandro Vercelli
Sent: Monday, May 08, 2006 1:34 PM
To: Oracle Freelists.org
Subject: Deadlock problem

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


--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: