search

danielgerlag / workflow-core

Lightweight workflow engine for .NET Standard
MIT License
5.34k stars 1.19k forks source link

Deadlock Exception in EntityFramework with SQL Server provider #506

Open knoxi opened 4 years ago

knoxi commented 4 years ago

Since I have upgraded my solution to .net core 3.1 and to the newest workflow core libraries, I get a sporadic deadlock exception while trying to read a workflow instance.

var internalWorkflow = await workflowStore.GetWorkflowInstance(workflowId).ConfigureAwait(false); or var internalWorkflows = await workflowStore.GetWorkflowInstances(workflowIds).ConfigureAwait(false);

We are using the SQL Server persistence and lock provider.

Did I missed something in the upgrade? How can I handle this issue? 

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.<>c__DisplayClass195_0.<ReadAsync>b__1(Task t)
   at Microsoft.Data.SqlClient.SqlDataReader.InvokeRetryable[T](Func`2 moreFunc, TaskCompletionSource`1 source, IDisposable objectToDispose)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 180
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.<MoveNextAsync>d__17.MoveNext() in /_/src/EFCore.Relational/Query/Internal/QueryingEnumerable.cs:line 291
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 180
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 152
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.<SingleAsync>d__21`1.MoveNext() in /_/src/EFCore/Query/ShapedQueryCompilingExpressionVisitor.cs:line 115
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.<SingleAsync>d__21`1.MoveNext() in /_/src/EFCore/Query/ShapedQueryCompilingExpressionVisitor.cs:line 127
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 180
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 152
   at WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider.<GetWorkflowInstance>d__8.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 180
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 152
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult() in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 563
   at WorkflowServer.Logic.Services.WorkflowService.<GetWorkflowById>d__27.MoveNext() in C:\Repos\WorkflowServer\Logic\WorkflowServer.Logic\Services\WorkflowService.cs:line 595

  This exception was originally thrown at this call stack:
    Microsoft.Data.SqlClient.SqlConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action<System.Action>)
    Microsoft.Data.SqlClient.SqlInternalConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action<System.Action>)
    Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject, bool, bool)
    Microsoft.Data.SqlClient.TdsParser.TryRun(Microsoft.Data.SqlClient.RunBehavior, Microsoft.Data.SqlClient.SqlCommand, Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.BulkCopySimpleResultSet, Microsoft.Data.SqlClient.TdsParserStateObject, out bool)
    Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(out bool)
    Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(bool, out bool)
    Microsoft.Data.SqlClient.SqlDataReader.ReadAsync.AnonymousMethod__1(System.Threading.Tasks.Task)
    Microsoft.Data.SqlClient.SqlDataReader.InvokeRetryable<T>(System.Func<System.Threading.Tasks.Task, System.Threading.Tasks.Task<T>>, System.Threading.Tasks.TaskCompletionSource<T>, System.IDisposable)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in ExceptionDispatchInfo.cs
    System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task) in TaskAwaiter.cs
    ...
    [Call Stack Truncated]
    ...
    [Resuming Async Method] 
    System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Line 172  C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<WorkflowServer.Common.Entities.Workflow.Workflow>.AsyncStateMachineBox<WorkflowServer.Logic.Services.WorkflowService.<GetWorkflowById>d__27>.MoveNext(System.Threading.Thread threadPoolThread) Line 617  C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.TaskAwaiter.OutputWaitEtwEvents.AnonymousMethod__12_0(System.Action innerContinuation, System.Threading.Tasks.Task innerTask) Line 304   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining) Line 742  C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.RunContinuations(object continuationObject) Line 3326    C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.FinishSlow(bool userDelegateExecute) Line 2076   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.TrySetException(object exceptionObject) Line 3226    C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<WorkflowCore.Models.WorkflowInstance>.SetException(System.Exception exception) Line 784   C#
    WorkflowCore.Persistence.EntityFramework.dll!WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider.GetWorkflowInstance(string Id)    Unknown
    WorkflowCore.Persistence.EntityFramework.dll!WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider.GetWorkflowInstance(string Id)    Unknown
    [Resuming Async Method] 
    System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Line 172  C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<WorkflowCore.Models.WorkflowInstance>.AsyncStateMachineBox<WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider.<GetWorkflowInstance>d__8>.MoveNext(System.Threading.Thread threadPoolThread) Line 617    C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.TaskAwaiter.OutputWaitEtwEvents.AnonymousMethod__12_0(System.Action innerContinuation, System.Threading.Tasks.Task innerTask) Line 304   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining) Line 742  C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.RunContinuations(object continuationObject) Line 3326    C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.FinishSlow(bool userDelegateExecute) Line 2076   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.TrySetException(object exceptionObject) Line 3226    C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>.SetException(System.Exception exception) Line 784  C#
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>(System.Collections.Generic.IAsyncEnumerable<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow> asyncEnumerable, System.Threading.CancellationToken cancellationToken) Line 127   C#
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>(System.Collections.Generic.IAsyncEnumerable<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow> asyncEnumerable, System.Threading.CancellationToken cancellationToken) Line 127   C#
    [Resuming Async Method] 
    System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Line 172  C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>.AsyncStateMachineBox<Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.<SingleAsync>d__21<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>>.MoveNext(System.Threading.Thread threadPoolThread) Line 617  C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.TaskAwaiter.OutputWaitEtwEvents.AnonymousMethod__12_0(System.Action innerContinuation, System.Threading.Tasks.Task innerTask) Line 304   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining) Line 742  C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.RunContinuations(object continuationObject) Line 3326    C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.FinishSlow(bool userDelegateExecute) Line 2076   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.TrySetException(object exceptionObject) Line 3226    C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<bool>.SetException(System.Exception exception) Line 784   C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncValueTaskMethodBuilder<bool>.SetException(System.Exception exception) Line 164  C#
    Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>.AsyncEnumerator.MoveNextAsync() Line 289    C#
    Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>.AsyncEnumerator.MoveNextAsync() Line 289    C#
    [Resuming Async Method] 
    System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Line 172  C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<bool>.AsyncStateMachineBox<Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<WorkflowCore.Persistence.EntityFramework.Models.PersistedWorkflow>.AsyncEnumerator.<MoveNextAsync>d__17>.MoveNext(System.Threading.Thread threadPoolThread) Line 617   C#
    System.Private.CoreLib.dll!System.Runtime.CompilerServices.TaskAwaiter.OutputWaitEtwEvents.AnonymousMethod__12_0(System.Action innerContinuation, System.Threading.Tasks.Task innerTask) Line 304   C#
    System.Private.CoreLib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining) Line 742  C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.RunContinuations(object continuationObject) Line 3326    C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task<bool>.TrySetResult(bool result) Line 419 C#
    System.Private.CoreLib.dll!System.Threading.Tasks.TaskCompletionSource<bool>.TrySetResult(bool result) Line 271 C#
    Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlDataReader.CompleteRetryable<bool>(System.Threading.Tasks.Task<bool> task, System.Threading.Tasks.TaskCompletionSource<bool> source, System.IDisposable objectToDispose)   Unknown
    System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(System.Threading.Thread threadPoolThread, System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Line 289  C#
    System.Private.CoreLib.dll!System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref System.Threading.Tasks.Task currentTaskSlot, System.Threading.Thread threadPoolThread) Line 2389  C#
    System.Private.CoreLib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch() Line 663 C#
    [Async Call Stack]  
    ...
