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:43:08 CDT