背景
在用 xtrabackup 等备份工具做备份时会有全局锁，正常情况锁占用时间很短，但偶尔会遇到锁长时间占用导致系统写入阻塞，现象是 show processlist 看到众多会话显示 wait global read lock，那可能对业务影响会很大。而且 show processlist 是无法看到哪个会话持有了全局锁，如果直接杀掉备份进程有可能进程杀掉了，但锁依然没释放，数据库还是无法写入。这时我们需要有快速定位持有全局锁会话的方法，杀掉对应会话数据库就恢复正常了。
方法
方法1：利用 metadata_locks 视图
此方法仅适用于 MySQL 5.7 以上版本，该版本 performance_schema 新增了 metadata_locks，如果上锁前启用了元数据锁的探针（默认是未启用的），可以比较容易的定位全局锁会话。过程如下。
开启元数据锁对应的探针
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
模拟上锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL | NULL | NULL | 140613033070288 | SHARED | EXPLICIT | GRANTED | lock.cc:1110 | 268969 | 80 |
| COMMIT | NULL | NULL | 140612979226448 | SHARED | EXPLICIT | GRANTED | lock.cc:1194 | 268969 | 80 |
| GLOBAL | NULL | NULL | 140612981185856 | INTENTION_EXCLUSIVE | STATEMENT | PENDING | sql_base.cc:3189 | 303901 | 665 |
| TABLE | performance_schema | metadata_locks | 140612983552320 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 268969 | 81 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.01 sec)
OBJECT_TYPE=GLOBAL LOCK_TYPE=SHARED 表示全局锁
mysql> 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';
+----------------+
| processlist_id |
+----------------+
| 268944 |
+----------------+
1 row in set (0.00 sec)
mysql> update performance_schema.setup_consumers set enabled = 'YES' where NAME = 'events_statements_history'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.events_statements_history where sql_text like 'flush tables%'\G
*************************** 1. row ***************************
THREAD_ID: 39
EVENT_ID: 21
END_EVENT_ID: 21
EVENT_NAME: statement/sql/flush
SOURCE: socket_connection.cc:95
TIMER_START: 94449505549959000
TIMER_END: 94449505807116000
TIMER_WAIT: 257157000
LOCK_TIME: 0
SQL_TEXT: flush tables with read lock
DIGEST: 03682cc3e0eaed3d95d665c976628d02
DIGEST_TEXT: FLUSH TABLES WITH READ LOCK
...
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
mysql> 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%';
+----------------+
| processlist_id |
+----------------+
| 12 |
+----------------+
1 row in set (0.01 sec)
#!/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
# thread_id变量，5.6和5.7版本有所不同，5.6版本是thd->thread_id，5.7版本是thd->m_thread_id，这里需要留意下
脚本输出
######## thread 2 ########
[Switching to thread 2 (Thread 0x7f610812b700 (LWP 10702))]
#0 0x00007f6129685f0d in poll () from /lib64/libc.so.6
$1 = 9 此处就是mysql中的会话ID
$2 = {static m_active_requests = 1, m_state = Global_read_lock::GRL_ACQUIRED_AND_BLOCKS_COMMIT, m_mdl_global_shared_lock = 0x7f60e800cb10, m_mdl_blocks_commits_lock = 0x7f60e801c900}
方法4：show processlist
mysql>pager awk '/username/{if (length($7) == 4) {print $0}}'|sort -rk6
mysql>show processlist
社区近期动态
Mycat 问题免费诊断
诊断范围支持：
Mycat 的故障诊断、源码分析、性能优化
服务支持渠道：
技术交流群，进群后可提问
QQ群（669663113）
社区通道，邮件&电话
osc@actionsky.com
现场拜访，线下实地，1天免费拜访
关注“爱可生开源社区”公众号，回复关键字“Mycat”，获取活动详情。
社区技术内容征稿
征稿内容：
格式：.md/.doc/.txt
主题：MySQL、分布式中间件DBLE、数据传输组件DTLE相关技术内容
要求：原创且未发布过
奖励：作者署名；200元京东E卡+社区周边
投稿方式：
邮箱：osc@actionsky.com
格式：[投稿]姓名+文章标题
以附件形式发送，正文需注明姓名、手机号、微信号，以便小编及时联系