ErikEJ
commented
4 years ago Have you tried increasing the command timeout?
Closed frankyuan closed 1 month ago
ErikEJ
commented
4 years ago Have you tried increasing the command timeout?
frankyuan
commented
4 years ago Thanks, @ericstj, No, I didn't try increasing command timeout. Currently, the command timeout is 3s. If a query executes timeout, I got stack like below, seems it is different than the above stack, do you mean the above stack is also a SQL execution timeout? By the way, from New relic, the select query's duration is 83.4ms, I'm not sure if it is the execution time.
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding
Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.\n
---> System.ComponentModel.Win32Exception (258): Unknown error 258\n
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)\n
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()\n
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\n
--- End of stack trace from previous location where exception was thrown ---\n
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)\n
--- End of stack trace from previous location where exception was thrown ---\n
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 419\n
JRahnama
commented
4 years ago @frankyuan, thanks for bringing this up to our attention, can you please let me know if:
Thank you
frankyuan
commented
4 years ago Sorry for reply delay, here is more detail information(by the way, not every cluster have above error, just in one cluster):
Docker container is using Linux? Yes
Are you using Azure SQL? No
If I have noticed correctly you could not see the error on your local machine. If I am right, is your local machine using Windows OS? Yes, my local machine is windows 10.
Is multi-threading, such as PLINQ or Parallel queries are used inside the code? No, we didn't use multi-threading, just using Dapper.SqlMapper's async method (QueryAsync). And I have asked in Dapper. StackExchange/Dapper#1435
JRahnama
commented
4 years ago @frankyuan thanks for the response. Can you provide sample repro code? If sample repro is not possible (without sample app it is really difficult to understand what is happening), can you share the connection string properties please?
frankyuan
commented
4 years ago SqlConnectionString like this: Data Source=xxx;Initial Catalog=xx;User ID=xx;Password=xx;Min Pool Size=5;Max Pool Size=100;Connect Timeout=8. CommandTimeout=3. As I can't reproduce in my local environment, I'm afraid can't provide sample repro for now. But I'm trying to reproduce, if find a method, will update here.
frankyuan
commented
4 years ago Seems only the SQL mentioned in description will throw the exception. And sometimes there is another similar exception like below when executing the same SQL.
Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.\n
---> System.ComponentModel.Win32Exception (258): Unknown error 258\n
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\n
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\n
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\n
at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)\n
at Microsoft.Data.SqlClient.SqlDataReader.ReadAsync(CancellationToken cancellationToken)\n
--- End of stack trace from previous location where exception was thrown ---\n
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 437\n@frankyuan any update on the issue on your side? Were you able to repro the issue? If not, can you kindly share your code or a similar code with me? I need the query you run and setting you have on your machine please. On the other note, can you test it with increasing Connection and command time out as well?
Svisstack
commented
3 years ago I'm experiencing a similar issue
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (258): No error information at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location where exception was thrown --- at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) ClientConnectionId:559501c9-13be-4150-a8bc-ef047cfe491a Error Number:-2,State:0,Class:11 --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
`
.NET target: Core 3.1 Operating system: Docker container (alpine)
lukas-navratil
commented
3 years ago We are also having similar issue like @Svisstack .
Microsoft.Data.SqlClient 2.0.1, .NET Core 3.1, running in Windows container and connecting to SQL Azure Hyperscale DB.
We have a multi-threaded service, lets say about 30 parallel threads that are writing data to the same database. There is a transaction per thread. The service is running fine for most of the time, but from time to time, some threads start to get the exceptions below and they are stuck on it. The command that is failing on this timeout is the same for all threads, but in each occurrence it's usually a different command. Quite often it's a command that is sending some table valued parameters, but we saw it to fail on a very basic SELECT with a single parameter as well. There is no activity on the DB side, connections are in awaiting command state.
From our observations it seems that when this exception is thrown, all threads are blocked on it. They never recovered from it.
---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 483When we noticed this exception, it seems that only some threads were affected and after approximately 30 minutes the issue disappeared.
---> System.ComponentModel.Win32Exception (121): The semaphore timeout period has expired.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 419I would like to provide a repro, but it's quite hard to reproduce that nondeterministic behavior.
Interesting is that we have 6 instances of this service, where each instance is connecting to different DB and has different number of threads, but we noticed the issue only on 2 instances. One of them is the above mentioned one with 30-35 threads, another one is way smaller, only 3-4 threads.
frankyuan
commented
3 years ago @frankyuan any update on the issue on your side? Were you able to repro the issue? If not, can you kindly share your code or a similar code with me? I need the query you run and setting you have on your machine please. On the other note, can you test it with increasing Connection and command time out as well?
Sorry for getting back to you late, I couldn't reproduce this issue currently, and the only clue is that there are more than 3000 records in the result, after I changed the logic to get fewer records, the exception is gone.
JRahnama
commented
3 years ago @frankyuan thanks for the update.
@Svisstack, @lukas-navratil can any of you guys provide a sample repro application please?
Svisstack
commented
3 years ago I dont have this possibility unfortunately.
Sent from my iPhone
On 25 Sep 2020, at 19:27, Javad notifications@github.com wrote:
@frankyuan thanks for the update.
@Svisstack, @lukas-navratil can any of you guys provide a sample repro application please?
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.
mischaherbrand
commented
3 years ago We are having the same issue, it happens randomly on our Azure SQL Server instance. Usually the query is very fast and sometimes it raises the error:
twurm
commented
3 years ago @JRahnama we are seeing the same issues, we will keep an eye on it an see if we can replicate it at will. At the momement is very random:
JRahnama
commented
3 years ago @twurm can you give us a repo application?
twurm
commented
3 years ago @JRahnama we are working on a repo app. The event doesn't happen all the time and happens in different areas of the application so we are trying to figure that out.
Svisstack
commented
3 years ago same experiences
On Mon, Sep 28, 2020 at 11:14 PM Tom Wurm notifications@github.com wrote:
@JRahnama https://github.com/JRahnama we are working on a repo app. The event doesn't happen all the time and happens in different areas of the application so we are trying to figure that out.
twurm
commented
3 years ago @JRahnama we also stumbled across #422 which does have a sample program to recreate the issue. While not the exact issue it is similar for us as we do use MARS.
JRahnama
commented
3 years ago @twurm, regarding the mentioned issue #422, if we reduce the number of parallel runs on that issue to anything less than or equal to 23 everything works fine. As a work around, if you have similar setup, you can try that or set MARS to false ,if possible, for now.
JRahnama
commented
3 years ago @frankyuan, @twurm, @mischaherbrand, @Svisstack, @lukas-navratil while I was trying to make a scenario to reproduce the issue
, I came up with a sample code the each first try to read from the pool was throwing the very same error message while the SqlCommand was not wrapped in a using block, but the rest of the recycled connection worked fine until the pool was renewed by reaching its max poolsize or a fatal error happened and pool got cleared and then the first connection out of the pool had the very same issue again. As a workaround can you make sure all your SqlConnection, SqlCommands and SqlDataReaders are wrapped in a using block properly and see if the issue comes up? That will also help me to understand if I am on the right track with the repro.
Thanks everybody
Svisstack
commented
3 years ago Cant confirm that currently, too much code on my side, but will have this in mind
Sent from my iPhone
On 16 Oct 2020, at 23:56, Javad notifications@github.com wrote:
@frankyuan, @twurm, @mischaherbrand, @Svisstack, @lukas-navratil while I was trying to make a scenario to reproduce the issue , I came up with a sample code the each first try to read from the pool was throwing the very same error message while the SqlCommand was not wrapped in a using block, but the rest of the recycled connection worked fine until the pool was renewed by reaching its max poolsize or a fatal error happened and pool got cleared and then the first connection out of the pool had the very same issue again. As a workaround can you make sure all your SqlConnection, SqlCommands and SqlDataReaders are wrapped in a using block properly and see if the issue comes up? That will also help me to understand if I am on the right track with the repro.
Thanks everybody
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.
frankyuan
commented
3 years ago @JRahnama , If I didn't miss something, all SqlCommands in my code are wrapped in using block.
lukas-navratil
commented
3 years ago @JRahnama , I found one command that wasn't wrapped in using block. I will deploy the fix and try to monitor if it helped or not.
lukas-navratil
commented
3 years ago @JRahnama , so it seems that since we deployed 8 days ago the version with added using around SqlCommand, the Win32Exception 258 hasn't been thrown.
However exception 121 was thrown several times. I can't tell whether it would recover from that as we implemented automatic restart of service when it's stuck on the same SqlException for too long. I'm not sure either that it's related to this issue, but it's just a heads up that this error still happens in our environment as I reported it above.
Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
---> System.ComponentModel.Win32Exception (121): The semaphore timeout period has expired.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 419@JRahnama, unfortunately, our app got stuck today on this issue again:
Microsoft.Data.SqlClient.SqlException (0x80131904): Execution 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 Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 483```
mrmartan
commented
3 years ago I am getting this as well. In my case its from within EFC DbContext. ASP.NET Core 3.1 (and its bundled EFC and SqlClient). Running in a container (base image mcr.microsoft.com/dotnet/core/aspnet:3.1) and the SQL Server instance runs on Windows.
I also use the EFC's DB Context Pool: services.AddDbContextPool<SqlStore>(options => options.UseSqlServer(this.configuration.GetSqlConnectionString(nameof(SqlStore))));
One of my colleagues insisted this is comming from SQL login timeouts (you can use that as a hint but I would not rely on that)
My connection string: Data source=***;Initial catalog=***;User ID=***;Connection Timeout=120;TrustServerCertificate=True;Application Name=***
Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---> System.ComponentModel.Win32Exception (258): Unknown error 258
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:d2ee303b-d340-4514-aeff-5a0f5b1fdf04
Error Number:-2,State:0,Class:11@mrmartan the issue may come from different sources and get caught on our driver. We had a case that port was missing on the server side and the exception was caught by our driver as the connection was not stablished. so, basically any reason that prevents stablishing a connection will cause this error. That being said, from TLS setup on the server, skipped ports and internet connection failure to driver issues most probably will produce the same exception. Is it possible for you to provide some sample repro code that causes the same issue?
mrmartan
commented
3 years ago This is happening randomly across out systems. You do typically see it on applications that cause higher loads or on SQL Server instances that are under higher load.
There is nothing special in particular about the applications that experience this. It's happening on both System.Data.SqlClient and Microsoft.Data.SqlClient. AFAICT it is not happening on .NET Framework/Windows (we do not run any .NET Core applications of note on Windows).
I am currently migrating one application that experiences this regularly to .NET 5. Will let you know if that changes anything.
mcanerim
commented
3 years ago @mrmartan any updates?
I have the problem now for several weeks with no luck in fixing it. Making the DbContext lifetime Transient caused the occurrence of this problem to decrease. In Scoped lifetime of context I had this issue several 100 times a day in production with Transient it's 1/10 of it.
Would really appreciate any help with this.
Stack Trace:
at System.Data.SqlClient.SqlCommand.<>c.1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.1.GetResult() at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.1.GetResult() at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__72.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.1.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.TaskLiftingExpressionVisitor.<_ExecuteAsync>d__81.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.AsyncSelectEnumerable2.AsyncSelectEnumerator.1.GetResult() at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.3.MoveNext() in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Aggregate.cs:line 120 at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()
mrmartan
commented
3 years ago So I have migrated one of the applications to .NET 5 and deployed it to our production environment. It is running there only for half a day now but so far the issue is gone. I have even been able to decrease connection timeout from 2 minutes to 5 seconds.
So I would say give .NET 5 a try.
WeihanLi
commented
3 years ago Any progress?
AFimin
commented
3 years ago here are some steps to reproduce, which works for our environment:
mrmartan
commented
3 years ago Well, I have found the error appearing in most of our applications. Can't say whether it is always the same underlying cause but updating to (ASP).NET 5 and/or https://github.com/dotnet/SqlClient/releases/tag/v2.1.0 (did not try 2.0) fixed it.
mcanerim
commented
3 years ago Migrating to .NET Core 5 does not solve the problem, it makes it worse. FYI.
The only solution I found is disabling pooling in the connection string with
Pooling=False;
This is not a good practice so is there any light for this problem?
Mewriick
commented
3 years ago Hello, the problem is not only when you try to read some data from DB, the problem is also when you try commit transactions, wich cause problem because DB successfully insert records, but applications raise exceptions.
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): Unknown error 258 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.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.SqlInternalTransaction.Commit() at System.Data.SqlClient.SqlTransaction.Commit()
@Mewriick , @mcanerim are you using System.Data.SqlClient? The stack trace you have provided is from that library not Microsoft.Data.SqlClient which the original issue was opened for.
Mewriick
commented
3 years ago @JRahnama It is really doesn't matter which client we are using
Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---> System.ComponentModel.Win32Exception (258): Unknown error 258
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at Microsoft.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
at Microsoft.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
at Microsoft.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.Common.DbConnection.BeginDbTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(CancellationToken cancellationToken)The problems with transactions occurs anyway
niuniu007
commented
3 years ago 那么问题应该怎样解决？我的程序是在插入的时候报错。在高负载的情况下
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): Unknown error 258 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.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1060
at Dapper.SqlMapper.ExecuteReaderImpl(IDbConnection cnn, CommandDefinition& command, CommandBehavior commandBehavior, IDbCommand& cmd) in C:\projects\dapper\Dapper\SqlMapper.cs:line 2856
`
WeihanLi
commented
3 years ago I met this timeout problem caused by ssl cert, I used debian based docker image, changed to alpine or ubuntu helps me out
niuniu007
commented
3 years ago 我将db connection 中改了MultipleActiveResultSets=false 后，此问题彻底消失了
cheenamalhotra
commented
3 years ago Translated by Google: I changed the multiplyActiveResultSets in the db connection to false, and the problem disappeared completely
@niuniu007 Yes this issue exists for MARS enabled connections only.
brjasha
commented
3 years ago Hello,
I experience same problems without MARS and with no DbContext pooling.
Here is my connection string:
Data Source=server_IP;Initial Catalog=my_db;User ID=my_user;Password=my_password;Max Pool Size=200
DbContext:
services.AddDbContext<MyDbContext>(o => { o.UseSqlServer(Configuration.GetConnectionString("MyDbContext")); o.EnableSensitiveDataLogging(); }, ServiceLifetime.Transient);
JRahnama
commented
3 years ago @brjasha You have defined a Max Pool Size of 200 and never set the pooling to false. According to MS Document
Pooling connections can significantly enhance the performance and scalability of your application. By default, connection pooling is enabled in ADO.NET. Unless you explicitly disable it....
Try disabling the pool and see what happens.
There is one more item in your DbContext that I am going to look deeper into it, ServiceLifetime.Transient.
brjasha
commented
3 years ago @JRahnama I'm afraid to disable connection pooling, as he have pretty high load - around 50 requests per second.
ServiceLifetime.Transient - this should work opposite to DbContext pooling - for each request for a service out of dependency injection container I should be getting a new instance of DbContext
ephra-samuel
commented
3 years ago here i got this error when inserting to database,
with stack :
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\n
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\n
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\n
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\n
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)\n
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)\n
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\n
at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action2 paramReader) in C:\\projects\\dapper\\Dapper\\SqlMapper.cs:line 2806\n at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in C:\\projects\\dapper\\Dapper\\SqlMapper.cs:line 568\n
at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 441\n```@JRahnama so I finally tried disabling pooling - didn't help. I also changed DbContext injection to:
services.AddDbContextPool<MyDbContext>(o => { o.UseSqlServer(Configuration.GetConnectionString("MyDbContext")); });
didn't help.
I also tried these settings in connection string:
PoolBlockingPeriod=NeverBlock;ConnectRetryCount=3;ConnectRetryInterval=5;
probably brought something in a form, that errors still happen, but it doesn't produce such big avalanche of errors.
What finally seemed to help - I took several places in the code, which are most often used (all were SELECT queries) and changed those from async to sync. It still produced like 30 error messages within 12 hours, but before it was several thousands per hour. Probably, if I rewrite several more "hot" places, even those 30 error will go away.
This module is quite heavily loaded - around 250 requests per second. Do you have an idea, what kind of connection is there between sync/async and load?
JRahnama
commented
3 years ago @brjasha, by any chance can you test the code with the latest released version of M.D.S (Microsoft.Data.SqlClient)? we have had couple of serious issues/bugs in the driver which was ported from S.D.S (System.Data.SqlClient) and now are fixed. You can follow the problem on issue #659.
brjasha
commented
3 years ago @JRahnama it seems, that it brought some improvements. Now with async queries it still produces those timeout errors, but not that many / not that often.
But I can't confirm, that problem was resolved. I reverted back to sync queries.
JRahnama
commented
3 years ago @brjasha I am happy that we have seen some improvements in this section. We will get back to this soon.
Describe the bug
When executing SQL such as
SELECT FieldA, FieldB FROM A INNER JOIN C ON A.FieldId = C.FieldId UNION SELECT FieldA, FieldD FROM A INNER JOIN D ON A.FieldId = D.FieldId, throw the error like below, not every time, just a little part of queries have this issue.To reproduce
Sorry, currently can't reproduce in the local environment, so can't provide more detail to reproduce.
Expected behavior
SQL should execute successfully every time.
Further technical details
Microsoft.Data.SqlClient version: 1.1.3 .NET target: Core 3.1 Operating system: Docker container
What I found/tried
https://stackoverflow.com/questions/57270245/sql-server-dbcommand-timeout-with-net-core-container-under-load
https://github.com/StackExchange/Dapper/issues/1435