Evaluate if MultipleActiveResultSets is indeed necessary for SQL Server connections

Question

Evaluate if MultipleActiveResultSets is indeed necessary for SQL Server connections

Closed this issue 8 months ago · 18 comments

Is your feature request related to a problem? Please describe.

For SQL Server, you have to specify MultipleActiveResultSets=True; in you connection string, otherwise Workflows will fail with "Putting the workflow in the faulted state. System.InvalidOperationException: The connection does not support MultipleActiveResultSets". There are apparently multiple cases of this too.

However, I don't think we actually have to require MARS, and it should only be necessary if you do parallelized DB queries via the same DB connection, what you shouldn't.

Describe the solution you'd like

Figure out if we indeed need MARS, or we have unwanted parallelization. If the latter, then let's fix those. If we need MARS, then let's add validation like this: #15210

Describe alternatives you've considered

I can't think of anything else.

Answer 1 · 2024-02-01T19:12:36.000Z

when this error occurs (and I have never seen it with workflows, which we use constantly), it tends to be a missing await somewhere, i.e. the session is being treated as thread safe BUT it is not

There have been some hints that calling multilple .SaveChangesAsync() may cause the above error (came from one of my team), but we refactored out the code that was doing that, so cannot comment further)

Answer 2 · 2024-02-08T19:01:00.000Z

All the issues you pointed to are closed. Can you describe a simple workflow that will make it fail so we can track the issue?

Answer 3 · 2024-02-12T00:53:06.000Z

I did some tests with the latest source and can't find any issues. I'll remove MultipleActiveResultSets=True; from our prod apps' connection strings after upgrading to OC 1.8 and close this if there's still no issue.

Answer 4 · 2024-03-13T18:27:19.000Z

Hmm, was it rather with Audit Trail? I also did some quick checks with that but no issues.

The mentioned 1.8 upgrade of ours is pending.

Answer 5 · 2024-03-13T18:40:14.000Z

Honestly Zoltan I would check any custom code you have.

Everytime this has been reported (you can search the issues) it has been a missing await or an async void usage, or an odd combination of .Result usage

Answer 6 · 2024-03-13T19:09:44.000Z

Most possibly yeah, I'll get back here once we have some experience with 1.8 in prod.

Answer 7 · 2024-03-13T19:15:43.000Z

cool, it happened to me recently when visual studio automatically added an async to an existing method that was already void, and because it was error handling code we didn't see it for three months until the error actually happened, and it went down into that await
Disapointed none of the analzyers that you guys setup and we use didn't pick up on it :( (not your guys fault, just such an obvious thing for an analyzer to spot and complain about.)

Answer 8 · 2024-03-13T19:54:05.000Z

That's strange, because at least one of the analyzers should catch exactly this, chiefly AsyncFixer...

Answer 9 · 2024-03-13T20:14:13.000Z

Best I have a look a the AsyncFixer and see if it's badly configured then... thx

Answer 10 · 2024-03-24T19:15:05.000Z

@sarahelsaig could you please add here what you found with the Roles module?

Answer 11 · 2024-03-30T21:55:18.000Z

Sorry, I didn't notice your message. I have opened an issue about it yesterday here.

Answer 12 · 2024-03-31T16:08:26.000Z

OK, thanks. I'll close this less specific issue if we still don't find any MARS problems with 1.8.2 (upgrading DotNest is still in progress).

Answer 13 · 2024-04-29T21:30:01.000Z

@Piedone you should keep this open. MultipleActiveResultSets for some reason is required for a reason that I have not explained yet.

@deanmarcussen Here are steps of how reproduce this issue (assuming you already have a default tenant with SaaS recipe)

Create a new tenant (use SQL Server) database NOT SQLite. and Using Blog recipe
Try setting up the tenant
During the setup "half way there" I think during installing the Roles module, you'll encounter an InvalidOperationException exception thrown by RoleUpdater

Here is the exception

