Why an Unindexed UPDATE Can Crash Your Business—and How to Prevent It

1. Why does this happen?

InnoDB’s default isolation level is REPEATABLE READ, which can produce phantom reads when multiple transactions run concurrently. To prevent phantoms, InnoDB uses next‑key locks (a combination of record locks and gap locks) on index entries, not on the rows themselves.

When an UPDATE is executed, InnoDB acquires an exclusive (X) lock on the affected index entries. The lock is held until the transaction ends, not until the statement finishes.

If the WHERE clause uses a unique index, the next‑key lock degrades to a single record lock, affecting only that row. The article shows a table with id as the primary key and two transaction scenarios:

Transaction A updates id = 1 using the indexed column; only that row is locked, so Transaction B’s update proceeds without blocking.

Transaction B updates using a WHERE clause that lacks an indexed column, causing a full‑table scan. In this case InnoDB places next‑key locks on every row (e.g., 4 record locks and 5 gap locks), effectively locking the whole table. Transaction B is blocked until Transaction A commits. When the table is large, a full‑table next‑key lock can hold for a long time, preventing any other statements (including SELECT ) from executing and causing the business to stall.

2. How to avoid the incident?

Enable MySQL’s sql_safe_updates parameter (set to 1). In safe‑update mode MySQL aborts UPDATE or DELETE statements that do not use a key column in the WHERE clause or a LIMIT clause. According to the official documentation, the conditions for a successful UPDATE are:

Use a WHERE clause that includes an indexed column.

Use a LIMIT clause.

Use both WHERE and LIMIT; the WHERE may omit an indexed column in this case.

For DELETE , the same three conditions apply. If an indexed column is present but the optimizer still chooses a full‑table scan, you can force the use of a specific index with FORCE INDEX([index_name]) , ensuring the statement follows the index path and avoids locking the entire table.

3. Summary

An UPDATE without an indexed WHERE can lock the whole table under InnoDB’s next‑key locking, leading to service outages. To prevent this, always include indexed columns in the WHERE clause, verify the execution plan on a test environment, enable sql_safe_updates , and, when necessary, apply FORCE INDEX to guide the optimizer.