RE: Deadlock problem

Huh?  

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_at_gmail.com] 
Sent: Monday, May 08, 2006 2:06 PM
To: Bobak, Mark
Cc: Thomas.Mercadante_at_labor.state.ny.us; alever_at_libero.it; Oracle
Freelists.org
Subject: 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
<mailto: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