System.Data.Entity.Infrastructure.CommitFailedException:C# 多线程 &SQL Server 2012 [英] System.Data.Entity.Infrastructure.CommitFailedException: C# Multithreading & SQL Server 2012

查看：22
本文介绍了System.Data.Entity.Infrastructure.CommitFailedException:C# 多线程 &SQL Server 2012的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们有一个 C# 多线程(100 个线程)程序，它从数据库中读取记录，每个线程选取一条记录(每个线程一个实体框架连接)并更新单个数据库表.

We have a C# multi threaded (100 threads) program which reads the records from the DB and each thread picks up one record (one Entity Framework connection per thread) and update the a single DB table.

最初几分钟(5 分钟)，程序运行正常，无一例外，然后突然所有线程开始抛出以下错误消息.. 约 1 分钟后，一切都会恢复正常阶段.. 我认为 SQL Server为单个 DB 表获取太多锁(可能正在尝试获取该表上的表锁)或到单个 DB 的连接太多并关闭所有连接..

For first few minutes (5 minutes) the program works fine without exception then all of a sudden all threads starts throwing the below error messages.. After ~1 min everything will come back to normal stage.. I think the SQL Server is getting too many locks for a single DB table (might be trying to acquire table lock on that table) or too many connections to a single DB and closing all the connections..

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

I am unable to debug this, can some one help me in getting the following information,

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

  1. Where does SQL Server 2012 store its logs?

我们能否提高日志级别，看看为什么在保存数据库实体时会抛出异常

Can we increase the log level to see why it throws an exception while saving the DB entity

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

How to get the number of locks per a table, different kind of locks (table lock, page lock, num row locks, etc) acquired by DB

用于调试此问题的任何其他指针.

Any other pointers to debug this issue.

仅供参考，我在从这个 cmd (SELECT SERVERPROPERTY('ErrorLogFileName')) 得到的 sqlerror 日志中没有发现任何有用的东西

FYI, I didn't find anything useful in sqlerror log got from this cmd (SELECT SERVERPROPERTY('ErrorLogFileName'))

这里是异常的堆栈跟踪

System.Data.Entity.Infrastructure.CommitFailedException:提交数据库事务时报告错误，但无法确定数据库服务器上的事务是成功还是失败.请参阅内部异常和 http://go.microsoft.com/fwlink/?LinkId=313468 了解更多信息.

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:超时已过期.操作完成之前超时时间已过或服务器没有响应.

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: 等待操作超时

System.ComponentModel.Win32Exception: The wait operation timed out

在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException 异常, Boolean breakConnection, Action1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 错误)
在 System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
在 System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
在 System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
在 System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
在 System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
在 System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
在 System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
在 System.Data.SqlClient.SqlInternalTransaction.Commit()
在 System.Data.SqlClient.SqlTransaction.Commit()
在 System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.b__c(DbTransaction t, DbTransactionInterceptionContext c)
在 System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget 目标，Action2 操作，TInterceptionContext 拦截上下文，Action3 执行，Action`3 执行)
--- 内部异常堆栈跟踪结束---

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalTransaction.Commit()
at System.Data.SqlClient.SqlTransaction.Commit()
at System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.<Commit>b__c(DbTransaction t, DbTransactionInterceptionContext c)
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, TInterceptionContextinterceptionContext, Action3 execution, Action3 执行)
在 System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.Commit(DbTransaction 事务，DbInterceptionContext 拦截上下文)
在 System.Data.Entity.Core.EntityClient.EntityTransaction.Commit()
在 System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
在 System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
在 System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
在 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
在 System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions 选项，布尔值 executeInExistingTransaction)
在 System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions 选项)
在 System.Data.Entity.Internal.InternalContext.SaveChanges()
在 System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
在 System.Data.Entity.DbContext.SaveChanges()

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)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
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()

推荐答案

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

I used to face the same sssue.If the threaded app is using same context object for all the threads we face these kind of issues. Create separate context objects for each thread.You might be adding some more load on your RAM but it gives clarity about states of enities in Context.

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());

这篇关于System.Data.Entity.Infrastructure.CommitFailedException:C# 多线程 &amp;SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