Repository navigation
Execution Timeout Expired Error (258, ReadSniSyncOverAsync) #647
Description
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.
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.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\n
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\n
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)\n
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()\n
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()\n
at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()\n
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Span1 buff, Int32 len, Int32& totalRead)\n
at Microsoft.Data.SqlClient.TdsParser.TrySkipValue(SqlMetaDataPriv md, Int32 columnOrdinal, TdsParserStateObject stateObj)\n
at Microsoft.Data.SqlClient.TdsParser.TrySkipRow(SqlMetaDataSet columns, Int32 startCol, TdsParserStateObject stateObj)\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
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
Reacted by bennil, Juris Gekišs, Andre H, Max Kostovetski, Kristy McCall, Alex Tsoi, flgarrid, Dmytry Emery, Greg, Thibault Dolley and 9 more
Reacted by Krystian Mikrut
Activity
ErikEJ commentedon Jul 12, 2020
Have you tried increasing the command timeout?
frankyuan commentedon Jul 12, 2020
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.
JRahnama commentedon Jul 13, 2020
@frankyuan, thanks for bringing this up to our attention, can you please let me know if:
Windows OS?
Thank you
frankyuan commentedon Jul 15, 2020
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. Unknown error 258 : The timeout period elapsed prior to completion DapperLib/Dapper#1435
JRahnama commentedon Jul 15, 2020
@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 commentedon Jul 16, 2020
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 commentedon Jul 20, 2020
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.
JRahnama commentedon Jul 23, 2020
@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 commentedon Aug 27, 2020
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.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.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(IEnumerable
1 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, Func
4 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 commentedon Sep 18, 2020
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.
When we noticed this exception, it seems that only some threads were affected and after approximately 30 minutes the issue disappeared.
I 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 commentedon Sep 19, 2020
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 commentedon Sep 25, 2020
@frankyuan thanks for the update.
@Svisstack, @lukas-navratil can any of you guys provide a sample repro application please?
Svisstack commentedon Sep 25, 2020
mischaherbrand commentedon Sep 28, 2020
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 commentedon Sep 28, 2020
@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:
Windows OS?
152 remaining items
PetteriPertola commentedon Jun 2, 2025
I looked through the changelog - which particular change is supposed to address this? I would like to keep this issue open until it is resolved. It hits us several times a day across multiple different components. Docker & Kubernetes setup on Linux like others in this thread, while same code has no issues when running as a Webapp instead.
MichelZ commentedon Jun 2, 2025
I think those are the one's that have the potential to improve on this situation:
(Also, the current "workaround" still works AFAIK - increase your MinThreads limits as described here)
MichelZ commentedon Jun 2, 2025
Here's the comment talking about the closing of it:
#647 (comment)
rla-cf commentedon Jun 2, 2025
Wow @Wraith2 , THANK YOU for working on this issue for so long.
PetteriPertola commentedon Jun 2, 2025
This workaround isn't feasible if you have let's say 10 or even more applications running on the same Kubernetes node - you can cause thread starvation to other applications even with proper requests/limits set in Kubernetes.
When will this be made into a released version? I can't test the preview 6.1.0 in a production system.
ErikEJ commentedon Jun 2, 2025
@PetteriPertola The planned miletones are listed here: https://github.com/dotnet/SqlClient/milestones
PetteriPertola commentedon Jun 12, 2025
@ErikEJ this should be reopened.
I am running with 6.1.0 preview in production and I am seeing absolutely no change with regards to this issue.
ErikEJ commentedon Jun 12, 2025
@PetteriPertola Sorry, I am not part of the team
PetteriPertola commentedon Jun 12, 2025
@David-Engel - i think this needs to be reopened as this is a persistent issue that only affects the SQL Client driver. We had a similar issue for Oracle connectivity, but using TCP keepalive 30 seconds, and max connection lifetime of 220 seconds fixed it for us. I firmly believe it's the same problem in the SQL Client Driver when deployed to Azure kubernetes and using SQL Server via Azure networking infrastructure. The difference is that we cannot configure keepalive for idle sessions for the client here, so we end up with broken connectivity.
The same issue persists in 6.1.0 preview, using .NET 9, and an azure specific linux image for Docker.
Wraith2 commentedon Jun 12, 2025
If you had a similar issue with another driver it is likely that it is caused by your environment and not sqlclient.
Without a replication there is no way to investigate the issue other than by guesswork.
PetteriPertola commentedon Jun 12, 2025
@Wraith2 unlikely - we see the same across all of our applications, 10-12 times a day in production. Symptom is always the same: slightly more idle traffic wise, the connection in the pool is broken. If running on Linux containers, this happens. If running on a windows based one, it never happens. So it must be related to how the SqlClient is handling the connections - especially idle connections being closed remotely - in a Linux based environment.
Wraith2 commentedon Jun 12, 2025
How do you arrive at the conclusion of it being unlikely when you say it happens over multiple ADO.net providers using tcp in your environment and it isn't happening to other users? It seems like an entirely reasonable correlation to me.
My final point is still the most relevant. Without a replication there is nothing that can be done.
jbogard commentedon Jun 12, 2025
Replication is easy, I just have to undo the fixes described here and it shows up within an hour or so. Just running Linux Azure App Services on Azure SQL.
Or switch back to Windows App Services, that also fixes the issue.
MichelZ commentedon Jun 12, 2025
Do the Windows nodes run on more CPU/Threads by any chance? Also, does slightly increase MinThreads still work?
David-Engel commentedon Jun 12, 2025
@PetteriPertola This error can apply to many failure scenarios. That's why this particular issue was closed. We have other open issues, which are more specific. We are also aware of the generally poor sync-over-async pattern present in several of the code paths inside SqlClient. This pattern can exacerbate performance and errors like this one. We recognize that and are working on different pieces of the problem.
For example, #3356, the connection pool is getting a refactor to improve its async performance and eliminate the designs that made it difficult to eliminate some of the poor sync-over-async patterns. #113 is for implementing true async Begin/End Transaction methods. Work is in progress to eliminate an async-over-sync pattern in the FedAuth flow during connection open (issue #2152, and PR #3392 is prereq work). And we know a refactor needs to happen to get rid of the ReadSniSyncOverAsync() method used in a lot of scenarios. #979, #601, #1562 are other more specific examples.