OrchardCore.Environment.Shell.ShellFeaturesManager|ERROR|IFeatureEventHandler thrown from OrchardCore.Roles.Services.RoleUpdater by InvalidOperationException System.InvalidOperationException: The connection does not support MultipleActiveResultSets.
   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 ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
   at YesSql.Store.ProduceAsync[T,TState](WorkerQueryKey key, Func`2 work, TState state)
   at YesSql.Services.DefaultQuery.Query`1.FirstOrDefaultImpl()
   at YesSql.Services.DefaultQuery.Query`1.FirstOrDefaultImpl()
   at OrchardCore.Data.Documents.DocumentStore.GetOrCreateMutableAsync[T](Func`1 factoryAsync) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore\OrchardCore.Data.YesSql\Documents\DocumentStore.cs:line 38
   at OrchardCore.Documents.DocumentManager`1.GetOrCreateMutableAsync(Func`1 factoryAsync) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore\OrchardCore.Infrastructure\Documents\DocumentManager.cs:line 71
   at OrchardCore.Roles.Services.RoleUpdater.UpdateRolesForInstalledFeatureAsync(IFeatureInfo feature) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore.Modules\OrchardCore.Roles\Services\RoleUpdater.cs:line 65
   at OrchardCore.Modules.InvokeExtensions.InvokeAsync[TEvents,T1](IEnumerable`1 events, Func`3 dispatch, T1 arg1, ILogger logger) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore\OrchardCore.Abstractions\Modules\Extensions\InvokeExtensions.cs:line 133    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 ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
   at YesSql.Store.ProduceAsync[T,TState](WorkerQueryKey key, Func`2 work, TState state)
   at YesSql.Services.DefaultQuery.Query`1.FirstOrDefaultImpl()
   at YesSql.Services.DefaultQuery.Query`1.FirstOrDefaultImpl()
   at OrchardCore.Data.Documents.DocumentStore.GetOrCreateMutableAsync[T](Func`1 factoryAsync) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore\OrchardCore.Data.YesSql\Documents\DocumentStore.cs:line 38
   at OrchardCore.Documents.DocumentManager`1.GetOrCreateMutableAsync(Func`1 factoryAsync) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore\OrchardCore.Infrastructure\Documents\DocumentManager.cs:line 71
   at OrchardCore.Roles.Services.RoleUpdater.UpdateRolesForInstalledFeatureAsync(IFeatureInfo feature) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore.Modules\OrchardCore.Roles\Services\RoleUpdater.cs:line 65
   at OrchardCore.Modules.InvokeExtensions.InvokeAsync[TEvents,T1](IEnumerable`1 events, Func`3 dispatch, T1 arg1, ILogger logger) in C:\Code\OrchardCMS\OrchardCore\src\OrchardCore\OrchardCore.Abstractions\Modules\Extensions\InvokeExtensions.cs:line 133

The RoleUpdater class does a look up in the database of a RoleDocument here which causes the exception.

Answer 14 · 2024-04-29T21:57:12.000Z

I was about to close this today, actually, because we haven't found any issues with 1.8. Are you saying that there's something else apart from #15794 and #15628?

Answer 15 · 2024-04-29T22:06:36.000Z

@Piedone I think solving #15794 may solve this issue too "but not sure". This ticket is about whether or not we should use MultipleActiveResultSets. Since MultipleActiveResultSets is disabled by default in Sql Server, I think we should not rely on enabling it for OC to run.

In my last comments, I showed by "as of now" this must be turned on otherwise we have a problem.

@deanmarcussen stated the following

Everytime this has been reported (you can search the issues) it has been a missing await or an async void usage, or an odd combination of .Result usage

I think there is a real problem that we should look at here and ensure that MultipleActiveResultSets is not needed

Answer 16 · 2024-04-29T22:25:35.000Z

I'll keep an eye out for it but I think this issue is invalid. In 1.8 we don't have any problems. In main we do, but those are tracked separately already with more specific issues too.

Answer 17 · 2024-04-29T22:29:25.000Z

Probably the key takeaway here, is that YesSql is not threadsafe.

When you get an error message from sql that MultipleActiveResultSets must be enabled, it has tended to be, that something is trying to use YesSql in a non threadsafe manner.

I can't comment between the different versions of orchard involved, as we are currently on 1.6 and will remain there quite happily for some time.

Good luck, never an easy issue to track down.

Answer 18 · 2024-04-30T00:18:17.000Z

Yeah, that's what we figured under the related issues. Let's close this and focus on the two other specific issues.