007 2024-10-29

I was pleased to discover Rails 8 would be using SQLite and Solid Queue to replace Redis and a job queue such as Resque.

This caught my attention because in 2017, I wrote a blog post on my company blog about using SQLite with a Queue. However my solution was radically different from the one implemented in Rails 8, so that's what I'll discuss below.

The busy_handler

For starters, I'm sure we've all seen this error:

database is locked (SQLite3::BusyException)

You can reproduce it easily by setting your sqlite3 database timeout to 1, like this:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 1

Relaunch Solid Queue and the error will surface rather quickly. The reason for this is due to multiple writers on the database.

From what I understand, the solution implemented in the current beta version of Rails 8 is to use a custom busy handler, written in Ruby, which performs some retries with a simple polling algorithm (instead of backoff) defined by a fixed amount of time. If the retries keeps failing, it will eventually timeout once it surpasses the timeout valued (in milliseconds) shown in the configuration pasted above. The busy handler is included in the sqlite3-ruby gem and activerecord-enhancedsqlite3-adapter.

It works, but it naturally doesn't work when the timeout is low, because you're dealing with locks and multiple writers.

Concurrency through simplicity

I find the current solution less than optimal because the underlying problem is still there. Even with a timeout set to 5000 (milliseconds), the database is locked error can still resurface if there's a deadlock. The end result are at least two writes failing. Even worse, your query process time becomes non-deterministic.

Back in 2017, I realized the flaw and applied a simpler technique which completely solves this entire race of problems: single writer on the SQLite database.

If you can guarantee only one thread will ever write to the database, I can guarantee it will never lock.

Here's what you need:

• 1 queue dedicated to db_writes
• 1 job which performs DB writes
• 1 worker process with 1 thread, which reads from the db_writes queue and runs the job

We're essentially serializing the concurrent writes by using db_writes as a FIFO queue.

On the surface, one might think this is slower because there's only a single process with a single thread doing the work. But even on a heavily loaded site, there will be a significantly lower number of writes than reads. A single process can handle all of them without issue or delay, and I believe scaling vertically can solve performance issues introduced by such serialization.

Even the author of the above busy handler wrote about Scaling SQLite writes in 2023, and came to the same conclusion as I did back in 2017 (although I didn't provide the numbers to back my claims haha).

But the queue uses SQLite

Indeed, Solid Queue uses an SQLite database to write and fetch job information. One solution is to also ensure Solid Queue only has one writer per database. This would require some interesting coordination in the Solid Queue code, which I'm currently looking into. When I first implemented this, I used Mosquitto with purely in-memory queues. I didn't need to worry about the queue's internals.

In Solid Queue, this can possibly be replicated using SQLite's :memory: database, by re-creating the production_queue tables when the process is launched. Unfortunately I can't vouch for it since I've never used it in production. Naturally, while using :memory: you lose all persistence until you manually dump or export the table data from within the same process...

So the next question is: how important is persistence in regards to the job queue? With some thoughtful design, the need to persist the queue could possibly be eliminated, or perhaps deferred to a recurring task (ex: every minute). It's added to my list of things to look into.

Writes are now asynchronous

Another thing to consider with this approach: the database writes effectively become asynchronous. It's not for everyone and clearly doesn't work well in all applications. However if your application is already designed to split reads and writes across separate clusters, as I wrote about on my blog in 2009, then this could work well for you.

Final notes

I only started playing with Rails 8 and Solid Queue about two days ago, so I haven't yet had a chance to fully grok the finer details of how they work together. Forgive me if I missed something important.

As a final note, I am quite happy to see Rails going in the direction of being standalone without requiring so many external dependencies. This will be good for everyone and make deployment much easier and cheaper for those opting-in to this new strategy.

https://blog.a1w.ca/p/2024-10-29-sqlite-database-is-locked-rails-8
2024-10-31 12:00:00 UTC