Блог

В SQL Server я выдаю следующий запрос:

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

Запрос вызывается параллельно несколькими процессами и вызывает проблему взаимоблокировки на уровне страницы.

У меня также есть НЕКЛАСТЕРИЗОВАННЫЙ ИНДЕКС, определенный в столбце tableB_id таблицы, который также является FK.

Я также делюсь XML-кодом взаимоблокировки ниже. Кто-нибудь может мне помочь, как я могу предотвратить этот тупик?

 <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>
 

Определение индекса следующим образом:

 Table B
index_name, index_description,                                    index_keys                                            

CIX_TableB  clustered, unique located on datePartitionScheme    , TestDate(-)
IX01_TableB nonclustered located on datePartitionScheme               FileID
PK_TableB   nonclustered, unique, primary key located on PRIMARY      ID

Table A

index_name, index_description,                                   index_keys  
                                                
IX01_TableA nonclustered located on PRIMARY                        tableB_id 
PK_TableA   clustered, unique, primary key located on PRIMARY       ID

 

Определение таблицы

 CREATE TABLE TableA(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [tableB_id ] [bigint] NOT NULL,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE TableB  WITH CHECK ADD  CONSTRAINT [FK_01] FOREIGN KEY([tableB_id])
REFERENCES [AR].TableB ([ID])

CREATE TABLE [TableB](
    [FileID] [int] NOT NULL,
    [BookgDt] [date] NOT NULL,
    [ID] [bigint] NOT NULL,
    [Description] [varchar](4000) NULL,

 CONSTRAINT [PK_TableB] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
GO

ALTER TABLE [TableB] ADD  CONSTRAINT [DF_01]  DEFAULT (NEXT VALUE FOR [Sequence_01]) FOR [ID]
GO