Re: Deadlock problem

... which also do not cause deadocks.

On 5/8/06, oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> wrote:
>
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F
> (LABOR)
> Sent: Monday, May 08, 2006 1:51 PM
> To: alever_at_libero.it; 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_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: 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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