I have a multi-threading application that needs to insert bulk of records in the same table from different places. After continuously running for 2 hours, my application runs into a connection time out issue.
Stack Trace
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.ComponentModel.Win32Exception (258): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData()
I suspect this may be the cause of deadlock in a particular table. I verified in SQL Server that table is on deadlock.
I have tried the following solution
using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
SERIALIZABLE
and SNAPSHOT isolation levels alsoWhen running the multi-threading application it says "MSDTC Server on unavailable".
Note: I started the Distributed Transaction Coordinator service but I still get your environment not support distributed transactions
I tried with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
, but I still get the same issue after 2 hours of running continuously
I added this SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
between the BeginTransaction
and Commit transaction
in my C# code but no luck.
Could you please someone can help me to overcome from this ?
Also, I am thinking like this may be the cause of multiple connections open between our application and the SQL Server database. FYI. I have my added my query with in the using statement only but still the connection not closed. How I am saying this means the number of connections from sysprocess in SQL Server showing many number of connections open
User contributions licensed under CC BY-SA 3.0