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