December 21, 2012 at 7:33 am
Hello,
Recently I've got a deadlock graph to investigate and noticed it contains SIX lock on page on simple UPDATE statement with WHERE statement including a column which is not indexed (so table scan occurs).
I'm just trying to understand when does SIX lock usually occurs. I'm trying to catch it on my environment, but without success. Also I've googled a lot, but found nothing about this particular type of locks (nothing besides msdn description of course).
Could someone explain on example how to reproduce this lock?
Thanks!
December 21, 2012 at 7:36 am
So, you have read this:
http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx
Shared with intent exclusive (SIX)
Indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources. Concurrent IS locks at the top-level resource are allowed. For example, an SIX lock on a table places an SIX lock on the table (allowing concurrent IS locks), and IX locks on the pages being modified (and X locks on the modified rows). There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.
December 21, 2012 at 7:39 am
Some relevant discussion on this forum:
http://www.sqlservercentral.com/Forums/Topic1227558-1292-1.aspx#bm1227579
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply