Unbounded UPDATE or DELETE

Warning — it works, but holds the single write lock or breaks the previous release.

What it catches

An UPDATE or DELETE with no WHERE touches every row in one write transaction. SQLite has one writer at a time: the whole application queues behind it (or hits SQLITE_BUSY) for as long as the statement runs, and the rollback journal or WAL grows by the size of the change.

Fires on

UPDATE orders SET status = 'archived';

Do this instead

State the predicate, and batch by rowid range from an operational job so each chunk commits on its own and the write lock is released between batches.

-- batched job, not a migration statement:
--    UPDATE orders SET status = 'archived'
--    WHERE status = 'closed' AND rowid BETWEEN ? AND ?;

Catch this before it ships

SQLite support is in alpha: this rule runs locally in the free CLI, static only, and not in the hosted service yet: npx bolvrk check migration.sql --engine=sqlite

Browse all 104 rules

← SL012 SL014 →