如何解决SqlException死锁 | 通信缓冲区资源问题

Question

已经有很多关于此问题的不同版本的stackoverflow问题，但是没有一个能帮助我解决我的问题。因此，我再次提供更具体的问题细节。

我们一直在随机获得“Transaction (Process ID xx) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”。请注意，这不是行或表级锁定。我已经尝试了足够的猜测/随机尝试；我需要确切的逐步指南，以解决通信缓冲区上的死锁。

如果您对具体细节感兴趣，请继续阅读。

具体情况的细节：我们有一个非常简单的基于Dapper ORM的C# .net core Web API，它接受请求并对托管在此Microsoft Sql服务器上的数据库执行CRUD操作。为此，连接管理器（注册为作用域服务）在请求范围内打开一个新的IDbConnection连接；此连接用于执行删除、插入、更新或获取操作。对于插入/更新/删除C#代码如下：await connection.ExecuteAsync("<Create update or delete statement>", entity); 对于GET请求，我们只需运行await connection.QueryFirstOrDefaultAsync<TEntity>("<select statement>", entity); ；共有5种实体（均呈现简单的非关系型表格）。它们都通过ID进行CRUD。

到目前为止尝试过的方法：

1. SQL语句上的MAXDOP=1查询提示 2. 确保在给定时间点只有一个实体进行CRUD操作。 3. 重新启动SQL服务器/应用程序实例 4. 确保端口/RAM/CPU/网络带宽没有耗尽 5. Alter DATABASE XXXXXX SET READ_COMMITTED_SNAPSHOT ON/OFF 6. 让事务尽可能小 7. 持久性重试策略作为解决随机瞬态问题的解决方法 8. 每种实体类型使用单个线程

服务器规格：我们在虚拟机中托管了Microsoft Sql Server 2016 On Azure，具有64个核心和400GB RAM。此服务器的通常工作负载为10% CPU和30% RAM，偶尔会达到80% CPU和350GB RAM。在发生此问题的所有时刻，CPU使用率都低于20％（大多数情况下约为10％，只有一次为20％），RAM在所有情况下都低于30％。

根据@Dan Guzman的要求，死锁XML事件如下：文件大小对于此帖子来说太大了，因此创建了此Google Drive文件。请单击以下链接，然后在右上角单击下载。这是一个zip文件。

https://drive.google.com/file/d/1oZ4dT8Yrd2uW2oBqBy9XK_laq7ftGzFJ/view?usp=sharing

- sbp

---

请在您的问题中添加死锁XML。参考此答案以获取从system_health xe跟踪中提取事件的示例。 - Dan Guzman

@DanGuzman，由于文件大小限制，我不得不将文件放在Google Drive上。我在上面的帖子中添加了文件链接，请点击右上方的下载按钮。谢谢。 - sbp

我看到所有的参数都声明为nvarchar(4000)。当引用的列类型不同时，这可能会阻止索引的有效使用，导致在UPDATE查询期间进行全表扫描，从而引发死锁。尝试使用与引用列相同类型的强类型参数，并检查执行计划以提高效率。 - Dan Guzman

@DanGuzman，非常感谢。太棒了。看起来问题已经解决了。即使处理了200万个请求，也没有出现任何异常。Dapper的默认参数大小4000对我的情况不适用。 - sbp

Answer 1

@DanGuzman提供了帮助，所以我必须点赞/选择他的答案作为采纳的答案。但是，我想总结一下发生了什么，我学到了什么以及如何逐步解决通信缓冲区死锁（或任何死锁）。

步骤 - 1
获取死锁报告。我使用了以下查询，但您也可以使用@DanGuzman建议的查询（在此问题的评论部分）。

SELECT
   xed.value('@timestamp', 'datetime2(3)') as CreationDate,
   xed.query('.') AS XEvent
