0

In SQL Server I am issuing the following query:

DELETE FROM TableA WHERE tableB_id IN (SELECT ID from TableB WHERE FileID = @P0)

The query is being called in parallel by multiple processes and which is causing a page-level deadlock issue.

I also have NONCLUSTERED INDEX defined on tableB_id column of TableA which is FK as well.

I am also sharing deadlock XML below. Can anyone help me how can I prevent this deadlock?

<deadlock>
  <victim-list>
    <victimProcess id="process1a0de90e108" />
  </victim-list>
  <process-list>
    <process id="process1a0de90e108" taskpriority="0" logused="2436" waitresource="PAGE: 6:1:17997419 " waittime="4726" ownerId="3967991339" transactionname="implicit_transaction" lasttranstarted="2021-06-07T21:18:08.423" XDES="0x19fa9470428" lockMode="U" schedulerid="2" kpid="7264" status="suspended" spid="2460" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-06-07T21:18:08.777" lastbatchcompleted="2021-06-07T21:18:08.777" lastattention="1900-01-01T00:00:00.777" clientapp="Microsoft JDBC Driver for SQL Server" hostname="test_app-7c98f79f44-mz8jf" hostpid="0" loginname=test_user isolationlevel="read committed (2)" xactid="3967991339" currentdb="6" currentdbname="p01-ardb" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="unknown" queryhash="0x9453568569bd0807" queryplanhash="0x1d037286349af98c" line="1" stmtstart="24" stmtend="178" sqlhandle="0x0200000098b1eb2a734675ebd923e42ae1e8765c543d5e020000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 bigint)DELETE FROM TableA WHERE tableB_id IN (SELECT ID from TableB WHERE FileID = @P0)   </inputbuf>
    </process>
    <process id="process1a0f2886108" taskpriority="0" logused="117716" waitresource="PAGE: 6:1:17915872 " waittime="4747" ownerId="3967991523" transactionname="implicit_transaction" lasttranstarted="2021-06-07T21:18:08.470" XDES="0x1a015ef8428" lockMode="U" schedulerid="4" kpid="51220" status="suspended" spid="2425" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-06-07T21:18:08.853" lastbatchcompleted="2021-06-07T21:18:08.853" lastattention="1900-01-01T00:00:00.853" clientapp="Microsoft JDBC Driver for SQL Server" hostname="test_app-7c98f79f44-mz8jf" hostpid="0" loginname=test_user isolationlevel="read committed (2)" xactid="3967991523" currentdb="6" currentdbname="p01-ardb" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="unknown" queryhash="0x9453568569bd0807" queryplanhash="0x1d037286349af98c" line="1" stmtstart="24" stmtend="178" sqlhandle="0x0200000098b1eb2a734675ebd923e42ae1e8765c543d5e020000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 bigint)DELETE FROM TableA WHERE tableB_id IN (SELECT ID from TableB WHERE FileID = @P0)   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="17997419" dbid="6" subresource="FULL" objectname="f8e7ffa7-ac56-4e56-8ca2-70672c9fe337.AR.TableA" id="lock19e62889280" mode="UIX" associatedObjectId="72057594635354112">
      <owner-list>
        <owner id="process1a0f2886108" mode="UIX" />
      </owner-list>
      <waiter-list>
        <waiter id="process1a0de90e108" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="17915872" dbid="6" subresource="FULL" objectname="f8e7ffa7-ac56-4e56-8ca2-70672c9fe337.AR.TableA" id="lock19da5da9780" mode="U" associatedObjectId="72057594635354112">
      <owner-list>
        <owner id="process1a0de90e108" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process1a0f2886108" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

Index definition as follows:

Table A
index_name, index_description,                                    index_keys                                            

CIX_TableA  clustered, unique located on datePartitionScheme    , TestDate(-)
IX01_TableA nonclustered located on datePartitionScheme               FileID
PK_TableA   nonclustered, unique, primary key located on PRIMARY      ID

Table B

index_name, index_description,                                   index_keys  
                                                
IX01_TableB nonclustered located on PRIMARY                        tableB_id 
PK_TableB   clustered, unique, primary key located on PRIMARY       ID

New contributor
Junaid Akhtar is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • I also have NONCLUSTERED INDEX defined on tableB_id column of TableA which is FK as well. -- But I don't see any indexes in your update that have tableB_id listed for Table A? – Erik Darling Jun 10 at 10:23

Your Answer

Junaid Akhtar is a new contributor. Be nice, and check out our Code of Conduct.

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

Browse other questions tagged or ask your own question.