MY.L7.UNBATCHED_MASS_DML — A backfill that nothing bounds
- Category: safety
- Level: 7
- Confidence: heuristic
- Downtime class:
blocking - Stability: stable
- Suites: lint
- Applies to: MySQL 8.4
- Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.
A data write in a migration that runs as one statement over however many rows the table happens to hold. It holds row locks and grows the undo log for as long as that takes, and the deploy waits for all of it.
Flagged
DB::table('orders')->where('status', 'old')->update(['status' => 'new']);
Preferred
// Walk a key range, so every batch reads only the rows it changes:
DB::table('orders')->whereBetween('id', [$from, $to])->where('status', 'old')->update(['status' => 'new']);
// …or bound the statement with LIMIT, which keeps the locks small only when an index serves the predicate:
// DB::table('orders')->where('status', 'old')->limit(1000)->update(['status' => 'new']);
For a large table, better still: move the backfill out of the migration into a queued job, so a deploy is not waiting on it at all.
It is a heuristic, and that is the honest word
SQLens can see that a statement is unbounded. It cannot see how many rows that means — a hundred rows and a hundred million produce identical SQL, and the difference between a non-event and an outage is entirely in the table, not in the statement.
It does not ask the server either. Reading table statistics is the deploy suite's job, where a run is allowed to look; the lint suite deliberately does not query the database. So this rule reports what it can see and names what it cannot, rather than inventing a row count to sound certain.
That is also why it sits at level 7. An unbounded write on a ten-row lookup table is reported too, and a project that has not opted into estimates should not fail a gate over it.
What MySQL makes visible that PostgreSQL does not
MySQL supports UPDATE … LIMIT n, and Laravel's MySQL grammar emits it verbatim from
->limit(1000):
UPDATE orders SET status = 'new' WHERE status = 'old' LIMIT 1000
PostgreSQL has no such clause; Laravel emulates the same call as
WHERE ctid IN (SELECT … LIMIT n) — a different shape entirely. Both were measured against real
servers. The hazard is the same on both engines; the signal is not, which is why this rule
is MySQL's own rather than a shared one.
What silences it
| The statement carries… | Reported? |
|---|---|
LIMIT n | no — the write is bounded, though not always its locks, see below |
BETWEEN (a key-range walk) | no — see below |
| a predicate and nothing else | yes |
| no predicate at all | no — that is GEN.L1.DML_WITHOUT_WHERE |
| a write to a table this migration just created | no — there are no accumulated rows to sweep |
Why a BETWEEN silences it: a key-range walk is exactly the chunking pattern this rule's own
advice recommends, and Laravel emits it with no LIMIT at all. Flagging it would be crying wolf on
the fix — and a linter that flags the recommended pattern is one a team turns off, at which point it
protects nothing. The leniency errs toward a false negative: a BETWEEN on a non-key column
bounds nothing, and this rule will not say so.
What a LIMIT bounds, and what it does not
A LIMIT bounds the rows a write changes, not the rows it locks. Under REPEATABLE READ,
InnoDB's default, an UPDATE or a DELETE locks every row its scan reads, whether the row matches
or not. On a predicate no index serves, a batch therefore locks everything its scan passes on the way
to its thousandth match, and every later batch scans the rows the earlier ones changed again.
Measured on MySQL 8.0 over 10,000 rows, status unindexed, the first match at id 9001: while
UPDATE orders SET status = 'new' WHERE status = 'old' LIMIT 10 held its locks, a second session
waited on row 5 until its lock wait timeout, and row 9500, past the tenth match, stayed free. Under
READ COMMITTED, row 5 was free as well.
That is why the advice leads with the key range. A LIMIT still silences the rule: the write is
bounded, and the size of the write is what this rule judges.
Three rules, three questions, one statement
Keeping them apart is what stops a single UPDATE from producing three findings a reader learns to
skim:
| Rule | Asks |
|---|---|
GEN. | correctness — is there a predicate at all? |
| MY.L3.MIXED_DDL_DML_NOT_ATOMIC | atomicity — does it follow a schema change it cannot be rolled back past? |
| this rule | size — the predicate is there, and the write is still unbounded |
Why the finding says blocking
Not from a literal in the rule. A data write is not a schema change, so the online-DDL matrix has no
entry for it and never will — the class comes from the package's named non-DDL derivation, whose
whole_table_write case is written for exactly this: a write whose predicate cannot bound it, whose
lock reach is therefore the whole table.
blocking describes the reach of the statement, not a guess at its duration. The duration is
the part no static reader has any business estimating.
Sources
- UPDATE syntax — the LIMIT clause — MySQL 8.4
- InnoDB locking — MySQL 8.4
- Locks set by different SQL statements in InnoDB — MySQL 8.4
The fix material this rule carries
A finding from this rule carries machine-readable fix material, using this sequence:
batched_backfill— Move the data in bounded batches from a job rather than in one migration statement.
The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.