Sure, missing indexes on FKs can cause deadlocks. Well, ok, not having an index on a FK column on the child table does not inherently cause a deadlock, but the absence of the index certainly increases the likelihood of an enqueue wait (and possibly a deadlock) duing concurrent DML.
-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
__ ____ ____ ____ ____ ____ ____
From: Jared Still [mailto:jkstill@(protected)] Sent: Monday, May 08, 2006 2:06 PM To: Bobak, Mark Cc: Thomas.Mercadante@(protected); alever@(protected); Oracle Freelists.org Subject: Re: Deadlock problem
... which also do not cause deadocks.
On 5/8/06, oracle-l-bounce@(protected) < oracle-l-bounce@(protected) <mailto: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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=us-ascii"> <META content="MSHTML 6.00.2900.2873" name=GENERATOR></HEAD> <BODY> <DIV><SPAN class=906310618-08052006><FONT face=Arial color=#0000ff size=2>Huh?</FONT></SPAN></DIV> <DIV><SPAN class=906310618-08052006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=906310618-08052006><FONT face=Arial color=#0000ff size=2>Sure, missing indexes on FKs can cause deadlocks. Well, ok, not having an index on a FK column on the child table does not inherently cause a deadlock, but the absence of the index certainly increases the likelihood of an enqueue wait (and possibly a deadlock) duing concurrent DML.</FONT></SPAN></DIV> <DIV><SPAN class=906310618-08052006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=906310618-08052006><FONT face=Arial color=#0000ff size=2>-Mark</FONT></SPAN></DIV> <DIV> </DIV><!-- Converted from text/rtf format --> <P><B><FONT face="Century Gothic" size=2>--</FONT></B> <BR><B><FONT face="Century Gothic" size=2>Mark J. Bobak</FONT></B> <BR><B><FONT face="Century Gothic" size=2>Senior Oracle Architect</FONT></B> <BR><B><FONT face="Century Gothic" color=#000000 size=2>P</FONT><FONT face="Century Gothic" size=2>ro</FONT><FONT face="Century Gothic" color=#ff0000 size=2>Q</FONT><FONT face="Century Gothic" size=2>uest Information & Learning</FONT></B> </P> <P><FONT face="Courier New" size=2>For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988</FONT></P> <DIV> </DIV><BR> <DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left> <HR tabIndex=-1> <FONT face=Tahoma size=2><B>From:</B> Jared Still [mailto:jkstill@(protected)] <BR><B>Sent:</B> Monday, May 08, 2006 2:06 PM<BR><B>To:</B> Bobak, Mark<BR><B>Cc:</B> Thomas.Mercadante@(protected); alever@(protected); Oracle Freelists.org<BR><B>Subject:</B> Re: Deadlock problem<BR></FONT><BR></DIV> <DIV></DIV>... 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)">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="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204 ,204) 1px solid">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 (See ora-4020.ora-code.com) (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></BODY></HTML>
