Avoiding SQLite Database Locks in Production | Bendangelo Blog
Avoiding SQLite Database Locks in Production
Jul 4, 2026
5 minute read
sqlite
ruby
rails
This is a Rails stack, using Solid Queue, and SQLite
My SQLite configuration
SQLite misconfiguration in deploy.yml
Recurring Jobs
Avoid Batching / Find Each in Jobs
Opencode skill to fix this
Post your SQLite tips
While working on Wenmar Pro, a POS for auto shops, I’ve encountered quite a few SQLite3::BusyException exceptions and it’s taken me months to figure out the cause. This happens because sqlite is single writer, when one process holds the lock, any other process must wait for it to be released, but often it’s not in time and thus throws this exception. The cause can be hard to track down, so I’ll share what I have learned so far.
It comes down to:
Better error tracking (I personally use AppSignal for it’s good Rails integration)
SQLite configuration
Background job recurring jobs
Optimize SQLite batch jobs
This is a Rails stack, using Solid Queue, and SQLite
For some context, I started this app 3 or so months ago and I felt I had nothing to lose by trying a new db. I’ve heard many good things about SQLite and it’s advancements in performance, esp for Rails. So I decided to give it a shot. Otherwise I would use Postgresql. A big factor was not having to manage an other process and because this software has a low amount of users, I felt this could be a good fit. As well as, worst case a migration from sqlite to postgres is fairly simple. So I took a chance.
So far it’s been great but it’s become clear you need to spend a little more time optimizing for sqlite. It’s single writer locking needs to be taken into account, and a lot of AI agents do not. I should note, I am keeping my eyes on Turso, it’s compatible with sqlite but offers multiple writers, which is awesome but it has no rails driver atm.
My SQLite configuration
I’m using rails and I’ll just share my configuration. One important thing is, the busy_timeout and timeout values should be the same. If you’re getting a lot of random lock exceptions trying making them the same value.
Other notes:
wal should be on.
the other custom values were suggested additions, cannot confirm if they help.
For testing:
busy_timeout is higher to help with rails test parallel testing
other values are for quicker db writing, which does help
default_pragmas: &default_pragmas<br>journal_mode: wal<br>synchronous: normal<br>temp_store: memory<br>mmap_size: 134217728<br>cache_size: -20000<br>busy_timeout: 5000<br>wal_autocheckpoint: 10000
default: &default<br>adapter: sqlite3<br>max_connections:<br>timeout: 5000<br>pragmas:<br>: *default_pragmas
development:<br>primary:<br>: *default<br>database: storage/development.sqlite3<br>queue:<br>: *default<br>database: storage/development_queue.sqlite3<br>migrations_paths: db/queue_migrate<br>cable:<br>: *default<br>database: storage/development_cable.sqlite3<br>migrations_paths: db/cable_migrate
# Warning: The database defined as "test" will be erased and<br># re-generated from your development database when you run "rake".<br># Do not set this db to the same as development or production.<br>test:<br>: *default<br>database: storage/test.sqlite3<br>timeout: 20000<br>pragmas:<br>: *default_pragmas<br>mmap_size: 268435456<br>synchronous: "OFF"<br>busy_timeout: 20000<br>wal_autocheckpoint: 0
# Store production database in the storage/ directory, which by default<br># is mounted as a persistent Docker volume in config/deploy.yml.<br>production:<br>primary:<br>: *default<br>database: storage/production.sqlite3<br>cache:<br>: *default<br>database: storage/production_cache.sqlite3<br>migrations_paths: db/cache_migrate<br>queue:<br>: *default<br>database: storage/production_queue.sqlite3<br>migrations_paths: db/queue_migrate<br>cable:<br>: *default<br>database: storage/production_cable.sqlite3<br>migrations_paths: db/cable_migrate
SQLite misconfiguration in deploy.yml
I’m using Kamal for deployments, which is very simple and I recommend it. But recently I found out one reason why db locks kept happening in my background jobs. I accidently added an env var for SOLID_QUEUE_IN_PUMA. This caused solid queue to run twice, once in my web container and once in my job container.
deploy.yml:
env:<br>secret:<br>- RAILS_MASTER_KEY<br>- LITESTREAM_ACCESS_KEY_ID<br>- LITESTREAM_SECRET_ACCESS_KEY<br>clear:<br>RAILS_ENV: production<br>RAILS_LOG_LEVEL: info<br>SOLID_QUEUE_IN_PUMA: false # this actually activates it
After months I figured out what happened.
puma.rb:
# Run the Solid Queue supervisor inside of Puma for single-server deployments.<br>plugin :solid_queue if ENV["SOLID_QUEUE_IN_PUMA"]
See the issue? Any value in SOLID_QUEUE_IN_PUMA would then activate this plugin. I can’t blame myself, this was done at initial project setup and I just never noticed.
Recurring Jobs
I’m using solid queue and it’s built in recurring.yml, I make sure all recurring jobs will never run at the same time. This is a great method for finding which job caused the lock. So if a new lock exception gets logged, the first thing I check is the time it was logged...