FROM
(
   SELECT CAST([target_data] AS XML) AS TargetData
   FROM sys.dm_xe_session_targets AS st
      INNER JOIN sys.dm_xe_sessions AS s
         ON s.address = st.event_session_address
      WHERE s.name = N'system_health'
         AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

步骤 - 2
找到与您的SQL异常时间/数据相对应的死锁事件。然后结合检测和结束死锁指南阅读此报告，以了解死锁问题的根本原因。在我的情况下，我在通信缓冲区上遇到了死锁，因此根据这个指南，内存（检测和结束死锁指南中的Memory部分）必须是引起问题的原因。正如丹所指出的那样，在我的情况下，死锁报告中出现了以下查询，该查询使用了太多的缓冲区（由于查询效率低下导致）。那么什么是通信缓冲区上的死锁呢？如果此查询需要太多的缓冲区来完成执行，则两个这样的查询可能同时开始执行并开始声明它们需要的缓冲区，但在某些时候可用缓冲区可能不足，它们将不得不等待其他查询执行完成后释放更多缓冲区。因此，两个查询都会相互等待完成，希望能够获得一些更多的缓冲区，并可能导致缓冲区上的死锁（根据指南中的内存部分）。

<inputbuf>
@SomeStatus1 nvarchar(4000),@ProductName nvarchar(4000),@ProductNameSide nvarchar(4000),@BayNo nvarchar(4000),@CreatedDateTime datetime,@EffectiveDate datetime,@ForSaleFrom datetime,@ForSaleTo datetime,@SetupInfoNode nvarchar(4000),@LocationNumber nvarchar(4000),@AverageProductPrice decimal(3,2),@NetAverageCost decimal(3,1),@FocustProductType nvarchar(4000),@IsProduceCode nvarchar(4000),@ActivationIndicator nvarchar(4000),@ResourceType nvarchar(4000),@ProductIdentifierNumber nvarchar(4000),@SellingStatus nvarchar(4000),@SectionId nvarchar(4000),@SectionName nvarchar(4000),@SellPriceGroup nvarchar(4000),@ShelfCapacity decimal(1,0),@SellingPriceTaxExclu decimal(2,0),@SellingPriceTaxInclu decimal(2,0),@UnitToSell nvarchar(4000),@VendorNumber nvarchar(4000),@PastDate datetime,@PastPrice decimal(29,0))
UPDATE dbo.ProductPricingTable 
SET SellingPriceTaxExclu = @SellingPriceTaxExclu, SellingPriceTaxInclu = @SellingPriceTaxInclu, 
SellPriceGroup = @SellPriceGroup, 
ActivationIndicator = @ActivationIndicator, 
IsProduceCode = @IsProduceCode, 
EffectiveDate = @EffectiveDate, 
NetCos
</inputbuf>

步骤三（修复）
等等！！！但是我用的是Dapper。那么它怎么会将我的查询转换为这样致命的查询呢？好吧，对于大多数情况，Dapper都具有出色的默认设置，但显然，在我的情况下，默认的4000个nvarchar使其崩溃了（请阅读丹尼尔的答案以了解这样的查询如何导致问题）。正如丹尼尔建议的那样，我使用以下自定义参数自动从输入实体构建参数：await connection.ExecuteAsync("<创建更新或删除语句>", entity);，其中entity是C#模型类的实例。我将其更改为自定义参数，如下所示。（仅为简单起见，我只添加了一个参数，但您可以使用所有所需的参数）

            var parameters = new DynamicParameters();
            parameters.Add("Reference", entity.Reference, DbType.AnsiString, size: 18 );
await connection.ExecuteAsync("<Create update or delete statement>", parameters );

我可以在分析器中看到请求现在具有精确匹配的列参数类型。这就是，这个修复方案解决了问题。感谢丹。

结论
我得出的结论是，在我的情况下，通信缓冲区上的死锁是由于执行了太多缓冲区的不良查询所致。这是因为我盲目地使用了默认的Dapper参数生成器。使用Dapper的自定义参数生成器解决了这个问题。

- sbp

Answer 2

死锁通常是需要查询和索引调整的症状。以下是死锁跟踪中的示例查询，建议确定死锁的根本原因:

<inputbuf>
@SomeStatus1 nvarchar(4000),@ProductName nvarchar(4000),@ProductNameSide nvarchar(4000),@BayNo nvarchar(4000),@CreatedDateTime datetime,@EffectiveDate datetime,@ForSaleFrom datetime,@ForSaleTo datetime,@SetupInfoNode nvarchar(4000),@LocationNumber nvarchar(4000),@AverageProductPrice decimal(3,2),@NetAverageCost decimal(3,1),@FocustProductType nvarchar(4000),@IsProduceCode nvarchar(4000),@ActivationIndicator nvarchar(4000),@ResourceType nvarchar(4000),@ProductIdentifierNumber nvarchar(4000),@SellingStatus nvarchar(4000),@SectionId nvarchar(4000),@SectionName nvarchar(4000),@SellPriceGroup nvarchar(4000),@ShelfCapacity decimal(1,0),@SellingPriceTaxExclu decimal(2,0),@SellingPriceTaxInclu decimal(2,0),@UnitToSell nvarchar(4000),@VendorNumber nvarchar(4000),@PastDate datetime,@PastPrice decimal(29,0))
UPDATE dbo.ProductPricingTable 
SET SellingPriceTaxExclu = @SellingPriceTaxExclu, SellingPriceTaxInclu = @SellingPriceTaxInclu, 
SellPriceGroup = @SellPriceGroup, 
ActivationIndicator = @ActivationIndicator, 
IsProduceCode = @IsProduceCode, 
EffectiveDate = @EffectiveDate, 
NetCos
</inputbuf>

虽然SQL语句文本被截断了，但它确实显示了所有参数声明为nvarchar(4000)（ORM常见问题）。这可能会导致在联接/where子句中引用的列类型不同时无法有效使用索引，从而导致全表扫描，在并发查询期间导致死锁。

更改参数类型以匹配引用列，并检查执行计划以提高效率。

- Dan Guzman