c# - System.Data.Entity.Infrastructure.CommitFailedException : C# Multithreading & SQL Server 2012

Question

我们有一个 C# 多线程(100 个线程)程序，它从数据库中读取记录，每个线程获取一条记录(每个线程一个 Entity Framework 连接)并更新单个数据库表。

前几分钟(5 分钟)程序运行正常，无一异常(exception)，然后突然所有线程开始抛出以下错误消息。大约 1 分钟后，一切都会恢复正常。我认为 SQL Server为单个数据库表获得太多锁(可能试图获取该表上的表锁)或与单个数据库的连接太多并关闭所有连接..

我无法调试它，有人可以帮助我获取以下信息吗，

1. SQL Server 2012 将其日志存储在哪里？

2. 我们能否提高日志级别以查看为什么它在保存数据库实体时抛出异常

3. 如何获取每个表的锁数，DB获取的不同类型的锁(表锁、页锁、行数锁等)

4. 调试此问题的任何其他指示。

仅供引用，我在从这个命令 (SELECT SERVERPROPERTY('ErrorLogFileName')) 获得的 sqlerror 日志中没有发现任何有用的信息

这是异常的堆栈跟踪

System.Data.Entity.Infrastructure.CommitFailedException: An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information.

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

System.ComponentModel.Win32Exception: The wait operation timed out

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)<br/>
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)<br/>
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)<br/>
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()<br/>
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()<br/>
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()<br/>
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)<br/>
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)<br/>
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)<br/>
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)<br/>
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)<br/>
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)<br/>
at System.Data.SqlClient.SqlInternalTransaction.Commit()<br/>
at System.Data.SqlClient.SqlTransaction.Commit()<br/>
at System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.<Commit>b__c(DbTransaction t, DbTransactionInterceptionContext c)<br/>
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action`3 executed)
--- End of inner exception stack trace ---

at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed)
at System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.Commit(DbTransaction transaction, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.EntityClient.EntityTransaction.Commit()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)<br/>
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)<br/>
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()<br/>
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
at System.Data.Entity.DbContext.SaveChanges()

Answer 1

我曾经遇到过同样的问题。如果线程应用程序对所有线程使用相同的上下文对象，我们就会遇到这类问题。为每个线程创建单独的上下文对象。您可能会在 RAM 上增加一些负载，但它可以清楚地了解上下文中实体的状态。

List<Task> tasks = new List<Task>();
foreach (var item in list)
{
   ObjectContext oContext = new ObjectContext("MyConnection");
   Task t = Task.Factory.StartNew(() =>
   {
      this.Update(item,oContext);
   });
   tasks.Add(t);
}

Task.WaitAll(tasks.ToArray());