RE: Unindexed FK Cause Deadlock or Only Share Lock?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Thu, 30 Jun 2005 11:48:44 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45023611E2@NT15.oneneck.corp>

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_at_ps.net'; Oracle-L_at_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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Post, Ethan Sent: Thursday, June 30, 2005 11:32 AM
To: Oracle-L_at_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

Received on Thu Jun 30 2005 - 14:53:00 CDT