OrchardCore: SQLite "database table is locked" when IDocumentManager and ISession both flush during recipe execution

[weirdyang]

I have a custom IRecipeStepHandler that:

Saves a YesSql document via _session.SaveAsync(detail)
Updates a cached document via _documentManager.UpdateAsync(doc)

Both calls succeed without error. The exception occurs later, when the shell scope disposes and flushes both writes — the IDocumentManager update and the ISession save both try to commit, causing a SQLite lock conflict.

SQLite Error 6: 'database table is locked'
at YesSql.Commands.UpdateDocumentCommand.ExecuteAsync
at YesSql.Session.FlushInternalAsync
at YesSql.Session.SaveChangesAsync

Function that updates

        public async Task<DeploymentVersionDetail> AddOrUpdateVersionAsync(
            string versionId,
            string importedBy,
            List<DeploymentVersionWorkflowEntry> workflows,
            List<ArchivedMediaEntry> archivedMedia)
        {
            DeploymentVersionDocument doc = await _documentManager.GetOrCreateMutableAsync();
            DeploymentVersionSummary existingSummary = doc.Versions.FirstOrDefault(v => v.VersionId == versionId);

            DeploymentVersionDetail detail;

            if (existingSummary is not null)
            {
                // Update existing — merge in new data
                detail = await _session.GetAsync<DeploymentVersionDetail>(existingSummary.DocumentId);
                detail.Workflows.AddRange(workflows);
                detail.ArchivedMedia.AddRange(archivedMedia);

                await _session.SaveAsync(detail);

                existingSummary.WorkflowCount = detail.Workflows.Count;
                existingSummary.ArchivedMediaCount = detail.ArchivedMedia.Count;
            }
            else
            {
                // Create new
                detail = new DeploymentVersionDetail
                {
                    VersionId = versionId,
                    ImportedUtc = DateTime.UtcNow,
                    ImportedBy = importedBy,
                    Workflows = workflows,
                    ArchivedMedia = archivedMedia
                };

                await _session.SaveAsync(detail);
                

                doc.Versions.Add(new DeploymentVersionSummary
                {
                    DocumentId = detail.Id,
                    VersionId = versionId,
                    ImportedUtc = detail.ImportedUtc,
                    ImportedBy = importedBy,
                    WorkflowCount = workflows.Count,
                    ArchivedMediaCount = archivedMedia.Count
                });
            }

            await _documentManager.UpdateAsync(doc);

            return detail;
        }
    }

The issue arises only after it enters the if block (i.e. another step is executed). If import a recipe with a single step (workflowtype) it works ok.

--- End of stack trace from previous location ---
 at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 662
 at YesSql.Commands.UpdateDocumentCommand.ExecuteAsync(DbConnection connection, DbTransaction transaction, ISqlDialect dialect, ILogger logger)
 at YesSql.Session.FlushInternalAsync(Boolean saving)
 at YesSql.Session.FlushInternalAsync(Boolean saving)
 at YesSql.Session.SaveChangesAsync()
 at YesSql.Session.SaveChangesAsync()
 at OrchardCore.Data.Documents.DocumentStore.CommitAsync()
 at OrchardCore.Environment.Shell.Scope.ShellScope.BeforeDisposeAsync()
 at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell)
 at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell)
 at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteStepAsync(RecipeExecutionContext recipeStep)
 at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, IDictionary`2 environment, CancellationToken cancellationToken)
 --- End of inner exception stack trace ---
 at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, IDictionary`2 environment, CancellationToken cancellationToken)

Question: What's the correct pattern for updating both a YesSql document and an IDocumentManager document within a recipe step? Should I use ShellScope.Current.DeferredTask to defer the document manager update to after the recipe transaction completes?

[hishamco]

I think it's related to or a duplicate of

https://github.com/OrchardCMS/OrchardCore/issues?q=is%3Aissue%20state%3Aopen%20database%20table%20is%20locked

[weirdyang]

thanks. I resolved it by using ISession instead of document manager. Not ideal since I can't cache the document, but at least it solves the issue.