Analyzing Slow UPDATE Locks in MySQL 5.7 and Optimizing with Indexes and Optimizer Trace

Background

A system running MySQL 5.7.25 with REPEATABLE READ isolation exhibited very slow update operations, with a large proportion of lock time in the slow‑query log.

Analysis

Examining the table definition and the execution plan of the UPDATE statement revealed that the query scans the primary key index and that the name column has no index. When multiple transactions run concurrently, this leads to blocking.

mysql> show create table test;
+-------+-----------------------------------------------------+
| Table | Create Table                                        |
+-------+-----------------------------------------------------+
| test  | CREATE TABLE `test` (
| `id` int(11) NOT NULL AUTO_INCREMENT,
| `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
| PRIMARY KEY (`id`)
| ) ENGINE=InnoDB AUTO_INCREMENT=2621401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain update test set name='test' where name='a';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| 1  | UPDATE      | test  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 2355988 | 100.00   | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set (0.00 sec)

Because the name column is not indexed, the UPDATE performs a full primary‑key scan, causing lock contention under concurrent transactions.

Adding an Index

If the name column has few duplicate values, adding an index can eliminate the blocking:

mysql> alter table test add index tt(name);
Query OK, 0 rows affected (2.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

After adding the index, the optimizer still chose a full‑table scan because the cost of using the new index (664,465) was higher than the cost of the full scan (475,206).

Using Optimizer Trace

Enabling optimizer_trace provides detailed insight into the optimizer’s cost calculations:

mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='test' where name='a';
Query OK, 262144 rows affected (5.97 sec)

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
... (trace output showing cost of table scan vs index scan) ...

The trace confirms that the full‑table scan cost (475,206) is lower than the index scan cost (664,465), so MySQL selects the full scan.

Possible Remedies

If the isolation level can be changed, switching from REPEATABLE READ (RR) to READ COMMITTED (RC) enables semi‑consistent reads, which can reduce lock conflicts for low‑cardinality columns. Otherwise, application‑level changes such as batching updates or redesigning the schema are recommended.

Conclusion

1. InnoDB row locks are based on indexed columns; without an index the engine falls back to a full‑table scan, leading to lock contention.

2. Enabling semi‑consistent reads under RC isolation can mitigate blocking, and optimizer trace is a valuable tool for diagnosing why the optimizer prefers a full scan.