Avoiding SQLite BUSY errors with worker jobs with Dream

Ecosystem
1

I’m working on building a webserver using Dream, with SQLite as the underlying DB for user data (both for storing the session information, and business logic).

Things have mostly been going quite well, but recently I have run into a situation where I’ve started to get BUSY write errors.

In particular, things started going wrong when I added a background worker to handle certain tasks independently, without immediate prompt from a user’s request:

  • Example, the user requests a reminder for an event, the task gets added to the queue of the worker, and then at a later date, the worker executes the task and then sends a reminder to the user)…

The initialization code for the worker is roughly as follows:

  Lwt.async @@ fun () ->
    let+ db =
      config
      |> Configuration.Params.database_path
      |> Uri.of_string
      |> Caqti_lwt.connect in
    let db = Result.get_exn db in
    worker db config

where the database_path is the same database_path that is provided to dream. I use Lwt.async here to allow the worker to operate independently rather than being prompted from a user input.

I understand that SQLite itself isn’t at fault here - it explicitly states in the documentation that it doesn’t provide any particular support for concurrent requests, and may return BUSY if used in such a setting, but seeing as I’ve seen so many projects (in Go for example), that have a similar architecture as mine, but don’t have this error, I wonder if I’m doing something wrong on the OCaml side.

In particular, I assume the issue is that I should instead be requesting a database connection from Dream’s SQL connection pool instead of creating my own, but I’m not sure how to do that.

Has anyone been working on something similar/run into this problem with Dream and SQLite?

2

I’m afraid I can’t help with dream but from an SQLite perspective you should set your database in wal mode, set a reasonable busy timeout value on your connections and start your write transactions with BEGIN IMMEDIATE.

Here’s sample code you should be able to apply mutatis mutandis to the db abstraction you are using.

Also if you are serving over http you could devise a combinator that sends back a 503 with a retry-after when a busy-timeout error pop-ups.

5 Likes
3

Creating a non-pooled connection for a background worker makes sense (at least as far as Caqti is concerned).

For the reference, the busy timeout can currently only be set using a PRAGMA (cf. Setting Busy timeout for Sqlite3 Connections · Issue #93 · paurkedal/ocaml-caqti · GitHub).

1 Like