Question
Can somebody explain me how a process can acquire SIX lock on a page? I my deadlock-graph xml file I see that a process running under RC isolation level (executing a select statement at the moment of deadlock) holds a SIX lock on a page.
What does this mean and how that lock could have been acquired? From what I got from http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx SIX locks protects S-locks on all resources and IX locks on some resources lower in the hierarchy.
For my case that would be IX-locks on rows? Can IX-lock be placed on a row? (I guess no). I am confused.
Another thing is that I expect several X-locks on rows and no S-locks at all (since the IL is ReadCommited). Why do I have the whole page locked with SIX if I only inserted several records in previous statement?
up!
Answer by Sebastian Meine
To answer that I have to take a little detour, so bear with me. If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not “compatible” with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource and X locks are taken to write to a resource. S locks are compatible with each other, X locks are not compatible with anything else. U locks are a hybrid that is used in some cases for deadlock prevention.
Now, SQL Server can take locks on several levels:Table, Partition, Page and Row. So if session one takes a table lock and session two takes a non-compatible lock on one row of the table, those two locks are not on the same resource and SQL Server won’t detect the collision. To protect against that, SQL Server always starts to take a lock on the table level and works its way down the hierarchy. Now the point of page and row locks is higher concurrency, so if one session wants to write to one row and another session wants to write to another row, they should not block each other. If a session in addition to taking a lock on a row also has to take the same lock on the table, that advantage is gone. So instead of taking an exclusive lock (X) on the table, the session requests an intend-exclusive lock (IX). This lock is compatible with other intend locks but not with other “real” locks. So another session can take an intend-exclusive lock on the same table as well. The intend-exclusive lock says, that the session intends to take an exclusive lock on a lower level resource. The same happens on the page level, if the intended lock is a row lock, so after all is done, the session has an IX lock on the table and on one of the pages and an X lock on one of the rows in that page. This also means, that you will never find an intend lock on a row as rows are the lowest level in the lock hierarchy.
In some circumstances a session holds an S lock on the table or a page. If the session now (within the same transaction) requests an X lock on a row in that same table, it first has to take an IX lock on the table/page. However, a session can hold only one lock on any given resource. So to take the IX lock, it would have to release the S lock wich is probably not desired, so SQL Server offers a combination: SIX.
The reason why you have a page lock is due to SQL Server sometimes deciding that it would be better to lock the page instead of locking each row. That happens often if there are very many locks taken between all sessions already, but can have many other reasons too.
So far the theory.
Now in your case the SIX lock is held by a three table join select query. A select never takes any type of lock that is not a shared lock unless you explicitly tell it to (e.g. with a XLOCK hint). Such a hint is not visible within the input buffer, so I assume the IX part is a left over from the last batch on this connection. If you are using connection pooling and forget to cleanup all open transactions, such a lock can live potentially forever. But it becomes also very hard to troubleshoot.
You could start by running an XEvent session that pairs OPEN TRANs with COMMITs and see if you can find the culprit that way.
Article link: http://xnerv.wang/understanding-six-lock-in-sql-server/
Reprinted from: (StackOverflow) Understanding SIX lock in SQL Server