Início / coding / Perguntas / 78843729
Accepted
Merlin Nestler
Asked: 2024-08-07 20:34:21 +0800 CST

Evitando deadlocks – Como bloquear no nível da linha

  • 772

Estou enfrentando um impasse que não consigo replicar experimentalmente. Acredito que, com base em meus experimentos, descobri como resolvê-lo, mas gostaria de aprender o princípio subjacente

Aqui está o gráfico do bloqueio fornecido pelo SQL Server. A partir do ID do objeto você pode inferir que ambos os processos estão tentando acessar a mesma tabela. insira a descrição da imagem aqui

A tabela fica assim. (Usei uma tabela temporária global para teste)

CREATE TABLE ##tMyTable(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Fk_1_Id] [int] NOT NULL,
    [Fk_2_Id] [int] NULL,
    [Fk_3_Id] [int] NULL,
    [Day] [date] NOT NULL,
    [Quantity] [decimal](19, 3) NOT NULL,
 CONSTRAINT [Pk_MyTable_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

+ foreign key stuff.

As colunas com o prefixo Fk_ são chaves estrangeiras.

Há também um índice na tabela:

CREATE NONCLUSTERED INDEX [Idx_IndexName] ON ##tMyTable
(
    [Fk_1_Id] ASC,
    [Fk_2_Id] ASC--,
    [Day] ASC,
    [Id] ASC
)
INCLUDE([Fk_3_Id],[Quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

O processo abortado foi uma instrução de exclusão:

delete from ##tMyTable where Fk_2_Id = 86416

O processo que foi permitido viver foi uma inserção. (as datas estão no formato alemão).

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Day, Quantity) select Fk_1_Id, 86415, Day, Quantity from tOtherTableWithSimilarColumns where Fk_4_Id = 22 and Day >= '01.07.2024' and Day < '01.08.2024'

Para teste configurei duas sessões com o código a seguir.

Begin transaction

Select @@SPID as FirstTransactionProcessID

Query I want to test

commit

Se eu executar o código sem incluir commitas transações estão bloqueando seus recursos e posso simular a situação de deadlock.

Com exec sp_lockeu posso verificar os bloqueios depois.

Antes de começar, inseri o seguinte na tabela:

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Fk_3_Id, Tag, Menge)
Select 1, 1, NULL, '2024-01-01', 22.404

Na sessão 1 eu executei isto:

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Fk_3_Id, Tag, Menge)
Select 2, 2, NULL, '2024-01-01', 22.404

E então na sessão 2 eu executei isto:

delete from ##tMyTable where Fk_2_Id = 1

O resultado foi este: insira a descrição da imagem aqui

spid 77 é a inserção e spid 69 é a exclusão.

Como você pode ver, a exclusão tenta obter um bloqueio U em uma chave, mas não consegue porque a inserção X bloqueia a chave. No entanto, eu esperava ver a inserção bloqueando uma página com um bloqueio IX enquanto a exclusão deseja a mesma página para um bloqueio U.

Pergunta: E minha configuração de teste está errada? Por que estou obtendo bloqueios diferentes em comparação ao meu impasse original? E por que a exclusão simplesmente não coloca um bloqueio IX na chave e opera no nível da linha? Dois bloqueios IX são compatíveis e as consultas não devem incomodar uma à outra. Afinal, eles estão visando IDs diferentes e, portanto, linhas diferentes.

O que parece resolver meu problema : se eu criar um índice que inclua apenas Fk_2_Id, não haverá conflitos de bloqueio em meu testsetup. No entanto, gostaria de evitar "consertar" algo sem entender por que minha solução funciona.

XML de impasse:

<deadlock>
 <victim-list>
  <victimProcess id="process1f4f20e5468" />
 </victim-list>
 <process-list>
  <process id="process1f4f20e5468" taskpriority="0" logused="0" waitresource="PAGE: 8:1:11652007 " waittime="2865" ownerId="213325556" transactionname="DELETE" lasttranstarted="2024-08-04T10:02:44.943" XDES="0x1f4e33c3aa0" lockMode="U" schedulerid="2" kpid="9392" status="suspended" spid="56" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2024-08-04T10:02:44.937" lastbatchcompleted="2024-08-04T10:02:44.933" lastattention="1900-01-01T00:00:00.933" clientapp=".Net SqlClient Data Provider" hostname="Our-Host" hostpid="11672" isolationlevel="read committed (2)" xactid="213325556" currentdb="8" currentdbname="Our_DB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtend="152" sqlhandle="0x02000000cff49033589c5bbc920f9e3e6c20dd52ec7b2a6b0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="1" stmtend="152" sqlhandle="0x02000000a28e5e12abc21669e1bfc6fb7c33f25bfee40b1c0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
delete from tMyTable where Fk_2_Id = 86416   </inputbuf>
  </process>
  <process id="process1f4f5011c28" taskpriority="0" logused="9944" waitresource="PAGE: 8:1:11631539 " waittime="4125" ownerId="213327344" transactionname="INSERT" lasttranstarted="2024-08-04T10:02:46.687" XDES="0x1f4e45e4460" lockMode="IX" schedulerid="4" kpid="11516" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-08-04T10:02:46.657" lastbatchcompleted="2024-08-04T10:02:46.653" lastattention="1900-01-01T00:00:00.653" clientapp=".Net SqlClient Data Provider" hostname="Our-Host" hostpid="11672" loginname="loginname" isolationlevel="read committed (2)" xactid="213327344" currentdb="8" currentdbname="Our_DB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtend="568" sqlhandle="0x02000000679aaf081f4d1ffe0710dbf9b238a8db3e6adb6b0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
       insert into tMyTable (Fk_1_Id, Fk_2_Id, Day, Quantity) select Fk_1t_Id, 86415, Day, Quantity from tOtherTableWithSimilarColumns where Fk_4_Id = 22 and Day &gt;= '01.07.2024' and Day &lt; '01.08.2024'   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="11652007" dbid="8" subresource="FULL" objectname="Our_DB.dbo.tMyTable" id="lock1f7bf84a200" mode="IX" associatedObjectId="72057661408477184">
   <owner-list>
    <owner id="process1f4f5011c28" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f4f20e5468" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="11631539" dbid="8" subresource="FULL" objectname="Our_DB.dbo.tMyTable" id="lock1f4e3647700" mode="U" associatedObjectId="72057661408477184">
   <owner-list>
    <owner id="process1f4f20e5468" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f4f5011c28" mode="IX" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>
sql-server

1 respostas

  1. Best Answer

    Cada chave estrangeira deve ser suportada por um índice, e está faltando um índice em FK_2_id.

    O DELETE não pode fazer uso do Idx_IndexNameíndice, pois a chave inicial é Fk_1_Ida que não está sendo buscada no DELETE. Então, em vez disso, ele verifica toda a tabela, o que causa enormes problemas de impasse. É um clássico Bookmark Deadlock.

    Então você precisa de um índice adicional.

    • 2

relate perguntas