Unindexed FK Cause Deadlock or Only Share Lock? 2005-06-30 - By Allen, Brandon
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@(protected)]
Sent: Thursday, June 30, 2005 12:08 PM
To: Reidy, Ron; Allen, Brandon; Oracle-L@(protected) 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...
1) sometimes you get share locks
2) sometimes you get deadlocks
3) 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@(protected)]
Sent: Thursday, June 30, 2005 1:58 PM
To: Post, Ethan; Allen, Brandon; Oracle-L@(protected) 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
|
|
