Skip to main content

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​

// MySQL accepts LIMIT on UPDATE, so a chunk is readable in the statement itself:
DB::table('orders')->where('status', 'old')->limit(1000)->update(['status' => 'new']);
// …or walk a key range, which bounds it just as well:
// DB::table('orders')->whereBetween('id', [$from, $to])->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 nno — the write is bounded
BETWEEN (a key-range walk)no — see below
a predicate and nothing elseyes
no predicate at allno — that is GEN.L1.DML_WITHOUT_WHERE
a write to a table this migration just createdno — 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.

Three rules, three questions, one statement​

Keeping them apart is what stops a single UPDATE from producing three findings a reader learns to skim:

RuleAsks
GEN.L1.DML_WITHOUT_WHEREcorrectness — is there a predicate at all?
MY.L3.MIXED_DDL_DML_NOT_ATOMICatomicity — does it follow a schema change it cannot be rolled back past?
this rulesize — 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​

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.