Connection Timeout issue in Entity Framework Core


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

  1. using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
  2. I tried with SERIALIZABLE and SNAPSHOT isolation levels also

When 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

asked on Stack Overflow Feb 20, 2019 by Gopal_k • edited Feb 20, 2019 by marc_s

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0