danielgerlag commented 4 years ago

Are you running multiple nodes?

knoxi commented 4 years ago

No, we don't run multiple nodes, but we configured it in the startup for being ready if it becomes necessary.

xophp commented 4 years ago

Hello, I have the exact same error after upgrading to 3.1.

Regards, Christoph

danielgerlag commented 4 years ago

No code has changed from our side, only the version of EF... perhaps the new version has a different default transaction isolation level or something. I will do some digging.

FrancoisCamus commented 4 years ago

Same issue here. Any workaround? We'll downgrade EF Core to see if it fixes the issue.

SebastienLagrange commented 4 years ago

For SQL Server provider, we have the same issue, and using SNAPSHOT isolation instead of default READ_COMMITED is better on our side. You can activate those options with the following statements :

ALTER DATABASE
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE
SET READ_COMMITTED_SNAPSHOT ON

Here some documentation about what is Snapshot isolation level in SQL Server: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

pbros commented 4 years ago

We have been facing the same issue, and configuring SNAPSHOT isolation on SQL seemed to have resolved the issue. That being said, I think would be good preferable if workflow-core did it itself using

SqlTransaction sqlTran =   
  connection.BeginTransaction(IsolationLevel.Snapshot);
danielgerlag commented 4 years ago

I have released a new version with the latest EF core and removed MARS. I have no been able to reproduce the issue, but please test with the latest version and let me know?

