Orchardcore: SQLite Error 6: 'database table is locked: ContentItemIndex

[protoncm]

Hello Orchard Core Team

Currently I using dev branch (c7401d3 ) of Orchard Core. i have encounter on this error when i tried to save my content item.

here is the error.

Microsoft.Data.Sqlite.SqliteException
  HResult=0x80004005
  Message=SQLite Error 6: 'database table is locked: ContentItemIndex'.
  Source=Microsoft.Data.Sqlite
  StackTrace:
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.<ExecuteDbDataReaderAsync>d__60.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 468
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at OrchardCore.Queries.Sql.SqlQuerySource.<ExecuteQueryAsync>d__7.MoveNext() in LOCAL_PATH\OrchardCore.Modules\OrchardCore.Queries\Sql\SqlQuerySource.cs:line 82

What i have done before.

I create my own content type call _LiquidPage_ with _TitlePart_ and _LiquidPart_.
I also have a SQL Query to get all another content items call AllMembers.

To reproduce this error

1. Create a new _LiquidPage_ content item.
2. Input {% assign allMembers = Queries['AllMembers'] | query %} in liquid body.
3. Save.

The error will occur while you saving but your input will saved.

I think this error is about query in liquid template while rendering template to html string.

[PiemP]

The liquid code seems wrong. It would be:

{% assign allMembers = Queries.AllMembers | query %}

The error stack trace talk about a problem with your SQLite database...seems something is crashed before and keep locked one table.

Have you tried to find some solution to unlock SQLite on google? It could be a problem not bounded to OrchardCore

[protoncm]

The liquid code seems wrong. It would be:

{% assign allMembers = Queries.AllMembers | query %}

Yes but i think we can do it like array as well.

The error stack trace talk about a problem with your SQLite database...seems something is crashed before and keep locked one table.

Have you tried to find some solution to unlock SQLite on google? It could be a problem not bounded to OrchardCore

Its clean database after created new Blank Site recipe. And I tried to google it but sadly it didn't help.

[PiemP]

I can't repro your issue with Lucene query and clean installation (I'm on the last dev source code).

Can you give me your SQL query? And a little explaination on what you want to do to try to repro your issue?

[protoncm]

Reproduce Steps

1. Create a LiquidPage content type with
2. TitlePart
3. LiquidPart
4. Create a Member content type with TitlePart
5. Create 2 members with random title.
6. Create SQL Query named AllMembers with following syntax

select DocumentId from ContentItemIndex
where Published = true and ContentType = 'Member' 

1. Create a Liquid Page with liquid data input

 {% assign allMembers = Queries['AllMembers'] | query %}

1. Save

[jtkech]

Okay, i could "repro".

It works fine with a lucene query, then with a sql query it doesn't fail (but it is very long) because a try catch has been added in GetContentItemAspectAsync() (i'm on the last dev branch) that is called by the content manager on publishing when the indexing feature is enabled. But the catched exception is exactly the same. If i disable the indexing feature it works.

Maybe because we are executing a query while saving the content item, i will take a look

Note: In the settings of the content type, if you check Include body parts, if i recall correctly GetContentItemAspectAsync() is called one more time.

[jtkech]

So i think (but not sure) that after having done a session save, because of the auto flush of yessql (when doing another session query), some sqlite commands have started and need to be commited before being able to execute our nested SqlQuery using another connection (explicitly created and opened).

Note: It looks like the other nested transaction issue we already had with nested workflows. Indeed if i add a session commit before executing the SqlQuery it works.

Hmm, because the indexing work (e.g on publishing) uses the body aspects of items that may trigger a SqlQuery, one solution would be to do this indexing work in a deferred task if possible, so in another non nested scope, after the one saving the item has been committed. @Skrypt is working on lucene indexing, i will see with him as i have time what we could do.

Meanwhile, if you still need the indexing feature, you would have to rather use your SqlQuery in a liquid view template / liquid template through the admin, but not in a liquid part.

Update another workaround is to not use a LiquiPart. Just tried by adding to the liquid page type a simple multiline html field (that supports liquid) that i named MyBody and in which i put your liquid query, then it works because the query is not executed when saving + indexing the item.

So okay with a field but the problem is not specific to the LiquidPart if i put the same liquid SqlQuery in an HtmlPart => same problem.

[jtkech]

But, because the nested SqlQuery is a read normally it should work, so, in SqlQuerySource just tried to wrap the query in an using (var transaction ...) with the configured isolation (ReadUncommitted).

So, with the following it works, i will do a PR to be reviewed by @sebastienros.

using (connection)
{
    await connection.OpenAsync();

    using (var transaction = connection.BeginTransaction(_store.Configuration.IsolationLevel))
    {
        documentIds = await connection.QueryAsync<int>(rawQuery, parameters);
    }
}