SQLEXCEPTION交易已被死锁的通信缓冲区资源 [英] SqlException Transaction was deadlocked on communication buffer resources

问题描述

我有一个服务器进程具有执行大量的数据库查询，它使用TPL并行运行的东西。它一直在努力罚款，今年所有的，直到今天，当它与以下异常30分钟的跨度坠毁过两次。

I have a server process that has to execute a lot of database queries, it uses TPL to run stuff in parallel. It has been working fine for all of this year, until today when it crashed twice in a 30 minute span with the following exception.

该数据库配置为记录任何死锁，但它并没有记录任何东西，所以它好像这个僵局只发生在客户端？

The database is configured to log any deadlocks, but it didn't log anything, so it seems as if this deadlock happened only on the client side?

我无法找到此异常以外一个<一的任何引用href="http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9154f1cf-fd0b-4980-aefe-521bafccd0df/">msdn论坛上的帖子，不提供任何信息。

I couldn't find any references to this exception other than one msdn forum post that doesn't provide any information.

有没有人见过这个异常？或者，知道自己能做什么，了解它的更多信息？

Has anyone seen this exception before? Or know what I could do to find out more information about it?

---> System.AggregateException: One or more errors occurred. 

---> System.Data.SqlClient.SqlException: Transaction (Process ID 89) was deadlocked on communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at App.CoreEngine.V5.DataAccess.SqlReader.Read(String readerDescription) in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\SqlReader.cs:line 121
   at App.CoreEngine.V5.DataAccess.DataContext.ExecuteQuery(PtQuery query, ValueStore`1 store, String readerDescription) in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 328
   at App.CoreEngine.V5.DataAccess.DataContext.<>c__DisplayClass12.<GetCalculatedDataForCompare>b__f(Object _) in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 267
   at System.Threading.Tasks.Task.Execute()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at App.CoreEngine.V5.DataAccess.DataContext.GetCalculatedDataForCompare() in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 276
   at System.Threading.Tasks.Task`1.InvokeFuture(Object futureAsObj)
   at System.Threading.Tasks.Task.Execute()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task`1.get_Result()
   at App.CoreEngine.V5.DataAccess.DataContext.get_CalcCompareData() in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 389
   at App.CoreEngine.V5.Calculation.CalculationEngine.Run() in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\Calculation\CalculationEngine.cs:line 243
   at App.CoreEngine.V5.Processor.Milestone.BatchRunner.Run() in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\Processor\Milestone\BatchRunner.cs:line 171

---> (Inner Exception #0) System.AggregateException: One or more errors occurred. 
---> System.Data.SqlClient.SqlException: Transaction (Process ID 89) was deadlocked on communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at App.CoreEngine.V5.DataAccess.SqlReader.Read(String readerDescription) in C:\SourceCode\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\SqlReader.cs:line 121
   at App.CoreEngine.V5.DataAccess.DataContext.ExecuteQuery(PtQuery query, ValueStore`1 store, String readerDescription) in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 328
   at App.CoreEngine.V5.DataAccess.DataContext.<>c__DisplayClass12.<GetCalculatedDataForCompare>b__f(Object _) in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 267
   at System.Threading.Tasks.Task.Execute()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at CoreEngine.V5.DataAccess.DataContext.GetCalculatedDataForCompare() in C:\SourceCode\AppV1\Releases\Libraries\CoreEngine\CoreEngine.V5\DataAccess\DataContext.cs:line 276
   at System.Threading.Tasks.Task`1.InvokeFuture(Object futureAsObj)
   at System.Threading.Tasks.Task.Execute()

修改 - 我只是双重检查的DBCC 1222接通这发生在服务器上，我跑 DBCC TRACESTATUS 并获得：

EDIT - I just double checked that dbcc 1222 is turned on for the server this happened on, I ran dbcc tracestatus and got:

TraceFlag   Status  Global  Session
1222    1   1   0
3605    1   1   0

并没有什么报告死锁在日志中

And there is nothing in the logs reporting the deadlocks

推荐答案

我的猜测是，执行计划现在使用的并行性，而在此之前它并不能满足成本阈值。

My guess is that the execution plan now uses parallelism, whereas before it didn't meet the cost threshold.

尝试MAXDOP 1查询

Try MAXDOP 1 in the queries

编辑，注释后

您还需要跟踪标志1204

You also need trace flag 1204.

TF 1222给人的死锁图形，但这个通信缓冲区资源的僵局可能没有涉及2个对象（我猜这不是一个索引/表冲突）。请参阅<一href="http://msdn.microsoft.com/en-us/library/ms178104.aspx">http://msdn.microsoft.com/en-us/library/ms178104.aspx

TF 1222 gives the deadlock graph but with this "communication buffer resource" deadlock there may not be 2 objects involved (I'm guessing here it isn't an index/table conflict). See http://msdn.microsoft.com/en-us/library/ms178104.aspx

还有一个未公开的TF 1205提供了更多的信息到错误日志

There is also the undocumented TF 1205 which gives more info into the error log

这篇关于SQLEXCEPTION交易已被死锁的通信缓冲区资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！