Why my clustered index causing deadlock (Keylocks and Pagelocks)

Question

We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously.

We are actually using EF to fetch data and the query involves more than 6 foreign key relations.

But on some days, the system hangs more than 6 times a day due to deadlocks. We have one clustered index column in our main table (Primary key field)

I shared the deadlock graph for your reference

<deadlock>
  <victim-list>
    <victimProcess id="process25d6ac3a6e2" />
  </victim-list>
  <process-list>
    <process id="process25d6ac3a6e2" taskpriority="0" logused="0" waitresource="KEY: 8:72057594092519424 (4343973e1238)" waittime="5297" ownerId="347172198" transactionname="SELECT" lasttranstarted="2023-03-08T15:13:05.167" XDES="0x19132613ba0" lockMode="S" schedulerid="7" kpid="9072" status="suspended" spid="86" sbid="2" ecid="12" priority="0" trancount="0" lastbatchstarted="2023-03-08T15:13:05.160" lastbatchcompleted="2023-03-08T15:13:05.163" lastattention="1900-01-01T00:00:00.163" clientapp=".Net SqlClient Data Provider" hostname="UKPG859CK" hostpid="3648" isolationlevel="read committed (2)" xactid="347172198" currentdb="8" currentdbname="ABP" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="452" stmtend="72870" sqlhandle="0x020000000d43691b40a21b2caa8564509985cf4d5ae9c3f30000000000000000000000000000000000000000">
          unknown
        </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
          unknown
        </frame>
      </executionStack>
      <inputbuf>
        (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT
        [Project4].[C3] AS [C1],
        [Project4].[C59] AS [C2],
        [Project4].[C60] AS [C3],
        [Project4].[C61] AS [C4],
        [Project4].[C62] AS [C5],
        [Project4]
      </inputbuf>
    </process>
    <process id="process25cdc56b085" taskpriority="0" logused="764" waitresource="PAGE: 8:1:528312 " waittime="4959" ownerId="347171929" transactionname="user_transaction" lasttranstarted="2023-03-08T15:13:05.060" XDES="0x18bda194420" lockMode="IX" schedulerid="2" kpid="7052" status="suspended" spid="73" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-03-08T15:13:05.057" lastbatchcompleted="2023-03-08T15:13:05.053" lastattention="1900-01-01T00:00:00.053" clientapp=".Net SqlClient Data Provider" hostname="UKPG859CK" hostpid="3648" loginname="LOGG\MKSAM" isolationlevel="read committed (2)" xactid="347171929" currentdb="8" currentdbname="ABP" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="136" stmtend="498" sqlhandle="0x020000002d65c221bca694a98771b8d919bc9686028cea870000000000000000000000000000000000000000">
          unknown
        </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
          unknown
        </frame>
      </executionStack>
      <inputbuf>
        (@0 int,@1 datetime2(7),@2 int,@3 datetime2(7),@4 int,@5 int,@6 int)UPDATE [dbo].[Mapping]
        SET [AssToId] = NULL, [MapStatus] = @0, [Updated] = @1, [Umag] = @2, [MappingCompleteDate] = @3, [DActive] = @4, [Old_Umag] = @5
        WHERE ([Id] = @6)
      </inputbuf>
    </process>
    <process id="process1913e995c28" taskpriority="0" logused="10000" waittime="4971" schedulerid="6" kpid="12888" status="suspended" spid="86" sbid="2" ecid="4" priority="0" trancount="0" lastbatchstarted="2023-03-08T15:13:05.160" lastbatchcompleted="2023-03-08T15:13:05.163" lastattention="1900-01-01T00:00:00.163" clientapp=".Net SqlClient Data Provider" hostname="UKPG859CK" hostpid="3648" isolationlevel="read committed (2)" xactid="347172198" currentdb="8" currentdbname="ABP" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="452" stmtend="72870" sqlhandle="0x020000000d43691b40a21b2caa8564509985cf4d5ae9c3f30000000000000000000000000000000000000000">
          unknown
        </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
          unknown
        </frame>
      </executionStack>
      <inputbuf>
        (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT
        [Project4].[C3] AS [C1],
        [Project4].[C59] AS [C2],
        [Project4].[C60] AS [C3],
        [Project4].[C61] AS [C4],
        [Project4].[C62] AS [C5],
        [Project4]
      </inputbuf>
    </process>
    <process id="process25e0523b858" taskpriority="0" logused="10000" waittime="5446" schedulerid="7" kpid="9368" status="suspended" spid="86" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2023-03-08T15:13:05.160" lastbatchcompleted="2023-03-08T15:13:05.163" lastattention="1900-01-01T00:00:00.163" clientapp=".Net SqlClient Data Provider" hostname="UKPG859CK" hostpid="3648" loginname="LOGG\MKSAM" isolationlevel="read committed (2)" xactid="347172198" currentdb="8" currentdbname="ABP" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="452" stmtend="72870" sqlhandle="0x020000000d43691b40a21b2caa8564509985cf4d5ae9c3f30000000000000000000000000000000000000000">
          unknown
        </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
          unknown
        </frame>
      </executionStack>
      <inputbuf>
        (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT
        [Project4].[C3] AS [C1],
        [Project4].[C59] AS [C2],
        [Project4].[C60] AS [C3],
        [Project4].[C61] AS [C4],
        [Project4].[C62] AS [C5],
        [Project4]
      </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594092519424" dbid="8" objectname="ABP.dbo.Mapping" indexname="PK_Mapping" id="lock18d1bba9980" mode="X" associatedObjectId="72057594092519424">
      <owner-list>
        <owner id="process25cdc56b085" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process25d6ac3a6e2" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <pagelock fileid="1" pageid="528312" dbid="8" subresource="FULL" objectname="ABP.dbo.Mapping" id="lock18d26c14a80" mode="S" associatedObjectId="72057594092650496">
      <owner-list>
        <owner id="process25e0523b858" mode="S" />
        <owner id="process25d6ac3a6e2" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process25cdc56b085" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
    <exchangeEvent id="Pipe18d77cc6780" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="23" tid="1" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process25d6ac3a6e2" />
      </owner-list>
      <waiter-list>
        <waiter id="process1913e995c28" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Port191470d4700" WaitType="e_waitPortOpen" waiterType="Coordinator" nodeId="0" tid="0" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
      <owner-list>
        <owner id="process1913e995c28" />
      </owner-list>
      <waiter-list>
        <waiter id="process25e0523b858" />
      </waiter-list>
    </exchangeEvent>
  </resource-list>
</deadlock>

score 2 · Accepted Answer · answered Mar 13 '23 at 16:32

most likely

You have a read query deadlocking with a write query, which means one or both query plans have one or more key lookups in them.

  <owner-list>
    <owner id="process25cdc56b085" mode="X" />
  </owner-list>
  <waiter-list>
    <waiter id="process25d6ac3a6e2" mode="S" requestType="wait" />
  </waiter-list>

You may be able to track down the query plans and look for tuning opportunities by using sp_BlitzCache:

/*select query*/
EXEC sp_BlitzCache 
    @OnlySqlHandles = '0x020000000d43691b40a21b2caa8564509985cf4d5ae9c3f30000000000000000000000000000000000000000';
/update query/
EXEC sp_BlitzCache 
    @OnlySqlHandles = '0x020000002d65c221bca694a98771b8d919bc9686028cea870000000000000000000000000000000000000000';

Other things to review:

Indexing for the foreign keys you mentioned, especially if they have any cascading actions assigned to them
Using an optimistic isolation level to avoid reader/writer blocking and deadlocking

Why my clustered index causing deadlock (Keylocks and Pagelocks)

1 Answers1

most likely

Linked