Why does the Rails 8.1 SQLite3 adapter begin transactions with BEGIN IMMEDIATE rather than SQLite's default deferred mode, and what does that cost?
answer
- write lock taken at BEGIN
- deferred reader cannot upgrade safely
- busy handler skipped on a stale upgrade
- save wraps itself in a transaction
- keep transaction blocks short
basics
~20 sBEGIN IMMEDIATE takes the write lock when the transaction starts, where the busy handler can wait for it. A deferred transaction that reads first and then writes can fail with database is locked at once, with no wait at all.
solid answer
~40 sThe adapter's `begin_db_transaction` runs `BEGIN immediate TRANSACTION`, and it also passes `default_transaction_mode: :immediate` to the sqlite3 gem, whose own default is deferred. A deferred transaction starts as a reader and asks for the write lock at its first write; if another connection committed in between, SQLite refuses the upgrade straight away without calling the busy handler, so `timeout:` cannot help and the request fails with `database is locked`. IMMEDIATE asks for the write lock at BEGIN, which is exactly where the busy handler can wait. The cost is that every ordinary Rails transaction, including the one `save` opens, holds the single write lock from its first statement to COMMIT, so transactions on one file run one after another. Keep blocks short and never make HTTP calls inside them.
go deeper
Recall that Rails starts SQLite transactions in IMMEDIATE mode and that SQLite allows one writer at a time.
Explain deferred versus immediate lock timing, the lazy BEGIN, and why the busy handler only helps at BEGIN.
Diagnose StatementTimeout as a long writer, shorten transaction blocks, and move slow work outside them.
Weigh serialised writers against SQLite's simplicity when deciding how much write traffic one file can carry.
## Two ways to start a SQLite transaction SQLite offers three transaction modes. Two matter here: | Mode | When the write lock is requested | Behaviour under contention | |---|---|---| | `DEFERRED` (SQLite's default) | at the first write inside the transaction | the transaction starts as a reader and tries to upgrade later | | `IMMEDIATE` | at `BEGIN` | the transaction waits at the start, before it has read anything | Rails picks **IMMEDIATE** for the transactions it opens. In the Rails 8.1 source, `SQLite3::DatabaseStatements#begin_db_transaction` calls `internal_begin_transaction(:immediate, nil)`, which executes `BEGIN immediate TRANSACTION`. The adapter also builds its connection parameters with `default_transaction_mode: :immediate`, overriding the sqlite3 gem's own default of deferred for any transaction the driver starts. Rails transactions are **lazy**: the `BEGIN` is not sent when the `transaction` block opens but just before the first query inside it. The IMMEDIATE lock request therefore happens at that first statement. ## Why deferred mode breaks under load Picture the carpentry firm's invoicing tool recording a payment: ```ruby Invoice.transaction do invoice = Invoice.find(params[:id]) # read invoice.payments.create!(amount: amount) # write invoice.update!(balance: invoice.balance - amount) end ``` Under DEFERRED, the `find` makes this a **read** transaction working from a snapshot of the database. If another request commits a write before `create!`, this transaction's snapshot is stale and it can no longer become the writer. SQLite does not wait in that situation, because waiting cannot make the stale snapshot current; it returns a busy error **without invoking the busy handler**. The `timeout: 5000` in database.yml never gets a chance, and the user sees a failure under perfectly ordinary concurrency. (The engine's locking rules behind this belong to SQLite's own documentation.) Under IMMEDIATE, the transaction asks for the write lock before the `find`. If someone else is writing, the request waits at that point, where the busy handler installed by `timeout:` sleeps and retries. Once it gets the lock, nobody else can commit underneath it, so the later writes cannot hit the stale-snapshot failure. ## What IMMEDIATE costs - **Every ordinary Rails transaction takes the write lock.** `save`, `create`, `update` and `destroy` each wrap themselves in a transaction, and a `transaction` block that only reads still takes the write lock once it runs its first query. - **Writers queue.** One file has one write lock, so transactions against `storage/production.sqlite3` run one after another. Readers outside transactions are unaffected thanks to WAL mode. - **Long blocks become everyone's problem.** A block that renders a PDF, calls a payment API or loops over thousands of rows holds the lock throughout, and other writers wait up to `timeout:` before failing with `ActiveRecord::StatementTimeout`. ## Writing transactions that suit it 1. **Keep the block to the database work.** Do slow work (HTTP calls, file generation, emails) before the block or after it commits. 2. **Batch big jobs.** An import of five years of invoices runs as many small transactions, not one enormous one. 3. **Do not open a transaction to read.** A report that only queries needs no block; without one, each query runs on its own and takes no write lock. 4. **Watch for `StatementTimeout`.** It is the symptom of a long writer, and the log line points at the transaction to shorten. ## Seeing it in the log The development log prints every transaction statement under the name `TRANSACTION`, so a save shows `BEGIN immediate TRANSACTION`, then the `INSERT`, then `COMMIT TRANSACTION`. Two details are worth pointing out in an interview: - the `BEGIN` line appears just before the first query in the block, not where the block opens, which is the lazy start described above; - the time between `BEGIN` and `COMMIT` is how long this request held the file's write lock; the `transaction.active_record` notification reports the same duration if you want to measure it in production. ## Where Rails does not use IMMEDIATE The adapter keeps two paths on DEFERRED: `begin_deferred_transaction`, used when a transaction is opened as non-joinable, and `begin_isolated_db_transaction`, used when an isolation level is requested. Ordinary `Model.transaction` blocks and the transactions around `save` take the IMMEDIATE path. Which options select the other two paths is part of the transaction API itself rather than of the SQLite adapter.
- Does wrapping a read-only report in Invoice.transaction cost anything on SQLite in Rails 8.1?Yes. Once the block runs its first query, Rails sends `BEGIN immediate TRANSACTION`, so the report holds the single write lock until COMMIT and every writer in the app waits behind it. Run report queries outside a transaction block; WAL mode lets them read while writes continue.
- Why doesn't raising timeout: fix database is locked errors from deferred transactions?When a deferred transaction that has already read tries to upgrade to a writer after another connection committed, SQLite returns busy immediately and never calls the busy handler. `timeout:` only configures that handler, so a longer value changes nothing. Starting with IMMEDIATE moves the wait to BEGIN, where the handler does run.
A deferred transaction is like browsing a shared spreadsheet and only asking for the edit lock when you start typing; if a colleague saved meanwhile, your edit is refused outright. IMMEDIATE is asking for the lock before you open the sheet: you may queue at the door, but nothing changes under you once inside.
- Believes timeout: rescues a deferred transaction's failed lock upgrade
- Thinks a read-only transaction block takes no write lock on SQLite
- Assumes BEGIN is sent the moment the transaction block opens
- Makes HTTP calls inside a transaction on a SQLite-backed app
- Says IMMEDIATE blocks plain reads outside transactions in WAL mode