sqlite: database is locked

[ronag]

Multiprocess use of sqlite database sometimes fails with "database is locked"

We are using https://github.com/nxtedition/nxt-undici/blob/main/lib/sqlite-cache-store.js but have the same problem with https://github.com/nodejs/undici/blob/main/lib/cache/sqlite-cache-store.js.

Basically we have a cluster nodejs service which is using the undici http cache. The queries are not super complicated or doing anything exotic.

It's unclear when/how/why this occurs and what to do about it. Might be a normal thing but in that case we should document it.

Refs: nodejs/undici#4124

    this.#getValuesQuery = this.#db.prepare(`
      SELECT
        id,
        body,
        deleteAt,
        statusCode,
        statusMessage,
        headers,
        etag,
        cacheControlDirectives,
        vary,
        cachedAt,
        staleAt
      FROM cacheInterceptorV${VERSION}
      WHERE
        url = ?
        AND method = ?
        AND start <= ?
      ORDER BY
        deleteAt ASC
    `)

    this.#insertValueQuery = this.#db.prepare(`
      INSERT INTO cacheInterceptorV${VERSION} (
        url,
        method,
        body,
        start,
        end,
        deleteAt,
        statusCode,
        statusMessage,
        headers,
        etag,
        cacheControlDirectives,
        vary,
        cachedAt,
        staleAt
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `)

    this.#deleteExpiredValuesQuery = this.#db.prepare(
      `DELETE FROM cacheInterceptorV${VERSION} WHERE deleteAt <= ?`,
    )

[cjihrig]

This is not a bug. This is just how SQLite works. Implementing #57597 may mitigate the issue.

[ronag]

Would be nice to have it explained somewhere?

Also throwing an error seems like a pretty slow way to indiciate busy/tryagain.

[ronag]

@cjihrig On a separate note. How does our localStorage implementation handle this? Does it retry?

[cjihrig]

https://github.com/nodejs/node/blob/main/src/node_webstorage.cc#L110

[cjihrig]

Closing as there is nothing else to do here. Users can leverage PRAGMA busy_timeout already, and #57752 will add an option to the API for this.

[ronag]

Would it make sense to change the api to return a Boolean rather than creating and throwing an error for this case?

[cjihrig]

Does any other SQLite library do that? I'm also not sure how that would work with APIs that already return a value.

[cjihrig]

I guess another question is, have you tried leveraging the busy timeout at all? It's not the same thing as a timeout in JavaScript for example.

[ronag]

I actually think a low value is good as I don't want to block the main thread. This would give a good way to yield.