主页 / dba / 问题 / 220085
Accepted
jesijesi
Asked: 2018-10-15 21:05:07 +0800 CST

死锁故障排除，受害者持有共享锁

  • 772

我正在尝试解决以下死锁情况 在此处输入图像描述

查看死锁 xml，两个会话都使用读取提交隔离级别。问题是，在读提交隔离级别，在这种情况下，为什么受害者会持有共享锁？我的理解和我刚刚测试过的是，在读取表时，它会在页/行级别获取共享锁，然后立即释放它。在读提交隔离级别的什么情况下，会话会想要持有它已获取的页面上的共享锁？

所有锁都位于表的非聚集索引上。

<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 个回答

  1. Best Answer

    关于你在这里的陈述：

    我的理解和我刚刚测试过的是，在读取表时，它会在页/行级别获取共享锁，然后立即释放它

    以下是文档如何简要描述 READ COMMITTED：

    指定语句不能读取已修改但未被其他事务提交的数据。这样可以防止脏读。当前事务中各个语句之间的其他事务可能会更改数据，从而导致不可重复读取或幻像数据。此选项是 SQL Server 的默认选项。

    所以你的理解接近正确。它不会立即释放锁——它会尽快释放锁。 在这种情况下，尽快获取下一行或下一页的锁。请参阅 Paul White 的博客文章The Read Committed Isolation Level：

    当locking read committed以行粒度获取锁时，当对下一行获取共享锁时释放对行获取的共享锁。在页粒度上，共享页锁在读取下一页的第一行时释放，以此类推

    由于死锁图中的锁是页面锁，我敢打赌死锁的发生是因为每个 8K 页面上都有很多小行。

    所以本质上，这里的问题是您有一个会话从 dbo.Batch 表中选择行 ( dbo.uspBatch_Search)，而另一个会话正在更新行 ( dbo.uspBatch_Update)。所以这就是发生的事情：

    1. spid 72 开始搜索 dbo.Batch，一次获取（和释放）页面上的共享 (S) 锁
    2. spid 234 开始根据 BatchId 更新 dbo.Batch 中的行。我的猜测是这是一次更新一行，在一个长事务中，持有锁直到结束（当死锁发生时它已经使用了 10,880 字节）
    3. spid 72 运行到一个页面，该页面已被 spid 234 中的更新进程锁定。因此它必须停在原处等待，因为它无法读取已被其他会话修改（且尚未提交）的数据)
    4. spid 234 一直向前移动，直到它到达被 spid 72 上的搜索进程 S 锁定的最后一行（它仍然持有，因为它无法获得它需要的下一个 S 锁）。spid 234与S锁不兼容的IX锁，只能等待。

    从而陷入僵局。

    如果您发布这两个过程的执行计划和查询文本，我们可能可以帮助您选择避免死锁的选项（使用索引或查询重写）。

    • 1

相关问题