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  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephens, Chris
Sent: Wednesday, April 30, 2008 12:21 PM To: oracle-l_at_freelists.org
Subject: help with a deadlock explanation/solution  

10.2.0.3  

Analyticalassignee has a deferrable initially deferred foreign key on employee  

Here is the relevant info from the trace ora-00060 file:  

                       ---------Blocker(s)--------

Resource Name process session holds waits process session holds waits

TX-000b000f-00020417 52 74 X 63 96 S

TX-0001002f-00022aa7 63 96 X 52 74 S

session 74: DID 0001-0034-00002889 session 96: DID 0001-003F-00002772

session 96: DID 0001-003F-00002772 session 74: DID 0001-0034-00002889

Rows waited on:

Session 96: obj - rowid = 0000D158 - AAANFYAAHAAADAKAAA

(dictionary objn - 53592, file - 7, block - 12298, slot - 0)

Session 74: obj - rowid = 0000D159 - AAANFZAAHAAADVDAAA

(dictionary objn - 53593, file - 7, block - 13635, slot - 0)

Information on the OTHER waiting sessions:

Session 96:

  pid=63 serial=64236 audsid=3207843 user: 93/ANALYTICAL

  O/S info: user: nobody, term: , ospid: 15675, machine: 050researchas1

            program: httpd_at_xxxx (TNS V1-V3)

  application name: httpd_at_xxxx (TNS V1-V3), hash value=0

  Current SQL Statement:

            INSERT INTO analyticalAssignee (formNumber, assignee, complete, analyte)

            VALUES (:01, :02, :03, :04) End of information on OTHER waiting sessions.

Current SQL statement for this session:

DELETE from basic.EMPLOYEE  

The delete employee statement is part of a nightly refresh of our employee hierarchy that is maintained in another system. That system does not track changes so the hierarchy must be rebuilt each night.  

From the trace file it looks the transaction that inserted into analyticalassignee could not commit until it knew whether the parent record was in employee. Since the employee delete happened first, it had to wait for the commit from the employee refresh transaction.  

The transaction that started with the delete from employee couldn't happen until it knew what all the child records were in anlyticalassignee so it had to wait for the analytical transaction to complete.  

Would that be a correct interpretation?  

The bigger questions is how could the transaction including the insert into analyticalassignee be coded to detect a situation that would result in a deadlock and code around it appropriately?  

Thanks for any help!      

CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

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