How dangerous is an UPDATE or DELETE without WHERE in a SQLite migration?

Unbounded UPDATE or DELETE

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

What happens

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.

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

UPDATE orders SET status = 'archived';

The safe pattern

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 ?;

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.

Fires (2)

delete all

DELETE FROM orders;

update all

UPDATE orders SET status = 'archived';

Stays silent (2)

delete on new table

CREATE TABLE staging (id INTEGER PRIMARY KEY);
DELETE FROM staging;

update where

UPDATE orders SET status = 'archived' WHERE status = 'closed' AND rowid BETWEEN 1 AND 10000;

How to check locally

Catch this before it ships

SQLite support is in beta: this rule runs locally in the free CLI, static only, and not in the hosted service yet. No install, nothing leaves your machine:

npx bolvrk check migration.sql --engine=sqlite

Browse all 135 rules

← SL012 SL014 →