Question when execute multi query on same time using threading get error 40 ?

[ahmedaziz]

I work on unity csharp . i try to execute multi query on sql sever database using async await threading on same time but i get error

so How to solve this error please and why this error generated and reasons for that

SqlException: Snix_Connect (provider: SNI_PN7, error: 40 - SNI_ERROR_40)

Snix_Connect (provider: SNI_PN7, error: 40 - SNI_ERROR_40)

System.Data.SqlClient.SqlInternalConnectionTds..ctor (System.Data.ProviderBase.DbConnectionPoolIdentity identity, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Object providerInfo, System.Boolean redirectedUserInstance, System.Data.SqlClient.SqlConnectionString userConnectionOptions, System.Data.SqlClient.SessionData reconnectSessionData, System.Boolean applyTransientFaultHandling) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.SqlClient.SqlConnectionFactory.CreateConnection (System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, System.Object poolGroupProviderInfo, System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningConnection, System.Data.Common.DbConnectionOptions userOptions) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection (System.Data.Common.DbConnection owningConnection, System.Data.ProviderBase.DbConnectionPoolGroup poolGroup, System.Data.Common.DbConnectionOptions userOptions) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.ProviderBase.DbConnectionFactory.TryGetConnection (System.Data.Common.DbConnection owningConnection, System.Threading.Tasks.TaskCompletionSource`1[TResult] retry, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection, System.Data.ProviderBase.DbConnectionInternal& connection) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal (System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource`1[TResult] retry, System.Data.Common.DbConnectionOptions userOptions) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection (System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource`1[TResult] retry, System.Data.Common.DbConnectionOptions userOptions) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.SqlClient.SqlConnection.TryOpen (System.Threading.Tasks.TaskCompletionSource`1[TResult] retry) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.SqlClient.SqlConnection.Open () (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.Common.DbDataAdapter.QuietOpen (System.Data.IDbConnection connection, System.Data.ConnectionState& originalState) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.Common.DbDataAdapter.FillInternal (System.Data.DataSet dataset, System.Data.DataTable[] datatables, System.Int32 startRecord, System.Int32 maxRecords, System.String srcTable, System.Data.IDbCommand command, System.Data.CommandBehavior behavior) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.Common.DbDataAdapter.Fill (System.Data.DataTable[] dataTables, System.Int32 startRecord, System.Int32 maxRecords, System.Data.IDbCommand command, System.Data.CommandBehavior behavior) (at <290425a50ff84a639f8c060e2d4530f6>:0)

System.Data.Common.DbDataAdapter.Fill (System.Data.DataTable dataTable) (at <290425a50ff84a639f8c060e2d4530f6>:0)

(wrapper remoting-invoke-with-check) System.Data.Common.DbDataAdapter.Fill(System.Data.DataTable)

StockUpdate.ProcessUpdateTask (TextureContainer textureContainer, System.Collections.Generic.List`1[T] assignedShelfFaceDatas, Automation automation, UnionCoop.Core.data.Stock stock) (at <572a6c74c0474fd7ae89cf224552a56e>:0)

StockUpdate+<>c__DisplayClass10_4.<UpdateVirtualStoreAsync>b__6 () (at <572a6c74c0474fd7ae89cf224552a56e>:0)

System.Threading.Tasks.Task`1[TResult].InnerInvoke () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Threading.Tasks.Task.Execute () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

--- End of stack trace from previous location where exception was thrown ---

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

StockUpdate+<UpdateVirtualStoreAsync>d__10.MoveNext () (at <572a6c74c0474fd7ae89cf224552a56e>:0)

--- End of stack trace from previous location where exception was thrown ---

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

Automation+<UpdateVirtualStore>d__69.MoveNext () (at <572a6c74c0474fd7ae89cf224552a56e>:0)

--- End of stack trace from previous location where exception was thrown ---

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () (at <eae584ce26bc40229c1b1aa476bfa589>:0)

System.Runtime.CompilerServices.AsyncMethodBuilderCore+<>c.<ThrowAsync>b__6_0 (System.Object state) (at <eae584ce26bc40229c1b1aa476bfa589>:0)

UnityEngine.UnitySynchronizationContext+WorkRequest.Invoke () (at <85d1d3e7744a4a47b5f51883bf40bba2>:0)

UnityEngine.UnitySynchronizationContext:ExecuteTasks()

 

[Skydiver]

As I recall from many years ago when I used to use SQL, that error is basically a failure to connect to the database. Assuming that your connections work at least once, then the likely issue that you have exhausted the maximum number of connections. If you are running into connection pool exhaustion, that means that you are hitting your database way too many times and may need to re-examine your code to remove those repetitive calls and/or implement some form of caching.

If you can't even make a single connection, then your problem is not really about async/await but simply about your connection string.

Any which way, you need to show us your code. All of this is pretty much guesswork on our part because all you've shown us is the callstack for the exception.

Even better than just showing us your code, is giving us the minimal code to reproduce your problem. Ideally, the minimal code wouldn't even require the use of Unity, and just be a simple console application.

Also, by any chance are you sharing the connection across threads? If so, don't do that. The connection object itself is not thread safe.