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)--------
---------Waiter(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@xxxx (TNS V1-V3)

  application name: httpd@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.

Other related posts: