How an Unindexed UPDATE Can Lock Your Whole MySQL Table and Crash Production

While chatting online, the author heard a story about a production outage caused by a single UPDATE statement that lacked an indexed WHERE clause, leading to a full‑table lock and a scolding from the boss.

InnoDB’s default isolation level is REPEATABLE READ . Under this level, concurrent transactions can suffer phantom reads, so InnoDB implements next‑key locks (a combination of record and gap locks) to prevent other transactions from inserting rows that would appear in a repeated query.

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

The lock is applied to the index, not directly to the row. If the WHERE clause uses a unique index, the next‑key lock degrades to a simple record lock, affecting only one row.

Example: a table with id as the primary key. Two transactions execute in order. Transaction A updates id = 1 using an indexed equality condition, so only that row is locked and Transaction B’s update proceeds without blocking.

However, when the WHERE clause does not use an indexed column, MySQL performs a full‑table scan. The update then acquires next‑key locks on **all** records (record + gap locks), effectively locking the entire table.

In this scenario, Transaction B’s UPDATE is blocked because Transaction A’s unindexed update generated four record locks and five gap locks, locking the whole table. While the lock is held, only SELECT … FROM statements can run; all other statements are blocked, causing the business to stall.

To prevent such accidents, MySQL provides the sql_safe_updates system variable. Setting it to 1 makes MySQL abort 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.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

Therefore, an UPDATE can succeed only if one of the following is true:

the WHERE clause contains an indexed column;

a LIMIT clause is used;

both WHERE and LIMIT are present, allowing the WHERE to omit an indexed column.

Similarly, a DELETE requires either an indexed WHERE or a combination of WHERE and LIMIT.

If the optimizer still chooses a full‑table scan despite an indexed WHERE, you can force index usage with: force index([index_name]) Finally, the author advises: always verify that an UPDATE uses an index scan on a test environment, enable sql_safe_updates to catch unsafe statements, and apply FORCE INDEX when necessary to avoid accidental full‑table locks.