Avoiding SQLite Database Locks in Production
12 hours ago
- #Background Jobs
- #SQLite
- #Rails
- The article discusses encountering SQLite3::BusyException in a Rails app using Solid Queue due to SQLite's single-writer locking mechanism.
- Key solutions include proper SQLite configuration with busy_timeout and timeout set to the same value, enabling WAL mode, and optimizing pragmas like synchronous and mmap_size.
- A misconfiguration in deploy.yml (SOLID_QUEUE_IN_PUMA env var) caused Solid Queue to run twice, leading to lock issues; fixing it resolved the problem.
- Recurring jobs in Solid Queue should be scheduled to avoid simultaneous execution to identify lock causes through logged exception times.
- Avoid using .find_each for batch updates in jobs as it holds a read cursor open; instead, pluck IDs and process in small batches to release locks quickly.
- An opencode skill was created to prevent SQLite lock issues by guiding AI agents to avoid read cursors during writes and batch writes in transactions.