Unindexed FK Cause Deadlock or Only Share Lock? 2005-06-30 - By Allen, Brandon
You can check the trace file created in udump when there is a deadlock and it
will show you the tables involved in the deadlock (you'll have to convert hex
to decimal to find the object_id then look it up in dba_objects). If the
deadlock is on TM type locks, it is likely the result of unindexed FKs, e.g.:
(From the top of one of my deadlock trace files in udump)
*** 2005-01-12 08:56:11.192
*** SESSION ID:(23.22614) 2005-01-12 08:56:11.156
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE AR_CUSTOMER_MASTER SET TOT_ORD_AMT=NVL(TOT_ORD_AMT,0) + :b1 - :b2 ,LAST
_ORD_AMT=:b1 - :b2 + :b5 ,NO_TIMES_ORDERED=NO_TIMES_
ORDERED + :b6 ,LAST_ORD_DATE=:b7 WHERE CUSTOMER_NO = :b8 AND ENTITY_ALL = :b9
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
-- ------Blocker(s)-- ----- -- ------Waiter(s)-- ------
Resource Name process session holds waits process session holds waits
TM-00000f45-00000000 18 23 SX 21 61 S
TM-00000d0f-00000000 21 61 S 18 23 SX
Example from above TM lock:
0f45 = 3909 (you can just use your Windows standard calculator to do the
conversion)
0d0f = 3343
SELECT object_name from dba_objects where object_id in (3909,3343); <- this
will give you the tables involved in the deadlock
|
|
