RE: help with a deadlock explanation/solution

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Stephens, Chris'" <chris_stephens@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 1 May 2008 16:19:46 -0400

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@xxxxxxxxxxxx] 
Sent: Thursday, May 01, 2008 9:13 AM
To: Mark W. Farnham; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxx] 
Sent: Wednesday, April 30, 2008 5:30 PM
To: Stephens, Chris; oracle-l@xxxxxxxxxxxxx
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>

Other related posts: