RE: Unindexed FK Cause Deadlock or Only Share Lock?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Thu, 30 Jun 2005 12:34:20 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45023611E3@NT15.oneneck.corp>

A share lock can cause a deadlock. "Share" refers to the mode of the lock, i.e. share vs. exclusive. A "deadlock" is not a type/mode of lock, it is just a situation that occurs when to transactions are holding locks and each one wants acess to the locked resource of the other. Thus, you can have a deadlock caused by shared locks. A share lock allows other users to have shared locks for other SELECTS, but a share lock does NOT allow other transactions to perform DML. For example, in the sample trace file I sent earlier:

                       ---------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

Session 23 has a SX lock on table 0f45 and wants an S lock on on table 0d0f, while session 61 is holding an S lock on table 0d0f and wants an SX lock on 0f45. The S & SX locks are mutually exclusive - you can't have both on the same table at the same time.

For more detail, you might want to carefully read "How Oracle Locks Data" in the concepts guide: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#2841

-----Original Message-----

From: Post, Ethan [mailto:Ethan.Post_at_ps.net] Sent: Thursday, June 30, 2005 12:08 PM
To: Reidy, Ron; Allen, Brandon; Oracle-L_at_Freelists. Org (E-mail) Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

That was one of the links in the original email. I see that it attempts to explain the mechanics, and perhaps I am thick and don't understand, but it seems to me it is just telling me why things will run "faster", it doesn't explain the difference in why...

sometimes you get share locks
sometimes you get deadlocks
sometime a fk prevents #2 (I assume some sort of #1 still occurs).

Also the information seems a little old (2003) and the in the link to Tom's stuff he kept seeming to reiterate a share lock when questioners were driving him towards deadlock. Tom carries a lot of weight with me.

-----Original Message-----

From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com] Sent: Thursday, June 30, 2005 1:58 PM
To: Post, Ethan; Allen, Brandon; Oracle-L_at_Freelists. Org (E-mail) Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

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

Ron Reidy
Lead DBA
Array BioPharma, Inc.

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 - 15:38:35 CDT