RE: Unindexed FK Cause Deadlock or Only Share Lock?

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

From Metalink Note 38373.1:

Support View: 
~~~~~~~~~~~~~   
ALWAYS use indexes on your foreign keys, except when your master table      is 
static; in that case not indexing your FK may be acceptable. 


From Metalink Note 15476.1:

When indexes are added on child table foreign keys columns, Oracle only require 
 normal 'mode 3 Row-X (SX)' locks on the modified table (parent or child) in  
Oracle 8.1.7 and 9.0.1. In Oracle9.2 onwards, we requires 'mode 2 Row-S (SS)'  
locks on the linked table (child table when modifying the parent table, or  
parent table when modifying the child table). All those DML locks can be 
disabled  via 'ALTER TABLE TABLE_NAME DISABLE TABLE LOCK' without inhibiting 
any DML  activity on both tables. Row level transactional locking can't be 
disabled. (see [NOTE:223303.1])  

When indexes are not present on child table foreign keys columns, Oracle 
requires,  on top of the previous locking situation: a) in 8.1.7, 'mode 4 
Share' locks on the child table when updating/deleting  from the parent table. 
The lock mode even becomes a 'mode 5 S/Row-X (SSX)'  lock when deleting from 
the parent table with a 'delete cascade' foreign key  constraint.Those locks 
can't be disabled (ORA-00069) and are held during the  full transaction time. 
b) in 9.0.1,  Oracle only need those additional locks during the execution time 
 of the UPDATE or DELETE. Those locks are downgraded to 'mode 3 Row-X (SX)'  
locks when the execution is finished. It is thus an improvement compared to  
Oracle 8.1.7. c) in 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no 
longer required  except when deleting from a parent table with a 'delete 
cascade' constraint.  

So, it is recommended to have indexes on the foreign key columns of the  child 
tables in order to avoid this additional locking activity, even if the  
negative effects became less pronounced with Oracle versions.



-----Original Message-----
From: Allen, Brandon 
Sent: Thursday, June 30, 2005 11:39 AM
To: 'Ethan.Post@xxxxxx'; Oracle-L@Freelists. Org (E-mail)
Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?


Yes, unindexed FKs can absolutely cause deadlocks.  I recently had a problem 
with this happening frequently for an application called Khameleon.  After 
adding FK indexes, the deadlocks vanished.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Post, Ethan
Sent: Thursday, June 30, 2005 11:32 AM
To: Oracle-L@Freelists. Org (E-mail)
Subject: Unindexed FK Cause Deadlock or Only Share Lock?



I am trying to find out if a missing index on a foreign key would cause
a deadlock. I always thought is would only cause a share lock and hold
up other DML, not deadlock it.

This asktom link http://tinyurl.com/djgco (search for deadlock and read
comments/responses) seems to suggest you only get a share lock.

However I was sent these links also...some don't seem to support the
idea of deadlocks but others do. Anyone know the answer here? Anyone
think of a quick test case which demonstrates this?

http://asktom.oracle.com/pls/ask/f?p=4950%3A8%3A%3A%3A%3A%3AF4950_P8_DIS
PLAYID%3A1528515465282

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22i
nteg.htm#8565

http://asktom.oracle.com/~tkyte/unindex/index.html

http://www.zanthan.com/itymbi/archives/001548.html

http://www.ixora.com.au/q+a/0103/27142446.htm

http://www.ixora.com.au/q+a/0010/26231626.htm

http://www.fors.com/orasupp/rdbms/misc/11828_1.HTM

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

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

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

Other related posts: