Should a SQLite write migration use BEGIN IMMEDIATE instead of DEFERRED?

Write migration in a DEFERRED transaction

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

A plain BEGIN is DEFERRED: it takes no lock until the first write, then tries to upgrade a read lock to a write lock. If another connection holds the write lock at that moment the upgrade fails immediately with SQLITE_BUSY, busy_timeout does not apply to a lock upgrade, and the migration aborts part way through its reads.

Why it is dangerous on a populated table

SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.

Fires on

BEGIN;
ALTER TABLE orders ADD COLUMN region TEXT;
COMMIT;

The safe pattern

BEGIN IMMEDIATE;
ALTER TABLE orders ADD COLUMN region TEXT;
COMMIT;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

BEGIN DEFERRED TRANSACTION;
UPDATE orders SET region = 'eu' WHERE region IS NULL;
COMMIT;

BEGIN;
ALTER TABLE orders ADD COLUMN region TEXT;
COMMIT;

BEGIN IMMEDIATE;
ALTER TABLE orders ADD COLUMN region TEXT;
COMMIT;

BEGIN;
PRAGMA foreign_key_check;
COMMIT;

How to check locally

npx bolvrk check migration.sql --engine=sqlite