Início / dba / Perguntas / 220085
Accepted
jesijesi
Asked: 2018-10-15 21:05:07 +0800 CST

Solução de problemas de impasse, vítima segurando o bloqueio compartilhado

  • 772

Estou tentando solucionar a situação de impasse abaixo insira a descrição da imagem aqui

Observando o xml de deadlock, ambas as sessões estão usando o nível de isolamento de leitura confirmada. A pergunta é, no nível de isolamento comprometido de leitura, nessa situação, por que a vítima está segurando o bloqueio compartilhado? Meu entendimento e acabei de testar é que, ao ler tabelas, ele adquiriria bloqueio compartilhado no nível de página/linha e o liberaria imediatamente. Em que situação no nível de isolamento de leitura confirmada, uma sessão deseja manter o bloqueio compartilhado na página que adquiriu?

Todos os bloqueios estão em um índice não clusterizado em uma tabela.

<deadlock>
    <victim-list>
        <victimProcess id="process11634c19088" />
    </victim-list>
    <process-list>
        <process id="process11634c19088" taskpriority="0" logused="0" waitresource="PAGE: 11:13:52372905 " ownerId="57900000774" transactionname="SELECT" lasttranstarted="2018-10-12T09:48:05.360" XDES="0x3e7feedc00" lockMode="S" schedulerid="12" kpid="8544" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-10-12T09:48:05.360" lastbatchcompleted="2018-10-12T09:48:05.377" lastattention="1900-01-01T00:00:00.377" clientapp=".Net SqlClient Data Provider" hostname="hostname1" hostpid="6912" loginname="ENETTAD\SVC_APP" isolationlevel="read committed (2)" xactid="57900000774" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="database1">
            <executionStack>
                <frame procname="database1.dbo.uspBatch_Search" line="88" stmtstart="5068" stmtend="7128" sqlhandle="0x03000b00fad68c201937da0036a9000001000000000000000000000000000000000000000000000000000000">
