SQLite error bind on a busy prepared statement about couchbase-lite-net HOT 11 OPEN

Hi !
We still have the same issue on production server ... Any news on this ?

2023-8-21 06:21:21.609+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT lastSeq FROM kvmeta WHERE name=?]
2023-8-21 06:21:21.609+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 88783 of [df5c253c0b]
2023-8-21 06:21:21.609+00:00 [.NET ThreadPool Worker]| ERROR) [Database] bad parameter or other API misuse (21/21)
[18:21:21 ERR] [Grpc.AspNetCore.Server.ServerCallHandler] Error when executing service method 'Get'.
Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 21): bad parameter or other API misuse.
at LiteCore.Interop.NativeHandler.ThrowOrHandle()
at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block)
at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a)
at Couchbase.Lite.Internal.Query.NQuery.Execute()

from couchbase-lite-net.

Hmmmm, without a reproduction I doubt this will be tracked down unfortunately. Do you have any information on when this happens? It's basically saying that at some point there was a misuse of SQLite by us when making a query, but the query and particular set of data that caused this are unknown from this ticket.

from couchbase-lite-net.

The query is build like that :

var fullQuery =
    $@"
SELECT
    {string.Join(",\n", selects)}
FROM
    {from}
WHERE
    {string.Join(" AND \n", wheres)}
;";

var query = _db.CreateQuery(fullQuery);
var result = query.Execute().AllResults();    

I'm adding more logs to give you all the queries but the issue seems related to concurrency problem not database schema or stuff like that. It appears when there is more than 10 requests done at the same time on the gRPC server. I'll take some time to create a repro if it's needed to be taken seriously.

from couchbase-lite-net.

If this is being done concurrently, are you creating a DB handle per thread? That would be my recommended way of doing it. In general mutable objects like that are not guaranteed to be thread safe like the immutable ones are.

from couchbase-lite-net.

2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT purgeCnt FROM kvmeta WHERE name=?]
2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 88783 of [df5c253c0b]
2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT purgeCnt FROM kvmeta WHERE name=?]
2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 88783 of [df5c253c0b]
2023-9-3 03:42:56.668+00:00 [.NET ThreadPool Worker]| ERROR) [Database] another row available (21/100)
2023-9-3 03:42:56.668+00:00 [.NET ThreadPool Worker]| ERROR) [Database] no more rows available (21/0)
[15:42:56 ERR] [Monitoring.Context.Core.Repositories.DeviceWorkflowRepository] An error occured while retrieving data from the database using query
SELECT
workflows.Id,
workflows.Label,
workflows.DeviceId,
devices.Label AS DeviceLabel
FROM

            _ AS workflows
            LEFT OUTER JOIN _ AS devices
            ON (
                devices.Id = workflows.DeviceId AND
                devices.Type = "device"
            )
        
        WHERE
            workflows.Type = "workflow" AND 

(
workflows.Disabled = FALSE OR
workflows.Disabled IS NOT VALUED
) AND
workflows.DeviceId IN ('09ed24b2-c219-4be5-b11b-098842f8e3a0')
;
Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 100): another row available.
at LiteCore.Interop.NativeHandler.ThrowOrHandle()
at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block)
at LiteCore.LiteCoreBridge.Check(C4TryLogicDelegate2 block)
at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a)
at Couchbase.Lite.Internal.Query.NQuery.Execute()
at Monitoring.Context.Core.BaseCouchbaseRepository1.GetManyEntities[TInfos](IEnumerable1 selects, String from, IEnumerable1 wheres) in /app/src/context/core/Monitoring.Context.Core/Tools/BaseCouchbaseRepository.cs:line 65 [15:42:56 ERR] [Grpc.AspNetCore.Server.ServerCallHandler] Error when executing service method 'GetMany'. Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 100): another row available. at LiteCore.Interop.NativeHandler.ThrowOrHandle() at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block) at LiteCore.LiteCoreBridge.Check(C4TryLogicDelegate2 block) at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a) at Couchbase.Lite.Internal.Query.NQuery.Execute() at Monitoring.Context.Core.BaseCouchbaseRepository1.GetManyEntities[TInfos](IEnumerable1 selects, String from, IEnumerable1 wheres) in /app/src/context/core/Monitoring.Context.Core/Tools/BaseCouchbaseRepository.cs:line 65
at Monitoring.Context.Core.Repositories.DeviceWorkflowRepository.GetMany(DeviceWorkflowsFilter filter) in /app/src/context/core/Monitoring.Context.Core/Repositories/App/Workflows/DeviceWorkflows/DeviceWorkflowRepository.cs:line 157
at Monitoring.Context.Server.DeviceWorkflowService.GetMany(DeviceWorkflowFilterRequest request, ServerCallContext context) in /app/src/context/core/Monitoring.Context.Server/Services/App/Workflows/DeviceWorkflows/DeviceWorkflowService.cs:line 53
at Grpc.Shared.Server.UnaryServerMethodInvoker3.AwaitInvoker(Task1 invokerTask, GrpcActivatorHandle1 serviceHandle) at Grpc.Shared.Server.UnaryServerMethodInvoker3.AwaitInvoker(Task1 invokerTask, GrpcActivatorHandle1 serviceHandle)
at Grpc.AspNetCore.Server.Internal.CallHandlers.UnaryServerCallHandler3.HandleCallAsyncCore(HttpContext httpContext, HttpContextServerCallContext serverCallContext) at Grpc.AspNetCore.Server.Internal.CallHandlers.ServerCallHandlerBase3.g__AwaitHandleCall|8_0(HttpContextServerCallContext serverCallContext, Method`2 method, Task handleCall)

I'm resolving Database instance using Microsoft Dependency Injection with a singleton lifetime.
Do you think that it will it be better to have scopped instance ? How does the sqlite will handle multi concurrency access ?

from couchbase-lite-net.

Somehow I missed this latest update it looks like. SQLite handles concurrency with locks but that is just to prevent actual corruption and not to address busy and/or race conditions. As for our library we recommend not sharing these instances between threads. One handle per thread is the recommended model when it comes to stuff like this.

from couchbase-lite-net.

What is "one handle" ? One Database instance per thread ? So scopped lifetime instance for ASP.Net Core projet ? What will happen if I have 2 databases that are updating the same document on multiple thread ?
I have a Replicator using this singleton instance too. How does it works if I have scopped Database instance ?

from couchbase-lite-net.

Again sorry for not responding, I'd been out on leave for a while but if you use one database instance per thread, the SQLite locking will prevent them from doing things at the same time and probably prevent SQLite errors like this. The replicator has its own instance (when you create it the first thing it does is open a new instance for itself to the same data file).

Having a replicator be a singleton is probably slightly less problematic, but still it can probably get hairy if you are calling start and stop from different threads as you will race and such. But since the replicator has its own db instance that it uses, the situation with scoped databases and the replicator is the same story as right now: They are separate instances doing their own thing and they are locked out from doing the same things at the same time (simplification).

from couchbase-lite-net.

Well, I finally managed to prevent concurrency issues by disabling concurrent request on my backend

services.AddRateLimiter(limiterOptions => limiterOptions
    .AddConcurrencyLimiter(policyName: DEFAULT, options =>
    {
        options.PermitLimit = 1;
        options.QueueProcessingOrder = QueueProcessingOrder.OldestFirst;
        options.QueueLimit = 1000;
    }));

For my use case it's enough but I'll definitively try to create scoped instances (per HTTP request = thread) of Database object to see if it's working in case of high concurrency scenario.

from couchbase-lite-net.

You might gain some performance by doing scoped objects, but if that's not an issue for you this works just as well.

from couchbase-lite-net.