knoxi commented 4 years ago

I was able to (perhaps) resolve the deadlock based on the hint of @SebastienLagrange by enable the Is Read Committed Snapshot On flag on my affected database. Since I have enabled this flag, I couldn't reproduce the deadlock. But I can verify your newest build against a database without enabled this flag.

ekersale commented 4 years ago

@danielgerlag, I ran our integration tests three times without deadlock, so it seems to be fixed in your latest version.

Edit: Well in fact it just happened on the 4th run. It's clearly more stable as before, since I faced the issue during almost all test run before, but there is still a few issues apparently. 


    Microsoft.Data.SqlClient.SqlException : Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  Stack Trace: 
    SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    SqlDataReader.TryHasMoreRows(Boolean& moreRows)
    SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
    <>c__DisplayClass195_0.<ReadAsync>b__1(Task t)
    SqlDataReader.InvokeRetryable[T](Func`2 moreFunc, TaskCompletionSource`1 source, IDisposable objectToDispose)
    --- End of stack trace from previous location where exception was thrown ---
    AsyncEnumerator.MoveNextAsync()
    EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
    EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
    EntityFrameworkPersistenceProvider.GetWorkflowInstances(Nullable`1 status, String type, Nullable`1 createdFrom, Nullable`1 createdTo, Int32 skip, Int32 take) ```
knoxi commented 4 years ago

I also couldn't reproduce it with the latest patch. But I noticed some exceptions in the Application Insights logs of our running instance on Azure. I will have a look if they still occur with your patch.

Would it be possible to extend your extension UseSqlServer(...) with the possibility to set EnableRetryOnFailure() as it is proposed in the exception message?

Microsoft.Data.SqlClient.SqlException at WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider+<GetRunnableInstances>d__6.MoveNext

An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseSqlServer' call. Database 'sql-DB001' on server 'sql-srv' is not currently available.  Please retry the connection later. 

System.InvalidOperationException:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+AsyncEnumerator+<MoveNextAsync>d__17.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__64`1.MoveNext (Microsoft.EntityFrameworkCore, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__64`1.MoveNext (Microsoft.EntityFrameworkCore, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider+<GetRunnableInstances>d__6.MoveNext (WorkflowCore.Persistence.EntityFramework, Version=3.0.0.0, Culture=neutral, PublicKeyToken=null)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Services.BackgroundTasks.RunnablePoller+<PollRunnables>d__9.MoveNext (WorkflowCore, Version=3.1.5.0, Culture=neutral, PublicKeyToken=null)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Services.BackgroundTasks.RunnablePoller+<PollRunnables>d__9.MoveNext (WorkflowCore, Version=3.1.5.0, Culture=neutral, PublicKeyToken=null)
Inner exception Microsoft.Data.SqlClient.SqlException handled at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext:
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlConnection.OpenAsync (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenDbConnectionAsync>d__50.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenDbConnectionAsync>d__50.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenAsync>d__47.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__17.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+AsyncEnumerator+<InitializeReaderAsync>d__18.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)

Microsoft.Data.SqlClient.SqlException at WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider+<GetRunnableEvents>d__16.MoveNext

An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseSqlServer' call. Database 'sql-DB001' on server 'sql-srv' is not currently available.  Please retry the connection later. 

System.InvalidOperationException:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+AsyncEnumerator+<MoveNextAsync>d__17.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__64`1.MoveNext (Microsoft.EntityFrameworkCore, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__64`1.MoveNext (Microsoft.EntityFrameworkCore, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider+<GetRunnableEvents>d__16.MoveNext (WorkflowCore.Persistence.EntityFramework, Version=3.0.0.0, Culture=neutral, PublicKeyToken=null)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Services.BackgroundTasks.RunnablePoller+<PollRunnables>d__9.MoveNext (WorkflowCore, Version=3.1.5.0, Culture=neutral, PublicKeyToken=null)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Services.BackgroundTasks.RunnablePoller+<PollRunnables>d__9.MoveNext (WorkflowCore, Version=3.1.5.0, Culture=neutral, PublicKeyToken=null)
Inner exception Microsoft.Data.SqlClient.SqlException handled at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext:
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen (Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenDbConnectionAsync>d__50.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenDbConnectionAsync>d__50.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenAsync>d__47.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__17.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+AsyncEnumerator+<InitializeReaderAsync>d__18.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
vasilispantelisswo commented 4 years ago

Hi, we have the same issue. I was able to trace the deadlock in SQL Server, it seems to happen when the host tries to update the "Data" column of the "Workflow" table while running the "GetWorkflowInstance" method of the "IWorkflowRepository". Here is the trace from SQL Server:

<deadlock>
 <victim-list>
  <victimProcess id="process1d6852b24e8" />
 </victim-list>
 <process-list>
  <process id="process1d6852b24e8" taskpriority="0" logused="0" waitresource="KEY: 291:72057594041532416 (b2d105b556d2)" waittime="4426" ownerId="1283598" transactionname="SELECT" lasttranstarted="2020-03-05T16:08:37.410" XDES="0x1d688e79a40" lockMode="S" schedulerid="7" kpid="105992" status="suspended" spid="59" sbid="3" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-03-05T16:08:37.407" lastbatchcompleted="2020-03-05T16:08:37.360" lastattention="1900-01-01T00:00:00.360" clientapp="Core .Net SqlClient Data Provider" hostname="host" hostpid="1344954369" loginname="domain\user" isolationlevel="read committed (2)" xactid="1283598" currentdb="291" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="52" stmtend="2296" sqlhandle="0x0200000033a6471dbea089e9d22517258f3328df5051dd590000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@__id_0 uniqueidentifier)SELECT [wf.ExecutionPointers].[PersistenceId], [wf.ExecutionPointers].[Active], [wf.ExecutionPointers].[Children], [wf.ExecutionPointers].[ContextItem], [wf.ExecutionPointers].[EndTime], [wf.ExecutionPointers].[EventData], [wf.ExecutionPointers].[EventKey], [wf.ExecutionPointers].[EventName], [wf.ExecutionPointers].[EventPublished], [wf.ExecutionPointers].[Id], [wf.ExecutionPointers].[Outcome], [wf.ExecutionPointers].[PersistenceData], [wf.ExecutionPointers].[PredecessorId], [wf.ExecutionPointers].[RetryCount], [wf.ExecutionPointers].[Scope], [wf.ExecutionPointers].[SleepUntil], [wf.ExecutionPointers].[StartTime], [wf.ExecutionPointers].[Status], [wf.ExecutionPointers].[StepId], [wf.ExecutionPointers].[StepName], [wf.ExecutionPointers].[WorkflowId]
FROM [wfc].[ExecutionPointer] AS [wf.ExecutionPointers]
INNER JOIN (
    SELECT TOP(1) [wf0].[PersistenceId]
    FROM [wfc].[Workflow] AS [wf0]
    WHERE [wf0].[InstanceId] = @__id_0
    ORDER BY [wf0].[PersistenceId]
) AS [t] ON [   </inputbuf>
  </process>
  <process id="process1d69201d088" taskpriority="0" logused="972" waitresource="KEY: 291:72057594041466880 (261b46e90185)" waittime="4422" ownerId="1283472" transactionname="user_transaction" lasttranstarted="2020-03-05T16:08:37.400" XDES="0x1d685c5f068" lockMode="X" schedulerid="1" kpid="73164" status="suspended" spid="60" sbid="3" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-03-05T16:08:37.407" lastbatchcompleted="2020-03-05T16:08:37.407" lastattention="1900-01-01T00:00:00.407" clientapp="Core .Net SqlClient Data Provider" hostname="host" hostpid="1344954369" loginname="domain\user" isolationlevel="read committed (2)" xactid="1283472" currentdb="291" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="96" stmtend="232" sqlhandle="0x020000001ef7be36db54a73d7008436935f5d0321000b62a0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p1 bigint,@p0 nvarchar(max) )SET NOCOUNT ON;
UPDATE [wfc].[Workflow] SET [Data] = @p0
WHERE [PersistenceId] = @p1;
SELECT @@ROWCOUNT;

   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594041532416" dbid="291" objectname="workflows_e88a090a-e998-4e01-8bbb-fbee97a742eb.wfc.ExecutionPointer" indexname="PK_ExecutionPointer" id="lock1d692719380" mode="X" associatedObjectId="72057594041532416">
   <owner-list>
    <owner id="process1d69201d088" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d6852b24e8" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594041466880" dbid="291" objectname="workflows_e88a090a-e998-4e01-8bbb-fbee97a742eb.wfc.Workflow" indexname="PK_Workflow" id="lock1d692725e00" mode="U" associatedObjectId="72057594041466880">
   <owner-list>
    <owner id="process1d6852b24e8" mode="S" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d69201d088" mode="X" requestType="convert" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

It seems one has an exclusive lock on "PK_ExecutionPointer" which the other is waiting while also having a a shared lock on "PK_Workflow" which the first one is waiting.

danielgerlag commented 4 years ago

So, the WorkflowRepository is meant to be an internal concern of the library... but if you elect to use it in your own code, I would advise you to also first also use the LockProvider to acquire a lock for that workflow ID before retrieving it, and then releasing the lock once you are done.

vasilispantelisswo commented 4 years ago

Thank you, I can confirm using DistributedLockProvider worked. It's weird that this hasn't happened before and just started happening a few days ago. It also doesn't have to do with the IWorkflowRepository, any kind of query on those tables while an update is happening would probably cause the same deadlock in READ COMMITTED mode. Maybe some change in the EF libraries?

vladimir-kovalyuk commented 3 years ago

So, the WorkflowRepository is meant to be an internal concern of the library... but if you elect to use it in your own code, I would advise you to also first also use the LockProvider to acquire a lock for that workflow ID before retrieving it, and then releasing the lock once you are done.

@danielgerlag if it is internal concern of the library then how we could get the workflow's data from the LifeCycleEvent handler? (BTW there is still issue when WorkflowCompleted is not generated by EndWorkflow step).

vladimir-kovalyuk commented 3 years ago

For those who intend to use distributed locking mechanism I would like to draw attention to the specifics of implementation - it does not wait if the resource has already been acquired. So you need to write smth like a loop to wait for the method AquireLock returns true.

k-krupka commented 2 years ago

Bumping up. It would be great to expose the 'EnableRetryOnFailure' from the EF API.

The error we've got is replicated as above:

An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call. A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) The semaphore timeout period has expired.

Call stack:

System.InvalidOperationException:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1+AsyncEnumerator+<MoveNextAsync>d__18.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__65`1.MoveNext (Microsoft.EntityFrameworkCore, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__65`1.MoveNext (Microsoft.EntityFrameworkCore, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Persistence.EntityFramework.Services.EntityFrameworkPersistenceProvider+<GetRunnableInstances>d__8.MoveNext (WorkflowCore.Persistence.EntityFramework, Version=3.6.3.0, Culture=neutral, PublicKeyToken=null)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Services.BackgroundTasks.RunnablePoller+<PollWorkflows>d__12.MoveNext (WorkflowCore, Version=3.6.3.0, Culture=neutral, PublicKeyToken=null)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at WorkflowCore.Services.BackgroundTasks.RunnablePoller+<PollWorkflows>d__12.MoveNext (WorkflowCore, Version=3.6.3.0, Culture=neutral, PublicKeyToken=null)
Inner exception Microsoft.Data.SqlClient.SqlException handled at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext:
   at Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__188_0 (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.Tasks.Task+<>c.<.cctor>b__272_0 (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.ExecutionContext.RunInternal (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.ExecutionContext.RunInternal (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__19.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__19.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1+AsyncEnumerator+<InitializeReaderAsync>d__19.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=6.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
Inner exception System.ComponentModel.Win32Exception handled at Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__188_0: