SQLite error bind on a busy prepared statement #1552
Comments
|
Hi !
|
|
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. |
|
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. |
|
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. |
I'm resolving Database instance using Microsoft Dependency Injection with a singleton lifetime. |
|
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. |
|
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 ? |
|
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). |
|
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. |
|
You might gain some performance by doing scoped objects, but if that's not an issue for you this works just as well. |
|
Regarding @borrrden 's comment above:
That isn't relevant at this level. Couchbase Lite has its own locking to make concurrent access safe. The sorts of errors in this bug report look like they'd be caused by bugs in our code.
This is for performance reasons, not correctness. Using one Database instance on multiple threads won't be any faster than using it on a single thread, because there are mutexes -- our own and SQLite's -- preventing any concurrency. Whereas if you use a different instance on each thread they can all read the database simultaneously. (But two writes cannot be concurrent; that's an architectural limitation of SQLite.) |
Library Version
Version 3.0.0
.NET Runtime
dotnet 7.0
Operating System / Device Details
mcr.microsoft.com/dotnet/aspnet:7.0
Log Output
Expected behavior
No error.
Steps To Reproduce
Unfortunately it seems to appear since we use dotnet 7 and version 3.0 of couchbase lite. It randomly appears and may cause the application to crash completely after some time.
The text was updated successfully, but these errors were encountered: