RE: help with a deadlock explanation/solution
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <chris_stephens@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Apr 2008 18:30:19 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stephens, Chris
Sent: Wednesday, April 30, 2008 12:21 PM
To: oracle-l@xxxxxxxxxxxxx
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)--------
---------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:
