6

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?

EDIT: Deadlock xml:

<deadlock-list>
 <deadlock victim="process4df94c8">
  <process-list>
   <process id="process4df94c8" taskpriority="0" logused="2968" waitresource="PAGE: 7:1:181357" waittime="3111" ownerId="41854656297" transactionname="user_transaction" lasttranstarted="2013-06-06T11:09:42.087" XDES="0x1d2434e80" lockMode="IX" schedulerid="6" kpid="3476" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-06-06T11:09:42.183" lastbatchcompleted="2013-06-06T11:09:42.183" clientapp=".Net SqlClient Data Provider" hostname="CWCEINAW" hostpid="4260" loginname="cwcuser" isolationlevel="read committed (2)" xactid="41854656297" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="508" stmtend="1358" sqlhandle="0x02000000876d6b08786774c344ce9ce8bd077ccce54751be">
INSERT [WP_CashCenter_StockTransactionLine] ([isverified], [isstockownerchanged], [dateupdated], [quantity], [value], [weight], [qualificationtype], [direction], [material_id], [stockcontainer_id], [stockownerid], [stocktransaction_id]) VALUES (@isverified, @isstockownerchanged, @dateupdated, @quantity, @value, @weight, @qualificationtype, @direction, @material_id, @stockcontainer_id, @stockownerid, @stocktransaction_id);     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@isverified bit,@isstockownerchanged bit,@dateupdated datetime,@quantity int,@value decimal(4,2),@weight decimal(8,8),@qualificationtype int,@direction int,@material_id nvarchar(3),@stockcontainer_id bigint,@stockownerid int,@stocktransaction_id bigint)INSERT [WP_CashCenter_StockTransactionLine] ([isverified], [isstockownerchanged], [dateupdated], [quantity], [value], [weight], [qualificationtype], [direction], [material_id], [stockcontainer_id], [stockownerid], [stocktransaction_id]) VALUES (@isverified, @isstockownerchanged, @dateupdated, @quantity, @value, @weight, @qualificationtype, @direction, @material_id, @stockcontainer_id, @stockownerid, @stocktransaction_id); SELECT scope_identity()    </inputbuf>
   </process>
   <process id="process5cd948" taskpriority="0" logused="24656" waitresource="KEY: 7:72057594277003264 (889d2c878f57)" waittime="3098" ownerId="41854656065" transactionname="user_transaction" lasttranstarted="2013-06-06T11:09:41.970" XDES="0x1253053c0" lockMode="S" schedulerid="3" kpid="4116" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-06-06T11:09:42.180" lastbatchcompleted="2013-06-06T11:09:42.177" lastattention="2013-06-03T13:13:45.090" clientapp=".Net SqlClient Data Provider" hostname="CWCEINAW" hostpid="4260" loginname="cwcuser" isolationlevel="read committed (2)" xactid="41854656065" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x020000009e156e12789f4ef811698c627d479b0240c2a7c1">
SELECT * FROM WP_CashCenter_StockTransaction
                WHERE [id] IN (
                    SELECT DISTINCT [ST].[id]
                    FROM WP_CashCenter_StockTransaction AS [ST]
                        LEFT JOIN WP_CashCenter_StockTransactionLine AS [STL] ON ([STL].[StockTransaction_id] = [ST].[id])
                    WHERE [ST].[Type] IN (1, 0, 10, 9)
AND ([STL].[Direction] IN (1, 0) OR [STL].[id] IS NULL) 
AND [ST].[Status] IN (0, 1)
AND ([STL].[StockContainer_id] = 300000274211 OR [ST].[StockContainerID] = 300000274211))     </frame>
    </executionStack>
    <inputbuf>
SELECT * FROM WP_CashCenter_StockTransaction
                WHERE [id] IN (
                    SELECT DISTINCT [ST].[id]
                    FROM WP_CashCenter_StockTransaction AS [ST]
                        LEFT JOIN WP_CashCenter_StockTransactionLine AS [STL] ON ([STL].[StockTransaction_id] = [ST].[id])
                    WHERE [ST].[Type] IN (1, 0, 10, 9)
AND ([STL].[Direction] IN (1, 0) OR [STL].[id] IS NULL) 
AND [ST].[Status] IN (0, 1)
AND ([STL].[StockContainer_id] = 300000274211 OR [ST].[StockContainerID] = 300000274211))    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="181357" dbid="7" objectname="Eindhoven_CWC.dbo.WP_CashCenter_StockTransactionLine" id="lock366ad3a80" mode="SIX" associatedObjectId="72057594277265408">
    <owner-list>
     <owner id="process5cd948" mode="SIX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4df94c8" mode="IX" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <keylock hobtid="72057594277003264" dbid="7" objectname="Eindhoven_CWC.dbo.WP_CashCenter_StockTransaction" indexname="PK_WP_Inbound_StockTransaction" id="lockee362e00" mode="X" associatedObjectId="72057594277003264">
    <owner-list>
     <owner id="process4df94c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5cd948" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
2
  • Can you share your deadlock XML?
    – JNK
    Jun 26, 2013 at 12:53
  • @JNK, yes, off course, I've edited the post. Although the main point of my question is that you help me to solve the deadlock, I just want to understand SIX lock.
    – Artur Udod
    Jun 26, 2013 at 13:06

1 Answer 1

18

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

4
  • thank you for the excellent answer! No, Im sure that I correctly close my transactions + asp.net connection pooling executes sp_reset_connection each time it take a connection from the pool.
    – Artur Udod
    Jun 27, 2013 at 8:39
  • So the reason is just that previous batches in that same transaction have taken X-locks. That's expected.
    – Artur Udod
    Jun 27, 2013 at 8:41
  • The thing that confused me is somewhat incorrect definition of SIX-lock from msdn (as it seems to me): msdn.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx: Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. This is correct only for SIX lock on a table, but not for a page, since for SIX lock on a page there are no intent-exclusive locks on lower levels of hierarchy - only exclusive ones.
    – Artur Udod
    Jun 27, 2013 at 8:45
  • Your answer made things really clear for me. Thank you again.
    – Artur Udod
    Jun 27, 2013 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.