SELECT @TotalRowCount = COUNT(*)    
  FROM dbo.Batch     
  JOIN #child ce    
   ON ce.ECN = requestednce    
  WHERE BatchId = ISNULL(@BatchId, BatchId)    
   AND RequestedUsername = ISNULL(@RequestedUsername, RequestedUsername)    
   AND requestednce = ISNULL(@requestednce, requestednce)    
   AND BatchStatusId = ISNULL(@StatusId, BatchStatusId)    
   AND BatchStatusId != @NewStatusId   and  BatchStatusId != @CancelledStatusId    
   AND (  (@IsReportRequired = @true AND ReportBlobId IS NOT NULL)     
   OR (@IsReportRequired = @false))    
   AND ( (     
   (@DateType = @CreatedDate AND CreatedDate BETWEEN cast(@FromDate AS varchar(20)) AND cast(@ToDate AS varchar(20)) )    
    OR     
   (@IncludeCurrent = @true AND ( BatchStatusId IN (@QueuedStatus,@InProgressStatus,@ReportingStatus)     
      OR     
       CompletedDate BETWEEN cast(@FromDate AS varchar(20)) AND cast(@ToDate AS varchar(20)))  )    
  )     
  OR @DateType = @IgnoreDateRange    </frame>
                <frame procname="adhoc" line="1" stmtstart="386" sqlhandle="0x01000b005101d12e207be87f2100000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[uspBatch_Search] @ECN = @p0, @BatchId = @p1, @requestednce = @p2, @RequestedUsername = @p3, @DateType = @p4, @FromDate = @p5, @ToDate = @p6, @StatusId = @p7, @IsReportRequired = @p8, @IncludeCurrent = @p9, @StartRowIndex = @p10, @SortExpression = @p11, @MaximumRows = @p12, @TotalRowCount = @p13 OUTPUT    </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@p2 int,@p3 varchar(8000),@p4 varchar(8000),@p5 datetime,@p6 datetime,@p7 int,@p8 tinyint,@p9 bit,@p10 int,@p11 varchar(8000),@p12 int,@p13 int output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[uspBatch_Search] @ECN = @p0, @BatchId = @p1, @requestednce = @p2, @RequestedUsername = @p3, @DateType = @p4, @FromDate = @p5, @ToDate = @p6, @StatusId = @p7, @IsReportRequired = @p8, @IncludeCurrent = @p9, @StartRowIndex = @p10, @SortExpression = @p11, @MaximumRows = @p12, @TotalRowCount = @p13 OUTPUT   </inputbuf>
        </process>
        <process id="process2e950984e8" taskpriority="0" logused="10880" waitresource="PAGE: 11:13:14044552 " waittime="241" ownerId="57900000848" transactionname="user_transaction" lasttranstarted="2018-10-12T09:48:05.367" XDES="0xf7c3c0c3b0" lockMode="IX" schedulerid="2" kpid="5476" status="suspended" spid="234" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-10-12T09:48:05.370" lastbatchcompleted="2018-10-12T09:48:05.367" lastattention="1900-01-01T00:00:00.367" clientapp=".Net SqlClient Data Provider" hostname="hostname1" hostpid="6912" loginname="ENETTAD\SVC_APP" isolationlevel="read committed (2)" xactid="57900000848" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="database1">
            <executionStack>
                <frame procname="database1.dbo.uspBatch_Update" line="32" stmtstart="1934" stmtend="2586" sqlhandle="0x03000b00b243467b8df206000ba7000001000000000000000000000000000000000000000000000000000000">
UPDATE Batch      
            SET  BatchStatusId = @BatchStatusId,      
            ReportBlobId = @ReportBlobId,      
            CompletedDate = @CompletedDate,    
            ImportReportVersion = @ImportReportVersion,    
            ExportReportVersion = @ExportReportVersion,  
            ErrorCode = @ErrorCode  
            WHERE BatchId  = @BatchI    </frame>
                <frame procname="adhoc" line="1" stmtstart="236" sqlhandle="0x01000b009c24d728c05a7d06ab00000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[uspBatch_Update] @RequestId = @p0, @BatchId = @p1, @BatchStatusId = @p2, @ReportBlobId = @p3, @CompletedDate = @p4, @ErrorCode = @p5, @ImportReportVersion = @p6, @ExportReportVersion = @p7    </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 uniqueidentifier,@p1 int,@p2 int,@p3 varchar(8000),@p4 datetime,@p5 int,@p6 int,@p7 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[uspBatch_Update] @RequestId = @p0, @BatchId = @p1, @BatchStatusId = @p2, @ReportBlobId = @p3, @CompletedDate = @p4, @ErrorCode = @p5, @ImportReportVersion = @p6, @ExportReportVersion = @p7   </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <pagelock fileid="13" pageid="52372905" dbid="11" subresource="FULL" objectname="database1.dbo.Batch" id="lock9f21680580" mode="IX" associatedObjectId="72060857777979392" databaseName="database1">
            <owner-list>
                <owner id="process2e950984e8" mode="IX" />
            </owner-list>
            <waiter-list>
                <waiter id="process11634c19088" mode="S" requestType="wait" />
            </waiter-list>
        </pagelock>
        <pagelock fileid="13" pageid="14044552" dbid="11" subresource="FULL" objectname="database1.dbo.Batch" id="lock1506883db00" mode="S" associatedObjectId="72060857777979392" databaseName="database1">
            <owner-list>
                <owner id="process11634c19088" mode="S" />
            </owner-list>
            <waiter-list>
                <waiter id="process2e950984e8" mode="IX" requestType="wait" />
            </waiter-list>
        </pagelock>
    </resource-list>
</deadlock>
sql-server sql-server-2014

1 respostas

  1. Best Answer

    Sobre sua declaração aqui:

    Meu entendimento e acabei de testar é que, ao ler tabelas, ele adquiriria bloqueio compartilhado no nível de página/linha e o liberaria imediatamente

    Veja como a documentação descreve brevemente READ COMMITTED:

    Especifica que as instruções não podem ler dados que foram modificados, mas não confirmados por outras transações. Isso evita leituras sujas. Os dados podem ser alterados por outras transações entre instruções individuais dentro da transação atual, resultando em leituras não repetíveis ou dados fantasmas. Essa opção é o padrão do SQL Server.

    Portanto, seu entendimento está próximo do correto. Ele não libera os bloqueios imediatamente - ele libera os bloqueios o mais rápido possível. Nesse caso, o mais rápido possível é o momento em que o bloqueio na próxima linha ou página é adquirido. Veja a postagem do blog de Paul White The Read Committed Isolation Level :

    Quando o bloqueio de leitura confirmada adquire bloqueios na granularidade de linha, o bloqueio compartilhado obtido em uma linha é liberado quando um bloqueio compartilhado é obtido na próxima linha. Na granularidade da página, o bloqueio de página compartilhada é liberado quando a primeira linha da próxima página é lida e assim por diante

    Como os bloqueios no gráfico de deadlock são bloqueios de página, aposto que o deadlock está acontecendo porque você tem muitas linhas pequenas em cada página de 8K.

    Então, essencialmente, o problema aqui é que você tem uma sessão que está selecionando linhas da tabela dbo.Batch ( dbo.uspBatch_Search), e outra sessão que está atualizando linhas ( dbo.uspBatch_Update). Então é isso que acontece:

    1. spid 72 começa a pesquisar através do dbo.Batch, adquirindo (e liberando) bloqueios compartilhados (S) em uma página por vez
    2. spid 234 inicia a atualização de linhas em dbo.Batch em BatchId. Meu palpite é que isso está atualizando uma linha de cada vez, dentro de uma transação longa, mantendo bloqueios até o final (é usado 10.880 bytes até agora quando ocorreu o bloqueio)
    3. o spid 72 é executado em uma página que foi bloqueada pelo processo de atualização no spid 234. Portanto, ele deve parar onde está e esperar, pois não pode ler os dados que foram modificados por outras sessões (e ainda não foram confirmados )
    4. O spid 234 continua avançando, até atingir a última linha que foi S bloqueada pelo processo de pesquisa no spid 72 (que ainda está segurando, porque não pode adquirir o próximo bloqueio S de que precisa). O bloqueio IX que spid 234 é incompatível com o bloqueio S, por isso tem que esperar.

    Assim o impasse.

    Se você postar planos de execução e texto de consulta para esses dois procedimentos, provavelmente podemos ajudá-lo com opções para evitar o impasse (com índices ou reescritas de consulta).

    • 1

relate perguntas