Why a Simple UPDATE Can Lock Your Whole MySQL Table—and How to Prevent It

Why Does This Accident Happen?

InnoDB’s default transaction isolation level is REPEATABLE READ, which can produce phantom reads when multiple transactions run concurrently. To prevent phantom reads, InnoDB implements row‑level locking using next‑key locks (a combination of record and gap locks) that lock the index entries and the gaps between them.

When an UPDATE statement runs, InnoDB acquires an exclusive (X) lock on the affected index entries. 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. If the WHERE clause does not use an indexed column, InnoDB must scan the whole table, applying next‑key locks to every row (record lock + gap lock), effectively locking the entire table.

Example: a table with id as the primary key.

Two transactions A and B run in sequence. In the first scenario, Transaction A’s UPDATE uses WHERE id = 1, which is indexed, so only that row is locked. Transaction B’s update proceeds without blocking.

In the second scenario, Transaction A’s UPDATE lacks an indexed column in the WHERE clause, causing a full‑table scan. InnoDB applies next‑key locks to all rows (four record locks and five gap locks in the example), so Transaction B’s UPDATE is blocked.

When a large table is updated without an indexed WHERE, the whole table remains locked until the transaction ends. During this time only SELECT ... FROM statements can run; all other statements are blocked, causing business downtime and, often, a scolding from management.

How to Prevent This Accident?

Enable MySQL’s sql_safe_updates variable (set it to 1). In safe‑update mode MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause.

If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) The default value is 0.

For an UPDATE to succeed in safe‑update mode, one of the following must be true:

Use a WHERE clause that includes an indexed column.

Use a LIMIT clause.

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

For a DELETE to succeed, the requirements are similar:

Use a WHERE clause with an indexed column.

Or use both WHERE and LIMIT, allowing the WHERE to lack an indexed column.

If the optimizer still chooses a full‑table scan despite an indexed WHERE, force the use of a specific index with: force index([index_name]) This hints the optimizer to use the given index and avoids the full‑table scan and its associated locking risk.

Conclusion

Never underestimate a single UPDATE statement; on a production database it can halt services and provoke management criticism. Always ensure the WHERE clause contains an indexed column and verify on a test environment that the statement uses an index scan. Enable sql_safe_updates to catch unsafe statements, and apply FORCE INDEX([index_name]) when the optimizer would otherwise perform a full‑table scan.