Why MySQL 8.0 Upgrade Can Suddenly Slow Down Queries: The Hidden Lock Table Trap

Background: a client upgraded MySQL from 5.7.29 to 8.0.25. Immediately after the upgrade, slow queries appeared despite low system load.

Root Cause Analysis

The monitoring script runs a query against sys.innodb_lock_waits. In MySQL 8.0 the view is built on performance_schema.data_locks instead of the 5.7 information_schema.innodb_locks. The underlying base table changed its behavior: the 8.0 data_locks table records every lock that a transaction currently holds, which can grow to millions of rows for large UPDATE statements.

Because reading performance_schema.data_locks requires holding the global mutex trx_sys->mutex for the whole scan, a large lock table blocks other transactions, causing the observed slowdown.

Test Verification

MySQL 5.7 Test

Created table t4 and executed a long‑running UPDATE that locked the whole table. Queries to information_schema.innodb_lock_waits and information_schema.innodb_locks showed one waiting transaction and one blocking transaction, matching the expected behavior.

MySQL 8.0 Test

Repeated the same steps on MySQL 8.0. Queries to performance_schema.data_locks returned multiple rows for the holding transaction and a waiting row for the second transaction, confirming that the view now reports all held locks.

Conclusion

The performance_schema.data_locks table can become extremely large when a single SQL statement locks many rows. The thread that reads this table holds trx_sys->mutex for the duration of the scan, blocking other transactions and causing massive slowdown. Adding an index to reduce the number of locked rows eliminated the problem in the production case. For lock‑contention diagnostics, querying performance_schema.data_lock_waits is sufficient.