skip to content

In a Rails 8.1 SQLite database.yml, what does timeout: 5000 do, and what error does a request get when it runs out?

level: middleimportance: should knowfreq 35%

answer

  1. milliseconds, not seconds
  2. waits on a locked database file
  3. busy handler sleeps without holding the GVL
  4. SQLite3::BusyException becomes StatementTimeout
  5. retries: removed in 8.1

basics

~20 s

timeout: 5000 makes a connection that finds the database locked by another writer retry for up to 5000 ms instead of failing at once. When the wait runs out, SQLite's busy error surfaces as ActiveRecord::StatementTimeout.

solid answer

~40 s

The generated SQLite `database.yml` sets `timeout: 5000`, in milliseconds. When a connection needs the write lock and another connection holds it, SQLite reports the file as busy; the adapter installs the sqlite3 gem's `busy_handler_timeout=` handler, which sleeps a millisecond between attempts and releases Ruby's global VM lock while it does, so other Puma threads keep running. If the lock is still held after 5000 ms, the driver raises `SQLite3::BusyException` ("database is locked") and Rails translates it to `ActiveRecord::StatementTimeout`. Without `timeout:` there is no wait at all. The value must cast to an integer or the adapter raises `TypeError`. Rails 8.0 deprecated the old `retries:` option in favour of `timeout:`, and Rails 8.1 removed it.

go deeper

for a junior

Recall that timeout: is in milliseconds and makes SQLite wait for a lock instead of failing immediately.

for a middle

Explain the busy handler, why it releases the GVL, and the BusyException to StatementTimeout translation.

for a senior

Treat repeated StatementTimeout as write contention, find the long transaction holding the lock, and keep timeout: as is.

for a principal

Weigh a longer lock wait against user-facing latency, and use the error rate as an input to the move off SQLite.

## What the key controls SQLite allows **one writer per database file** at a time. When a second connection wants to write while the first holds the write lock, SQLite does not queue it; it answers "busy". What happens next is up to a **busy handler**, a callback the application registers on the connection to decide whether to wait and try again. In a Rails app that is what `timeout:` configures. The generated `config/database.yml` for SQLite has it in the shared `default` block: ```yaml default: &default adapter: sqlite3 max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 ``` The unit is **milliseconds**, so 5000 is five seconds. The adapter's own documentation states the default plainly: without the key there is **no wait**, and the first lock conflict is an error. ## How Rails installs the wait When a connection is configured, the `SQLite3Adapter`: 1. reads `@config[:timeout]` and casts it with `type_cast_config_to_integer`, so `"5000"` from a URL query string works too; 2. raises `TypeError, "timeout must be integer"` if the cast does not produce an integer; 3. sets `busy_handler_timeout = timeout` on the raw `SQLite3::Database`. `busy_handler_timeout=` is a sqlite3 gem method (added in gem 2.0). It registers a Ruby block as the busy handler: on the first busy callback it records a deadline; on later ones it calls `sleep(0.001)` and tries again until the deadline passes, then gives up. The gem documents it as the alternative to `busy_timeout=`, which makes SQLite sleep inside C **while holding the GVL**. That difference matters under Puma. A Rails process runs several request threads. If one thread waits for the lock inside C with the GVL held, every other thread in that process stalls too, including the one that holds the lock and is trying to finish its commit. Sleeping in Ruby gives the GVL back, so the lock holder can finish and the waiter gets in. ## What happens when the wait runs out | Stage | What you see | |---|---| | lock held, deadline not reached | the query simply takes longer | | deadline passed | the driver raises `SQLite3::BusyException` ("database is locked") | | Rails' exception translation | `ActiveRecord::StatementTimeout`, a subclass of `QueryAborted` < `StatementInvalid` | For the carpentry firm's invoicing tool, a `StatementTimeout` on `Payment.create!` means some other write held the lock for the full five seconds: usually a long transaction (an import, a report that wraps reads and writes, an HTTP call made inside a transaction block), not ordinary traffic. ## Reading the error in production - The exception names the statement that **waited**, not the transaction that **held** the lock. Look for a long request or job running at the same moment. - Look at the gap: an error raised about five seconds after the request started is the busy wait expiring, not a slow query. - A burst of these errors right after a deploy often means a migration or a data backfill held the lock while traffic arrived. ## Things the key does not do - It is **not** the pool's checkout wait. Waiting for a free connection from the pool is `checkout_timeout` (default 5 seconds), and running out raises `ActiveRecord::ConnectionTimeoutError`. - It is **not** a statement time limit. A slow query that holds no lock conflict runs as long as it takes. - It does **not** help a transaction that started as a reader and later tries to write after someone else committed; SQLite reports that conflict without calling the busy handler. Rails avoids that case by opening its transactions with `BEGIN IMMEDIATE`. ## What happened to retries: Earlier Rails versions also accepted a `retries:` count for SQLite as another way to wait on a busy database. Rails 8.0 deprecated it in favour of `timeout:`, and **Rails 8.1 removed it**. A config carried forward from an older app should drop `retries:` and rely on `timeout:` alone. ## Choosing a value - Keep the generated 5000 unless you have evidence; raising it hides long transactions instead of fixing them. - Set it on **every** SQLite entry. The generated production block repeats `<<: *default` in `primary`, `cache`, `queue` and `cable`, so all four inherit it. - Treat a steady trickle of `StatementTimeout` as a signal about write contention, which is the point where one writer per file starts to hurt.

  • Why does Rails use the gem's busy_handler_timeout= rather than SQLite's own busy_timeout?
    SQLite's built-in busy timeout sleeps inside C while the Ruby thread still holds the global VM lock, so every other thread in the Puma process stalls, including the thread trying to commit and release the lock. `busy_handler_timeout=` sleeps in Ruby for a millisecond per attempt, which releases the GVL and lets the lock holder finish.
  • How do you tell a SQLite lock wait from a connection-pool wait in a Rails 8.1 error report?
    A lock wait that expires is `ActiveRecord::StatementTimeout`, translated from `SQLite3::BusyException` ("database is locked"), and is governed by `timeout:`. A pool wait that expires is `ActiveRecord::ConnectionTimeoutError` after `checkout_timeout` (5 seconds by default), meaning every connection was checked out.

  • Reads timeout: 5000 as five thousand seconds
  • Confuses timeout: with checkout_timeout for the connection pool
  • Expects ActiveRecord::Deadlocked when the SQLite lock wait expires
  • Still configures retries: for SQLite on Rails 8.1
  • Thinks timeout: caps how long any slow query may run

Assumes Rails 8.1.4 with the sqlite3 gem 2.x: timeout: installs busy_handler_timeout=, and the retries: option deprecated in 8.0 was removed in 8.1.