RE: help with a deadlock explanation/solution

Oh, you wanted a solution as opposed to general advice. Okay, risking being wrong without the entire code between the relevant commits of both sessions and constraint references to the objects involved, if you add a select for update from employee for the assignee in the transaction doing the insert into analyticalAssignee before the insert, then probably your deadlocks will go away.  

That makes ...,employee,..,analyticalassignee,. a fragment of your order of tables list.  

By the way camelCase is ugly and error prone - stick an underbar in there, though I suppose de gustibus non est disputandem.  

That's a guess. Let me know how it works out.  

mwf

From: Stephens, Chris [mailto:chris_stephens_at_admworld.com] Sent: Thursday, May 01, 2008 9:13 AM
To: Mark W. Farnham; oracle-l_at_freelists.org Subject: RE: help with a deadlock explanation/solution  

This is kind of what I don't understand.  

"Delete from employee" doesn't affect any data in Analyticalassignee. There are foreign keys referencing employee from analyticalassignee that must be validated once the trasaction involving "Delete from employee" completes.  

An insert into alayticalassignee doesn't affect any data in employee.it just has to validate that the employeenumber exists for the foreign key problems.  

I don't 100% understand how this is causing a deadlock condition. .but I'm planning on re-reading the concepts manual and brought in my copy of Expert Oracle Database Architecture to get this in my head.    

chris

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Wednesday, April 30, 2008 5:30 PM
To: Stephens, Chris; oracle-l_at_freelists.org Subject: RE: help with a deadlock explanation/solution  

Most deadlocks of this sort can be avoided by designating an order of tables for transactions. Once all transactions insert/update/delete from tables in the same order Oracle's routine row level locking eliminates the vast majority of deadlock situations. ( If you have more current updaters than rows and itls in a given block it is still possible to have coincidental deadlocks due to being unable to proceed due to the block not being able to accommodate the next parallel transaction. Whether bumping initrans or limiting rows per block results in a better data density for you in these cases will vary. ) But just consistently using the defined order of tables for transactions usually eliminates most of the deadlock possibilities.  

Regards,  

mwf

<snip>

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