sqlite: database is locked

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 <= ?`,
    )

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

Would be nice to have it explained somewhere?

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

@cjihrig

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

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

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.

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

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

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.

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.

Navigation Menu

Repository navigation

sqlite: database is locked #57741

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

sqlite: database is locked #57741

Description

Activity

cjihrig commented on Apr 4, 2025

ronag commented on Apr 4, 2025

ronag commented on Apr 4, 2025

cjihrig commented on Apr 4, 2025

cjihrig commented on Apr 5, 2025

ronag commented on Apr 5, 2025

cjihrig commented on Apr 5, 2025

cjihrig commented on Apr 5, 2025

ronag commented on Apr 5, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions