How to Quickly Identify and Kill Sessions Holding Global Read Locks in MySQL

Background : When backup tools such as xtrabackup acquire a global read lock, the lock is usually released quickly, but occasionally it persists for a long time, causing all write operations to block. The SHOW PROCESSLIST command only shows sessions waiting for the lock, not the session that actually holds it.

Goal : Provide DBA‑level methods to rapidly locate the session that owns the global read lock and kill it to restore normal database activity.

Method 1 – Using performance_schema.metadata_locks (MySQL 5.7+) :

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
FLUSH TABLES WITH READ LOCK;
SELECT * FROM performance_schema.metadata_locks;
-- Identify rows where OBJECT_TYPE='GLOBAL' and LOCK_TYPE='SHARED'
SELECT t.processlist_id FROM performance_schema.threads t
JOIN performance_schema.metadata_locks ml ON ml.owner_thread_id = t.thread_id
WHERE ml.object_type='GLOBAL' AND ml.lock_type='SHARED';
-- Kill the returned processlist_id

Rows with OBJECT_TYPE=GLOBAL and LOCK_TYPE=SHARED indicate the global lock; the join returns the processlist_id that can be killed.

Method 2 – Using performance_schema.events_statements_history (MySQL 5.6+) :

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history';
FLUSH TABLES WITH READ LOCK;
SELECT * FROM performance_schema.events_statements_history WHERE sql_text LIKE 'flush tables%';
SELECT t.processlist_id FROM performance_schema.threads t
JOIN performance_schema.events_statements_history h ON h.thread_id = t.thread_id
WHERE h.digest_text LIKE 'FLUSH TABLES%';
-- Kill the returned processlist_id

This method relies on the history of executed statements; it may miss the lock if the history has already been purged.

Method 3 – Using a GDB script (fallback when the above views are unavailable):

#!/bin/bash
set -v
threads=$(gdb -p $1 -q -batch -ex 'info threads' | awk '/mysql/{print $1}' | grep -v '*' | sort -nk1)
for i in $threads; do
  echo "######## thread $i ########"
  lock=$(gdb -p $1 -q -batch -ex "thread $i" -ex 'p do_command::thd->thread_id' -ex 'p do_command::thd->global_read_lock' |
        grep -B3 GRL_ACQUIRED_AND_BLOCKS_COMMIT)
  if [[ $lock =~ 'GRL_ACQUIRED_AND_BLOCKS_COMMIT' ]]; then
    echo "$lock"
    break
  fi
done
# Note: thread_id field differs between MySQL 5.6 (thd->thread_id) and 5.7 (thd->m_thread_id)

The script attaches to the MySQL process, enumerates its threads, and prints the thread that holds GRL_ACQUIRED_AND_BLOCKS_COMMIT , which corresponds to the global read lock session ID.

Method 4 – Filtering SHOW PROCESSLIST :

pager awk '/username/{if (length($7) == 4) {print $0}}' | sort -rk6;
SHOW PROCESSLIST;

When the backup runs under a specific user (e.g., root ), sessions with a long Time value, empty State and Info fields are likely the lock holder; kill the identified Id .

Method 5 – Restart MySQL :

If all else fails, restarting the MySQL service releases the global read lock.

Feel free to share any better solutions in the comments.