Help understanding why a deadlock occurred on row level index lock

Question

I have the following deadlock xml

<deadlock>
  <victim-list>
    <victimProcess id="process3340d548c8" />
  </victim-list>
  <process-list>
    <process id="process3340d548c8" taskpriority="0" logused="1676" waitresource="KEY: 5:72057594083016704 (80e6876e1037)" waittime="4843" ownerId="6974726" transactionname="user_transaction" lasttranstarted="2018-05-25T13:52:16.627" XDES="0x330b1b4458" lockMode="U" schedulerid="1" kpid="34260" status="suspended" spid="201" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-25T13:52:16.657" lastbatchcompleted="2018-05-25T13:52:16.657" lastattention="1900-01-01T00:00:00.657" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="6974726" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValueHyperlink_DeleteByAttributeValueIds" queryhash="0xdc817ac17586cee6" queryplanhash="0x8759f1b16359d45e" line="7" stmtstart="340" stmtend="644" sqlhandle="0x03000500f793ca333699da00eba8000001000000000000000000000000000000000000000000000000000000">
DELETE
        AVH
    FROM
        [model].[AttributeValueHyperlink] AVH
    INNER JOIN
        @AttributeValueIdsTable AVT
    ON
        [AVT].EntityId = [AVH].AttributeValueI    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValue_DeleteByAttributeValueIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="10" stmtstart="490" stmtend="660" sqlhandle="0x030005006cae724fc899da00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValueHyperlink_DeleteByAttributeValueIds]
        @AttributeValueId    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValue_DeleteByModelItemIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="13" stmtstart="732" stmtend="918" sqlhandle="0x03000500def65a51d299da00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC model.AttributeValue_DeleteByAttributeValueIds
        @AttributeValueIds = @AttributeValueId    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Generic_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="41" stmtstart="2062" stmtend="2208" sqlhandle="0x0300050096f1cb432e9cda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValue_DeleteByModelItemIds]      
            @ModelItemIdTabl    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Object_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="25" stmtstart="1088" stmtend="1302" sqlhandle="0x0300050061e52c65069dda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[ModelItem_Generic_Delete] 
        @ObjectIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 1697441121]   </inputbuf>
    </process>
    <process id="process3330f29088" taskpriority="0" logused="1744" waitresource="KEY: 5:72057594083016704 (5b32eda0fe69)" waittime="4575" ownerId="6948390" transactionname="user_transaction" lasttranstarted="2018-05-25T13:52:14.370" XDES="0x331cb2c458" lockMode="U" schedulerid="2" kpid="29596" status="suspended" spid="181" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-25T13:52:14.403" lastbatchcompleted="2018-05-25T13:52:14.390" lastattention="1900-01-01T00:00:00.390" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="6948390" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValueHyperlink_DeleteByAttributeValueIds" queryhash="0xdc817ac17586cee6" queryplanhash="0x8759f1b16359d45e" line="7" stmtstart="340" stmtend="644" sqlhandle="0x03000500f793ca333699da00eba8000001000000000000000000000000000000000000000000000000000000">
DELETE
        AVH
    FROM
        [model].[AttributeValueHyperlink] AVH
    INNER JOIN
        @AttributeValueIdsTable AVT
    ON
        [AVT].EntityId = [AVH].AttributeValueI    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValue_DeleteByAttributeValueIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="10" stmtstart="490" stmtend="660" sqlhandle="0x030005006cae724fc899da00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValueHyperlink_DeleteByAttributeValueIds]
        @AttributeValueId    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValue_DeleteByModelItemIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="13" stmtstart="732" stmtend="918" sqlhandle="0x03000500def65a51d299da00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC model.AttributeValue_DeleteByAttributeValueIds
        @AttributeValueIds = @AttributeValueId    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Generic_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="41" stmtstart="2062" stmtend="2208" sqlhandle="0x0300050096f1cb432e9cda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValue_DeleteByModelItemIds]      
            @ModelItemIdTabl    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Relationship_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="23" stmtstart="1078" stmtend="1302" sqlhandle="0x030005000d989e704f9dda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[ModelItem_Generic_Delete]
        @RelationshipIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 1889441805]   </inputbuf>
    </process>
    <process id="process330f11fc28" taskpriority="0" logused="32948" waitresource="KEY: 5:72057594083016704 (80e6876e1037)" waittime="2558" ownerId="6941127" transactionname="user_transaction" lasttranstarted="2018-05-25T13:52:13.970" XDES="0x33199a4458" lockMode="U" schedulerid="2" kpid="91236" status="suspended" spid="193" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-25T13:52:21.987" lastbatchcompleted="2018-05-25T13:52:21.983" lastattention="1900-01-01T00:00:00.983" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="6941127" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValueHyperlink_DeleteByAttributeValueIds" queryhash="0xdc817ac17586cee6" queryplanhash="0x8759f1b16359d45e" line="7" stmtstart="340" stmtend="644" sqlhandle="0x03000500f793ca333699da00eba8000001000000000000000000000000000000000000000000000000000000">
