We have 2 different wcf services which get invoked by a windows service. The methods in those wcf services are updating the same table inside different stored procedures. One thing is for sure: records which are getting updated in these 2 procs are different. Here is the deadlock graph xml:
<deadlock victim="process4cad4c8">
<process-list>
<process id="process4cad4c8" taskpriority="0" logused="0" waitresource="PAGE: 11:1:960" waittime="3687" ownerId="1043067366" transactionname="UPDATE" lasttranstarted="2015-01-06T04:01:31.207" XDES="0x19b797950" lockMode="IX" schedulerid="7" kpid="3124" status="suspended" spid="103" sbid="6" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-01-06T04:01:31.197" lastbatchcompleted="2015-01-06T03:53:28.053" clientapp="EntityFramework" hostname="hostname" hostpid="1332" loginname="loginname" isolationlevel="read committed (2)" xactid="1043067366" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="DB.schema.Proc1" line="63" stmtstart="5600" stmtend="6756" sqlhandle="0x03000b002bcbd65c9605370018a400000100000000000000">
UPDATE Table1 WITH (ROWLOCK)
SET Column1 = @Column1
WHERE Id = @Id </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 11 Object Id = 1557580587] </inputbuf>
</process>
<process id="process8b54c8" taskpriority="0" logused="308" waitresource="KEY: 11:72057594041008128 (6a28efb36b7a)" waittime="3707" ownerId="1043066108" transactionname="user_transaction" lasttranstarted="2015-01-06T04:01:30.750" XDES="0x81bed950" lockMode="X" schedulerid="3" kpid="5420" status="suspended" spid="104" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-01-06T04:01:31.270" lastbatchcompleted="2015-01-06T04:01:31.270" clientapp="EntityFramework" hostname="hostname" hostpid="1332" loginname="loginname" isolationlevel="read committed (2)" xactid="1043066108" currentdb="11" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="DB.schema.Proc2" line="11" stmtstart="432" stmtend="754" sqlhandle="0x03000b00bac88363cfbb3d0018a400000100000000000000">
UPDATE Table1 WITH (ROWLOCK)
SET Column1 = @Column1,
Column2 = @Column2
WHERE Id = @Id </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 11 Object Id = 1669580986] </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="960" dbid="11" objectname="DB.schema.Table1" id="lockf5976b00" mode="SIU" associatedObjectId="72057594039500800">
<owner-list>
<owner id="process8b54c8" mode="SIU"/>
</owner-list>
<waiter-list>
<waiter id="process4cad4c8" mode="IX" requestType="convert"/>
</waiter-list>
</pagelock>
<keylock hobtid="72057594041008128" dbid="11" objectname="DB.schema.Table1" indexname="IX_Table1_Column1" id="lock133959780" mode="U" associatedObjectId="72057594041008128">
<owner-list>
<owner id="process4cad4c8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process8b54c8" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Here is the index we have which is one of the culprits in the deadlock:
CREATE NONCLUSTERED INDEX [IX_Table1_Column1] ON [schema].[Table1]
(
[Column0] ASC,
[Column1] ASC
)
INCLUDE
(
[Id],
[Column2]
)
WITH
(
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90
)
ON [PRIMARY];
We have a primary key nonclustered index on column Id
.
Best Answer
The deadlock diagram is:
The particular issue here is that (4)
IX
is not compatible with (1)SIU
, whereas it is compatible with the more usualIU
lock seen in this situation.The
SIU
lock occurs because somewhere between selecting the row in question with theUPDLOCK
hint (which takesIX
at the object level,IU
at the page level, andU
at the row level), a statement in the transaction is acquiring a shared (S
) page-level lock on the nonclustered primary key index structure.The existing
IU
page lock is converted toSIU
. Although shared locks are usually released very quickly under theread committed
isolation level, that does not occur when the shared lock is merged into a combination lock likeSIU
. The lifetime of that lock is the same as theIU
component (the life of the transaction here).You can avoid this specific deadlock by ensuring page-level shared locks are not taken on the same access method after the
UPDLOCK
statement (row-level shared locks would be ok). This will avoid theSIU
page lock. The page granularity explains why operations on two differentid
values can deadlock.That said, unless the question is missing some detail, you will still have an update statement that is prone to deadlock in another way. The base table appears to be a heap, and the update plan will likely feature an RID lookup:
This is a well-known pattern that often results in a conversion deadlock under high concurrency. The reading side of the plan accesses the nonclustered index first, then the base table. The update side of the plan accesses the base table first, then the nonclustered index. By touching the same resources in reverse order, multiple concurrent executions of the same plan can deadlock.
The RID lookup is needed for the query processor to check if the value of
Column1
is being changed. If it is not changing, a redundant nonclustered index update can be avoided. You can remove this RID lookup by includingColumn1
in the nonclustered primary key, or by changing the primary key from nonclustered to clustered.