Why Does a SELECT on performance_schema.data_locks Hang MySQL? Deep Dive & Fix

Problem Description

MySQL 8.0.26 hangs when a batch job executes INSERT INTO t1 SELECT * FROM t2 while a scheduled inspection script concurrently runs SELECT * FROM performance_schema.data_locks and SELECT * FROM performance_schema.data_lock_waits. Initially only some statements become unresponsive, but eventually the whole MySQL instance cannot be logged into.

Analysis Process

Thread states at hang

Thread 1 runs the full INSERT INTO t1 SELECT * FROM t2 statement.

Thread 2 runs the inspection queries SELECT * FROM performance_schema.data_locks followed by SELECT * FROM performance_schema.data_lock_waits.

All other threads are blocked.

Stack analysis

Thread 285 holds LOCK_status and is blocked by Thread 21 holding srv_innodb_monitor_mutex.

Thread 21 holds srv_innodb_monitor_mutex and is blocked on trx_sys_mutex_enter() (i.e., trx_sys->mutex).

Many threads, including the INSERT and the SELECT on performance_schema.data_lock_waits, are blocked on trx_sys_mutex_enter().

Local reproduction

On a small VM (2‑4 GB), create a table with millions of rows, start a transaction that locks the table, then run SELECT * FROM performance_schema.data_locks. The query fails with:

ERROR 3044 (HY000): Memory allocation error: while scanning data_locks table in function rnd_next.

After the error, the INSERT thread blocks waiting for trx_sys->mutex.

Debug investigation of trx_sys->mutex

A debug build of MySQL was compiled to expose the thread ID stored in the mutex. Using GDB, the mutex address can be printed and correlated with info thread output, e.g.:

gdb) p/x 140316922181376
$2 = 0x7f9e144d7700

Root cause hypothesis & evidence

The inspection query triggers a memory allocation failure while holding trx_sys->mutex. The code catches std::bad_alloc but returns without unlocking the mutex, leaving it permanently locked. The offending commit is:

https://github.com/mysql/mysql-server/commit/d6be2f8d23b1fe41f10c7147957faf68b117abb2

Bug explanation

Acquire trx_sys->mutex via trx_sys_mutex_enter().

Call scan_trx_list to iterate over transaction lists.

If std::bad_alloc is thrown, the catch prints an error and returns, skipping the subsequent trx_sys_mutex_exit().

Because many InnoDB components rely on trx_sys->mutex, the unreleased lock causes a cascade of blocked threads and eventually a full MySQL hang.

Reproduction steps (simplified)

Prepare a VM with 2‑4 GB memory.

Create a table t1 with ~5 million rows.

Start a transaction and lock the table: BEGIN; SELECT * FROM t1 FOR UPDATE; Execute SELECT * FROM performance_schema.data_locks; – it should raise ERROR 3044 (HY000).

Immediately query SELECT * FROM performance_schema.data_lock_waits; – this query becomes blocked.

Conclusion

The deadlock originates from:

The batch INSERT INTO t1 SELECT * FROM t2 generates billions of lock records in performance_schema.data_locks.

Scanning these records consumes excessive memory; the resulting std::bad_alloc is caught between mutex lock and unlock, leaving trx_sys->mutex unreleased.

Because trx_sys->mutex is heavily used, subsequent queries (e.g., performance_schema.data_lock_waits) and background threads become blocked, eventually causing a full MySQL hang.

Solution

The bug was fixed in MySQL 8.0.37; upgrading resolves the issue.

Avoid querying performance_schema.data_locks when the lock table contains an extremely large number of rows.