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>