database is locked

[ronag]

Error: database is locked
    at get size (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/sqlite-cache-store.js:298:47)
    at #prune (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/sqlite-cache-store.js:270:14)
    at SqliteCacheStore.set (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/sqlite-cache-store.js:238:16)
    at CacheHandler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/interceptor/cache.js:159:19)
    at Handler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/utils.js:263:40)
    at Handler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/utils.js:263:40)
    at Handler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/interceptor/response-retry.js:202:20)
    at Handler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/utils.js:263:40)
    at Handler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/interceptor/dns.js:22:11)
    at Handler.onComplete (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/utils.js:263:40)

Something with the SQL database? I guess prune should at least catch and ignore the error?

[ronag]

Not sure when/how this can occur.

[mcollina]

Might it be possible that you are running multiple servers connecting to the same SQLite instance? See https://www2.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked for examples.

[ronag]

Isn't the idea that the sql cache should work cross process? We should probably handle errors like these?

[mcollina]

Isn't the idea that the sql cache should work cross process? We should probably handle errors like these?

Yes! 100%. However I'm not really sure how to handle this (minus literally ignoring the error)

[ronag]

I think this specific error can probably be ignored... though I'm not sure how/when this can otherwise also occur?

I'm still confused by this error in general. I thought sqlite was designed not to have this issue?

[mcollina]

I thought sqlite was designed not to have this issue?

Unfortunately no, you can have this issue in SQLite as the link above describe. However, this shouldn't really be possible with how we are using SQLite in the cache store.

[ronag]

However, this shouldn't really be possible with how we are using SQLite in the cache store.

Question is how I manage to trigger this then :D. It was in production so I don't have a repro. Will try to find a way to get that somehow...

[metcoder95]

In theory, it shouldn't be an issue, but it could be an overlap between writes and reads? (attempting to read to the table while other request handling attempts to write within the same connection).

We are not safe to such conditions in the current implementation (as well it is just a rough bet)

[mcollina]

There can be an overlap between write and reads only if two happen in parallel, but this shouldn't be possible because the SQLite docs clearly states that the locked status happens on the same connection. The usual case for that error is:

• begin a transaction
• do something async
• ... in the meanwhile, something else try to access the same table/rows etc.

[ronag]

I'm getting a lot of these errors in production.

[ronag]

It's usually #findValue or get size()

Error: database is locked     at get size (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/sqlite-cache-store.js:298:47)     at #prune (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/sqlite-cache-store.js:270:14)     at SqliteCacheStore.set (file:///usr/src/app/node_modules/@nxtedition/nxt-undici/lib/sqlite-cache-store.js:238:16)
--

[ronag]

I suspect our pruning logic is causing contention which makes sqlite timeout certain queries which leads to "database is locked" errors.

I'm going to avoid using maxEntryCount in production.

[metcoder95]

Seeing the commit you added @ronag I'm wondering if we should better change the way we prune; currently it is using a setInterval with an arbitrary 10s value.

What about pruning upon read (if time expired) and offer the chance the flag to customize it (along with maxEntryCount), as well offer returning expired records before pruning.

[ronag]

I don't have any good ideas unfortunately. We've added a max entry age option to our cache interceptor (i.e. make sure deleteAt is always withing a week or so) and do deleteExpiredValuesQuery on every set. That seems to work well. Anything related to counting seems to be super slow and problematic.

Here is out "optimized" version: https://github.com/nxtedition/nxt-undici/blob/main/lib/sqlite-cache-store.js

Basically we have removed:

• updateValueQuery
• deleteOldValuesQuery
• countEntriesQuery

Which we see as problematic.

[metcoder95]

I don't have any good ideas unfortunately. We've added a max entry age option to our cache interceptor (i.e. make sure deleteAt is always withing a week or so) and do deleteExpiredValuesQuery on every set. That seems to work well.

Yeah, this seems to match what I had in mind; let me see if I can find some time next week to work on this.

[ronag]

So from what I've understood this is normal behavior and we should either yield and try again or just ignore the error. I would recommend the latter.

We can reduce the occurence by making the database operations as efficient as possible.