Início / dba / Perguntas / 282786
Accepted
Kin Shah
Asked: 2021-01-08 14:45:30 +0800 CST

deadlock com nível de isolamento de leitura repetível

  • 772

Tentando entender o deadlock que está ocorrendo com o nível de isolamento de leitura repetível quando o proc armazenado é invocado.

Este é o xml de deadlock:

    <TextData>
  <deadlock-list>
 <deadlock victim="process6fac029848">
  <process-list>
   <process id="process6fac029848" taskpriority="0" logused="0" waitresource="KEY: 8:72078600230076416 (d3c590b351cd)" waittime="1171" 
            ownerId="26116633497" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.317" XDES="0x12553cbe3b0" 
            lockMode="U" schedulerid="3" kpid="19776" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" 
            lastbatchstarted="2021-01-07T10:35:38.310" lastbatchcompleted="2021-01-07T10:35:38.310" lastattention="1900-01-01T00:00:00.310" 
            clientapp=".Net SqlClient Data Provider" hostname="web01" hostpid="34852" loginname="someuser" 
            isolationlevel="repeatable read (3)" xactid="26116633497" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" 
            clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" 
            sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
   <process id="process6fab017088" taskpriority="0" logused="284" waitresource="KEY: 8:72064801531101184 (4b819525e255)" waittime="1283" 
   ownerId="26116632474" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.290" XDES="0x44037ec3b0" 
   lockMode="X" schedulerid="25" kpid="25152" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
   lastbatchstarted="2021-01-07T10:35:38.287" lastbatchcompleted="2021-01-07T10:35:38.283" lastattention="1900-01-01T00:00:00.283" 
   clientapp=".Net SqlClient Data Provider" hostname="web02" hostpid="57552" loginname="someuser" 
   isolationlevel="repeatable read (3)" xactid="26116632474" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72078600230076416" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_Id_Status_userid_UpdateTime" id="lock287c93a100" mode="U" associatedObjectId="72078600230076416">
    <owner-list>
     <owner id="process6fab017088" mode="U" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fac029848" mode="U" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72064801531101184" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_SomeId_Id" id="lock55fc471880" mode="S" associatedObjectId="72064801531101184">
    <owner-list>
     <owner id="process6fac029848" mode="S" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fab017088" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
</TextData>

a tabela de criação com índices relevantes:

CREATE TABLE [schema].[TableName](
    [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SomeId] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
    [RecordStatus] [tinyint] NOT NULL,
 CONSTRAINT [PK_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]
GO
--- 2 indexes that are part of deadlock 
IX_TableName_SomeId_Id = filtered Index - SomeId, Id (include status) where status = 0
IX_TableName_Id_Status_userid_UpdateTime - non clustered index - Id include(status, userid, updatetime)

A chamada SP é:

  • leitura repetível de nível de isolamento
  • inicia a transação
  • faz uma seleção da tabela base e insere as linhas que serão afetadas em uma variável temporária.
  • executa outro SP
  • atualiza a tabela base. <<--- é aqui que acontece o impasse
  • mescla os dados com alguma lógica de negócios
  • se não houver erro, confirma a transação

A tabela base possui um gatilho que insere e atualiza a tabela de auditoria para rastrear alterações.

Estou apenas tentando entender por que estou recebendo muitos impasses.

Atualizar :

declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid

Controle de fluxo de procedimento armazenado:

variables 
    @id 
    @someid 
    
begin 
    set nocount on
    set transaction isolation level repeatable read
    set xact_abort on

    begin tran delete
    begin try

    declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

    some SP runs

    -- update statememt that causes deadlock 
    -- it basically sets status = 1 -- deleted (soft delete)

    -- merge data into tables using CTE and MERGE 
    END TRY

    begin catch 
        -- error handling 
        -- roll back tran 
    end catch

    if @@trancount > 0
    begin
        commit tran delete
    END

END
sql-server sql-server-2014

1 respostas

  1. Best Answer

    Segue minha hipótese:

    Vamos chamar as chamadas de deadlock do proc P1 e P2 .

    Tanto P1 quanto P2 usam o índice filtrado para localizar linhas. Como isso é Leitura Repetitiva sem outras dicas, eles usam bloqueios S e não os liberam. Eles incluem uma linha comum. Chamaremos sua localização no índice filtrado Row A .

    Neste ponto, P1 e P2 têm bloqueios S na Linha A.

    Agora, as atualizações começam.

    P1 lê o outro índice para localizar as linhas que precisam ser atualizadas. Ele usa bloqueios U enquanto lê e coloca um bloqueio U em Row B , que na verdade representa a mesma linha que A, apenas em um índice diferente.

    Agora P1 tem um bloqueio S na Linha A e um bloqueio U na Linha B.

    P1 vê que tem uma linha para modificar e inicia o processo. Ele também deve excluir a linha do índice filtrado, o que exigirá um bloqueio X.

    P1 tenta converter seu bloqueio S em um bloqueio X, mas é bloqueado pelo bloqueio S de P2 .

    P2 inicia sua atualização. Ele tenta adquirir um bloqueio U na Linha B , mas é bloqueado pelo bloqueio U de P1 . Impasse .

    Acredito que usar a dica UPDLOCK na seleção inicial deve permitir que você evite o impasse.

    • 5

relate perguntas