DELETE
        AVH
    FROM
        [model].[AttributeValueHyperlink] AVH
    INNER JOIN
        @AttributeValueIdsTable AVT
    ON
        [AVT].EntityId = [AVH].AttributeValueI    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValue_DeleteByAttributeValueIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="10" stmtstart="490" stmtend="660" sqlhandle="0x030005006cae724fc899da00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValueHyperlink_DeleteByAttributeValueIds]
        @AttributeValueId    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValue_DeleteByModelItemIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="13" stmtstart="732" stmtend="918" sqlhandle="0x03000500def65a51d299da00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC model.AttributeValue_DeleteByAttributeValueIds
        @AttributeValueIds = @AttributeValueId    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Generic_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="41" stmtstart="2062" stmtend="2208" sqlhandle="0x0300050096f1cb432e9cda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValue_DeleteByModelItemIds]      
            @ModelItemIdTabl    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Relationship_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="23" stmtstart="1078" stmtend="1302" sqlhandle="0x030005000d989e704f9dda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[ModelItem_Generic_Delete]
        @RelationshipIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.RelationshipPair_DeleteByModelItemIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="21" stmtstart="1252" stmtend="1462" sqlhandle="0x03000500bc1cd015159eda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[ModelItem_Relationship_Delete]
        @RelationshipIds,
        0,
        @DeletedBy,
        @DeletedOn,    </frame>
        <frame procname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.ModelItem_Object_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="20" stmtstart="878" stmtend="1076" sqlhandle="0x0300050061e52c65069dda00eba8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[RelationshipPair_DeleteByModelItemIds]
        @ObjectIdTable,
        @DeletedBy,
        @DeletedO    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 1697441121]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594083016704" dbid="5" objectname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValueHyperlink" indexname="IX_AttributeValueHyperlink_AttributeValueId" id="lock3320f42880" mode="U" associatedObjectId="72057594083016704">
      <owner-list>
        <owner id="process3330f29088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process3340d548c8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594083016704" dbid="5" objectname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValueHyperlink" indexname="IX_AttributeValueHyperlink_AttributeValueId" id="lock3320f4fb00" mode="X" associatedObjectId="72057594083016704">
      <owner-list>
        <owner id="process330f11fc28" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process3330f29088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594083016704" dbid="5" objectname="7b7e4b64-e8dd-4a72-8f98-447678798791.model.AttributeValueHyperlink" indexname="IX_AttributeValueHyperlink_AttributeValueId" id="lock3320f42880" mode="U" associatedObjectId="72057594083016704">
      <owner-list>
        <owner id="process3340d548c8" mode="U" requestType="wait" />
      </owner-list>
      <waiter-list>
        <waiter id="process330f11fc28" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

As far as I can understand, we have 3 row level locks on the IX_AttributeValueHyperlink_AttributeValueId index. I'm not sure why some of them (process330f11fc28) has an X-lock on this index, but the others don't.

Also the execution plan for this delete looks like this

I don't understand why the deadlock is happening. Everything seems ok.

This is on a sql azure database by the way, so it's using RCSI isolation level, however our transactions are setup (in the C# layer) to use read committed.

Paul White · Accepted Answer · 2018-05-29 11:25:01Z

I don't understand why the deadlock is happening.

For this execution plan, the sequence of locking operations involved in deleting each row is:

U lock nonclustered index (taken at the index seek)
U lock clustered index (taken at the delete operator)
X lock clustered index (at the delete operator)
X lock nonclustered index (at the delete operator)

I'm not sure why ... process330f11fc28 has an X-lock on this index, but the others don't.

The plan has no blocking operators, so it is a simple pipeline (roughly speaking, each row gets to the end of the pipeline before the next one is processed).

When the deadlock occured, one process (session 193) had an X lock on a nonclustered index row (final step above). Sessions 181 and 201 were blocked at the first step, trying to get an incompatible U lock on the same nonclustered index row session 193 has exclusively locked.

I apologise in advance that the detailed explanation is somewhat involved.

Internal update locks

The update lock on the nonclustered index is taken automatically by the engine to avoid a common type of conversion deadlock, which occurs where two processes acquire an S lock on the same resource, then both try to convert to X. Each is prevented from converting S to X by the other, so a deadlock occurs.

Taking a U lock prevents this because U is compatible with S but not another U. Naturally S locks are not normally taken under RCSI, but these U locks are. This avoids attempting to update a stale version of the row.

The automatic U lock is taken under RCSI only for the instance of the table that provides the row locator for the update operation. Other tables in the query (including any additional references to the update target) continue to use row versioning.

These automatic U locks have a different lifetime from regular update locks (such as might be taken with an UPDLOCK hint). Regular U locks are held to the end of the transaction. Internal U locks are held to the end of the statement, with an exception: if the same plan operator that took the lock can deduce that the row does not qualify for the update, the lock is released immediately.

