SQLite: claiming a job fails with SQLITE_BUSY when another connection writes #32

Hey! 👋

We are moving an AdonisJS app's mail to @adonisjs/queue on SQLite, on a single machine. While testing the documented setup, a web process dispatching and a queue:work worker claiming on the same database file, the worker's claim sometimes failed with SQLITE_BUSY.

What's happening

popFrom claims a job in a transaction that reads the job, then updates it (knex_adapter.ts#L156-L183, and the same in kysely_adapter.ts#L176-L209). On SQLite that transaction starts as a reader. When another connection commits between the SELECT and the UPDATE, SQLite cannot upgrade it and fails the UPDATE at once with SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT in WAL mode). busy_timeout does not help there, since SQLite skips the busy handler when waiting could deadlock.

The comment on #supportsSkipLocked says SQLite is "single-writer so it doesn't need it" (L207). That holds for one connection, but a web process and a worker are two connections to the same file. upsertSchedule already avoids this with a single statement, for the reason its comment gives (L776-L779).

Reproduction

Two failing tests, next to the existing two-connection tests: muco-rolle@aa07e0a

They race a dispatch on one connection with claims on two connections, for both the Knex and the Kysely adapters, and fail with:

SqliteError: update `queue_jobs` set `status` = 'active', ... where `id` = 'job-0' and `queue` = 'default' and `status` = 'pending' - database is locked

With two processes instead (one dispatching 3,000 jobs, one running a Worker, @boringnode/queue 0.8.0 and better-sqlite3), the claim failed once in each of 8 runs, and each failure stalls the worker for the 5 s error delay.

Possible fix

On SQLite, claim the job with a single UPDATE ... WHERE id = (SELECT id ... LIMIT 1) RETURNING data, as upsertSchedule does. The statement takes the write lock itself, so it waits through busy_timeout. With that change both tests pass, the two-process runs had no failures, and the full suite passes apart from one PostgreSQL date test that fails the same way on main on my machine.

#processDelayedJobs, recoverStalledJobs and claimDueSchedule read then write in a transaction the same way. I have not seen them fail, and they compute values in JavaScript between the read and the write, so I left them out.

The root cause on the Knex side is reported in knex/knex#6542 (Knex always starts SQLite transactions with a deferred BEGIN).

Happy to send a PR for the claim.