Why a Single UPDATE Can Crash Your MySQL Database and How to Prevent It

When executing an UPDATE on a MySQL table using InnoDB, forgetting to use an indexed column in the WHERE clause can cause a full‑table next‑key lock, blocking other transactions and potentially crashing the business.

Why does this happen?

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

An UPDATE acquires an exclusive X lock on the affected records. The lock is held until the transaction ends, not just until the statement finishes.

If the WHERE clause uses a unique index, the next‑key lock degrades to a simple record lock affecting only one row. However, when the WHERE clause lacks an indexed column, InnoDB performs a full‑table scan and applies next‑key locks to every record, effectively locking the entire table.

Example: a table with a primary‑key column

id

. When two transactions run:

Transaction A updates a row using

WHERE id = 1

(indexed), so only that row is locked and Transaction B can proceed.

When the WHERE clause does not use an index, the UPDATE triggers a full‑table scan, applying next‑key locks to all rows. The following diagram shows Transaction B being blocked:

Thus, a large table updated without an index can lock the entire table for the duration of the transaction, blocking SELECTs and other statements.

How to avoid it?

Enable MySQL’s safe‑update mode:

<code>SET sql_safe_updates = 1;</code>

When

sql_safe_updates

is 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause.

For UPDATE to succeed, at least one of the following must be true:

WHERE clause includes an indexed column.

UPDATE includes a LIMIT clause.

Both WHERE and LIMIT are present (the WHERE may lack an index).

For DELETE, either an indexed WHERE clause or a combination of WHERE and LIMIT is required.

If the optimizer still chooses a full‑table scan despite an indexed WHERE, force the use of a specific index:

<code>UPDATE table_name FORCE INDEX (index_name) SET ... WHERE ...;</code>

Summary

Never run an UPDATE without ensuring the WHERE clause uses an indexed column or a LIMIT.

Test the statement on a staging environment to verify it uses an index scan.

Enable

sql_safe_updates

to catch unsafe UPDATE/DELETE statements.

If the optimizer prefers a full scan, use

FORCE INDEX

to direct it to the appropriate index.