See my article Data Modifications under Read Committed Snapshot Isolation.

Cyclic deadlock

This automatic U lock does not provide protection from a cyclic deadlock. Two transactions that modify resource A and resource B inside a transaction, but in reverse order, are guaranteed to deadlock:

Transaction T1 modifies row R1 (but does not commit or abort).
Transaction T2 modifies row R2 (but does not commit or abort).
T1 attempts to lock row R2 and blocks (T2 has an incompatible lock).
T2 attempts to lock row R1 and blocks (T1 has an incompatible lock).
Deadlock.

Where "modifies" above includes insert, update, delete etc.

Specific deadlock

The example in the question is a variation on this theme, where:

Session 193 has deleted row R1, holding X on that row
Session 193 is waiting to acquire U on row R2
Session 181 owns a U lock on R2
Session 181 is waiting to acquire U on R1
Deadlock

(session 201 is also waiting to acquire U on row R2 but it is an innocent bystander.)

To be clear: The exact deadlock sequence above cannot occur for the precise execution plan shown in the question. Session 181 could not hold U on R2 and go on to request U on R1 due to the lack of a blocking operator, and/or separation between acquire and release points for the nonclustered U lock. Any U locked row found by the index seek is guaranteed to get converted to X before the next seek row is processed.

Nevertheless, just because that is the plan for the statement now, does not mean that was the plan when the deadlock occurred. For example, when a statement-level recompile occurs SQL Server can see the cardinality of the table variable. This could well lead to a hash join plan instead.

Hash join plan

In a hash join plan, rows from the table variable would be used to build a hash table. Once that is completed, SQL Server can start reading rows from AttributeValueHyperlink, taking a U lock on each row emitted by the index scan (there is nothing to seek on now).

At the hash join, each probe-side row is evaluated against the join predicate. If a match is found the row goes on to the Clustered Index Delete operator, where the clustered U, X, and nonclustered X locks are taken as part of locating and deleting the entries corresponding to the current row.

However, if the row does not join at the hash join, the U lock is not released. The U locks for unjoined rows will continue to accumulate until they are all released when the current statement ends. This is simply a consequence of the U locks being taken at one operator (nonclustered index scan) but tested for eligibility at another (the hash join).

Anyway, multiple U locks make the reported deadlock possible.

Avoiding the deadlock

Of course the simple nested loops plan might also deadlock when processing the same data (the locks would just be more obviously a cyclic deadlock). To avoid the deadlock, you would need to ensure the input sets are disjoint, or the rows in each set are processed in strictly the same order (sorted the same way, and processed in the same sequence by the execution plan).

David Browne - Microsoft · Accepted Answer · 2018-05-25 20:00:53Z

1

Each session is deleting multiple rows from the same table based on the value in a secondary index. Even if you don't attempt to delete the same row in multiple sessions, the sessions will search for the rows to delete using the secondary index, reading with a U lock, and then convert to an X lock for each row. This creates the possibility of deadlocks.

You can avoid this by searching for the rows to delete without a U lock, and then deleting them in a separate statement. Some of the rows you find in the first query may be deleted by the time you attempt the second. But you probably don't care about that.

So something like:

     declare @ids_to_delete table(id int)

     insert into @ids_to_delete(id)
     select id
     from [model].[AttributeValueHyperlink] AVH
     INNER JOIN @AttributeValueIdsTable AVT
     ON
        [AVT].EntityId = [AVH].AttributeValueI 

     delete from  [model].[AttributeValueHyperlink] 
     where id in (select id from @ids_to_delete)

edited May 25, 2018 at 20:00

answered May 25, 2018 at 19:10

David Browne - Microsoft

47.6k3 gold badges51 silver badges101 bronze badges

I tried this. Unfortunately it just pushed the issue upwards. I now have a page-level deadlock on the table itself (or more precisely the clustered index).
– Umair
Commented May 29, 2018 at 16:30
Well, you can always use a TABLOCK hint to ensure that the DELETEs run one session at a time.
– David Browne - Microsoft
Commented May 29, 2018 at 17:23
Aha I see. When would the TABLOCK be released? At the end of the delete statement or the end of the transaction?
– Umair
Commented May 29, 2018 at 18:05

Add a comment |

Stack Exchange Network

Help understanding why a deadlock occurred on row level index lock

2 Answers 2

Internal update locks

Cyclic deadlock

Specific deadlock

Hash join plan

Avoiding the deadlock

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
deadlock
azure-sql-database
or ask your own question.

Linked

Hot Network Questions

Help understanding why a deadlock occurred on row level index lock

2 Answers 2

Internal update locks

Cyclic deadlock

Specific deadlock

Hash join plan

Avoiding the deadlock

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
deadlock
azure-sql-database
or ask your own question.