RE: Unindexed FK Cause Deadlock or Only Share Lock?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <Ethan.Post@xxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Jun 2005 11:48:44 -0700
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
