RE: Unindexed FK Cause Deadlock or Only Share Lock?

Ethan,

I also had deadlock problems in a Curam application because of missing indexes on Foreign Key problems. I added the indexes and all the problems went away.

Sorry, I don't have a sample for you.

Tom

-----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 2:32 PM
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
--

http://www.freelists.org/webpage/oracle-l Received on Thu Jun 30 2005 - 15:10:14 CDT