5

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.

4
  • Post the execution plan. I don't get why one process took a SIU lock which encompasses a shared page lock. You are updating exactly one row, right? Are there other statements in the transaction? line="63" => there seem to be many in the proc at least. – usr Jan 16 '15 at 11:02
  • did you check this out? stackoverflow.com/questions/15073547/… – Ozgur Bar Jan 16 '15 at 11:33
  • @usr, yes we have started transaction in EF which gets the record by updlock and do some processing and finally call proc2. So thats why it is having SIU lock. – Sandeep Polavarapu Jan 16 '15 at 11:52
  • Do you have a clustered index on the table? If not, consider changing the primary key index to clustered as that will eliminate this deadlock. That will also eliminate the need to specify Id as an included column in the non-clustered index. – Dan Guzman Jan 16 '15 at 15:12
8

The deadlock diagram is:

Deadlock

The particular issue here is that (4) IX is not compatible with (1) SIU, whereas it is compatible with the more usual IU lock seen in this situation.

The SIU lock occurs because somewhere between selecting the row in question with the UPDLOCK hint (which takes IX at the object level, IU at the page level, and U 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 to SIU. Although shared locks are usually released very quickly under the read committed isolation level, that does not occur when the shared lock is merged into a combination lock like SIU. The lifetime of that lock is the same as the IU 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 the SIU page lock. The page granularity explains why operations on two different id 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:

Probable execution plan

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 including Column1 in the nonclustered primary key, or by changing the primary key from nonclustered to clustered.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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