1

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>
Improve this question
1
  • 2
    Please also show the table and index definitions, and share the query plans via pastetheplan.com
    – Charlieface
    Commented Mar 13, 2023 at 17:00

1 Answer 1

Reset to default
2

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:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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