On 5/8/06, oracle-l-bounce@(protected) <oracle-l-bounce@(protected)> 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@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of Mercadante, Thomas F > (LABOR) > Sent: Monday, May 08, 2006 1:51 PM > To: alever@(protected); 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@(protected) > [mailto:oracle-l-bounce@(protected)] 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 (See ora-4020.ora-code.com) (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
... which also do not cause deadocks.<br><br><div><span class="gmail_quote">On 5/8/06, <b class="gmail_sendername"><a href="mailto:oracle-l-bounce@(protected) .org">oracle-l-bounce@(protected)</a></b> <<a href="mailto:oracle-l-bounce @(protected)"> oracle-l-bounce@(protected)</a>> wrote:</span><blockquote class="gmail _quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0 .8ex; padding-left: 1ex;">If it were FKs that are missing indexes, waits would be on a TM enqueue, <br>not a TX enqueue.<br><br>-Mark<br><br><br>--<br>Mark J. Bobak<br>Senior Oracle Architect<br>ProQuest Information & Learning<br><br>For a successful technology, reality must take precedence over public<br>relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988 <br><br><br>-- --Original Message-- --<br>From: <a href="mailto:oracle-l-bounce @(protected)">oracle-l-bounce@(protected)</a><br>[mailto:<a href="mailto :oracle-l-bounce@(protected)">oracle-l-bounce@(protected)</a>] On Behalf Of Mercadante, Thomas F <br>(LABOR)<br>Sent: Monday, May 08, 2006 1:51 PM<br>To: <a href="mailto:alever @(protected)">alever@(protected)</a>; Oracle <a href="http://Freelists.org" >Freelists.org</a><br>Subject: RE: Deadlock problem<br><br>Allesandro,<br> <br>There were two theories concerning where a commit statement should be<br >issued from.<br><br>The first theory I heard from Oracle when PL/SQL first came out was that<br>packages should not have commit statements in them - that the <br>application should issue the commit when all of the pieces of work were<br >completed. It was thought that the application would better know when a<br>commit should be issued.<br><br>The other theory was to put all of the work in the PL/SQL packages and <br>let it control everything and either report back success (commit) or<br >failure (rollback) to the application.<br><br>Today, either way works just fine in my view.<br><br>As for your problem, dig a little deeper. Most deadlocks that I've seen <br>are caused by foreign key constraints and missing indexes. So look at<br>the tables involved and look for the table being updated being<br >referenced by another table via a FK. Simply adding indexes to the <br>foreign key columns solves this problem. <br><br>And remember - this is an application problem. Somebody might have to<br>fix some code!<br><br>Good Luck.<br><br>Tom<br><br>---- -Original Message-- --<br>From: <a href="mailto:oracle-l-bounce@(protected)" >oracle-l-bounce@(protected) </a><br>[mailto:<a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce @(protected)</a>] On Behalf Of Alessandro Vercelli<br>Sent: Monday, May 08, 2006 1:34 PM<br>To: Oracle <a href="http://Freelists.org">Freelists.org </a><br>Subject: Deadlock problem<br><br>Hi all,<br>I'm trying to solve an ora -4020 (deadlock) issue; the trace file (sorry<br>if messed) shows:<br><br >ksqded1: deadlock detected via did<br>DEADLOCK DETECTED<br>Current SQL statement for this session: <br>Update <TABLE> set <FIELD1>='<VALUE>' where <FIELD2> ; like '<STRING>%'<br>The following deadlock is not an ORACLE error. It is a deadlock due to<br>user error in the design of an application or from issuing incorrect <br>ad-hoc SQL. The following information may aid in determining the<br >deadlock:<br>Deadlock graph:<br>   ; -- ------Blocker(s)-- -----<br>-- ------Waiter(s)-- ------<br>Resource Name process session holds waits process session <br>holds waits<br>TX-00040015-0000305b 13 11 X 10  ; 14<br>X<br>TX-0007000b-0000309e 10 14 X 13 11<br>X<br>session 11: DID 0001-000D-00000001 session 14: DID <br>0001-000A-00000001<br>session 14: DID 0001-000A-00000001 session 11: DID<br>0001-000D-00000001<br>Rows waited on:<br >Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session 11: obj<br>- rowid = 0000147E - AAABR+AAKAAAzEeAAH <br><br>The trace file shows clearly that session 11 and 14 are blocking each <br>other.<br><br>Note that <STRING> can be very long, but it's almost certain that this<br>is not causing the problem.<br><br>Database version is 8.0.5 on Solaris 8 sparc.<br><br>So, I'm looking at the piece of source containing the affected code (I'm<br>not the developer neither a skilled one) and I have seen something<br>strange, that is a sql package containing many procedures with <br>insert/update statements and none of these insert/update was followed by<br >a commit; I asked the developer for this matter and she said that a<br>commit would prevent a possible rollback of database transaction.<br><br> Now, my questions are:<br>1. Is it correct an insert/update without a commit into a sql package?<br>If yes, when are the inserted/updated data commited?<br >2. Would this the possible cause of the deadclock, as the table indexes <br>could be locked by a large number of records inserted/updated?<br>3. Is this the correct way to get the choice of performing a rollback?<br><br><br >Thanks for you help,<br><br>Alessandro<br><br>--<br><a href="http://www .freelists.org/webpage/oracle-l"> http://www.freelists.org/webpage/oracle-l</a><br><br><br>--<br><a href="http:/ /www.freelists.org/webpage/oracle-l">http://www.freelists.org/webpage/oracle-l< /a><br><br><br>--<br><a href="http://www.freelists.org/webpage/oracle-l"> http://www.freelists.org/webpage/oracle-l</a><br><br><br></blockquote></div><br ><br clear="all"><br>-- <br>Jared Still<br>Certifiable Oracle DBA and Part Time Perl Evangelist<br>
