技术分享 | 快速定位令人头疼的全局锁

于由洪斌发布

背景

在用 xtrabackup 等备份工具做备份时会有全局锁，正常情况锁占用时间很短，但偶尔会遇到锁长时间占用导致系统写入阻塞，现象是 show processlist 看到众多会话显示 wait global read lock，那可能对业务影响会很大。而且 show processlist 是无法看到哪个会话持有了全局锁，如果直接杀掉备份进程有可能进程杀掉了，但锁依然没释放，数据库还是无法写入。这时我们需要有快速定位持有全局锁会话的方法，杀掉对应会话数据库就恢复正常了。

通常这种紧急情况发生，需要 DBA 有能力快速恢复业务，如果平时没有储备，现找方法肯定是来不及的，所以我整理了几种方法，在实际故障中帮助我快速的定位到锁会话恢复了业务，非常有效，与大家分享。

方法

方法1：利用 metadata_locks 视图

此方法仅适用于 MySQL 5.7 以上版本，该版本 performance_schema 新增了 metadata_locks，如果上锁前启用了元数据锁的探针（默认是未启用的），可以比较容易的定位全局锁会话。过程如下。

  1. 开启元数据锁对应的探针
  2. mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
  3. Query OK, 1 row affected (0.04 sec)
  4. Rows matched: 1  Changed: 1  Warnings: 0

  6. 模拟上锁
  7. mysql> flush tables with read lock;
  8. Query OK, 0 rows affected (0.06 sec)

  10. mysql> select * from performance_schema.metadata_locks;
  11. +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
  12. | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
  13. +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
  14. | GLOBAL      | NULL               | NULL           |       140613033070288 | SHARED              | EXPLICIT      | GRANTED     | lock.cc:1110      |          268969 |             80 |
  15. | COMMIT      | NULL               | NULL           |       140612979226448 | SHARED              | EXPLICIT      | GRANTED     | lock.cc:1194      |          268969 |             80 |
  16. | GLOBAL      | NULL               | NULL           |       140612981185856 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     | sql_base.cc:3189  |          303901 |            665 |
  17. | TABLE       | performance_schema | metadata_locks |       140612983552320 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |          268969 |             81 |
  18. +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
  19. 4 rows in set (0.01 sec)

  21. OBJECT_TYPE=GLOBAL  LOCK_TYPE=SHARED 表示全局锁


  24. 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';
  25. +----------------+
  26. | processlist_id |
  27. +----------------+
  28. |         268944 |
  29. +----------------+
  30. 1 row in set (0.00 sec)
定位到锁会话 ID 直接 kill 该会话即可。
方法2：利用 events_statements_history 视图
此方法适用于 MySQL 5.6 以上版本，启用 performance_schema.eventsstatements_history（5.6 默认未启用，5.7 默认启用），该表会 SQL 历史记录执行，如果请求太多，会自动清理早期的信息，有可能将上锁会话的信息清理掉。过程如下。
  1. mysql> update performance_schema.setup_consumers set enabled = 'YES' where NAME = 'events_statements_history'
  2. Query OK, 0 rows affected (0.00 sec)
  3. Rows matched: 1  Changed: 0  Warnings: 0

  5. mysql> flush tables with read lock;
  6. Query OK, 0 rows affected (0.00 sec)

  8. mysql> select * from performance_schema.events_statements_history where sql_text like 'flush tables%'\G
  9. *************************** 1. row ***************************
  10.               THREAD_ID: 39
  11.                EVENT_ID: 21
  12.            END_EVENT_ID: 21
  13.              EVENT_NAME: statement/sql/flush
  14.                  SOURCE: socket_connection.cc:95
  15.             TIMER_START: 94449505549959000
  16.               TIMER_END: 94449505807116000
  17.              TIMER_WAIT: 257157000
  18.               LOCK_TIME: 0
  19.                SQL_TEXT: flush tables with read lock
  20.                  DIGEST: 03682cc3e0eaed3d95d665c976628d02
  21.             DIGEST_TEXT: FLUSH TABLES WITH READ LOCK
  22. ...
  23.     NESTING_EVENT_LEVEL: 0
  24. 1 row in set (0.00 sec)

  26. 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%';
  27. +----------------+
  28. | processlist_id |
  29. +----------------+
  30. |             12 |
  31. +----------------+
  32. 1 row in set (0.01 sec)
方法3：利用 gdb 工具
如果上述两种都用不了或者没来得及启用，可以尝试第三种方法。利用 gdb 找到所有线程信息，查看每个线程中持有全局锁对象，输出对应的会话 ID，为了便于快速定位，我写成了脚本形式。也可以使用 gdb 交互模式，但 attach mysql 进程后 mysql 会完全 hang 住，读请求也会受到影响，不建议使用交互模式。
  1. #!/bin/bash
  2. set -v

  4. threads=$(gdb -p $1 -q -batch -ex 'info threads'| awk '/mysql/{print $1}'|grep -v '*'|sort -nk1)
  5. for i in $threads; do
  6.   echo "######## thread $i ########"
  7.   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`
  8.   if [[ $lock =~ 'GRL_ACQUIRED_AND_BLOCKS_COMMIT' ]]; then
  9.     echo "$lock"
  10.     break
  11.   fi
  12. done

  14. # thread_id变量，5.6和5.7版本有所不同，5.6版本是thd->thread_id，5.7版本是thd->m_thread_id，这里需要留意下

脚本输出

  1. ######## thread 2 ########
  2. [Switching to thread 2 (Thread 0x7f610812b700 (LWP 10702))]
  3. #0  0x00007f6129685f0d in poll () from /lib64/libc.so.6
  4. $1 = 9 此处就是mysql中的会话ID
  5. $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}
但实际环境可能会比较复杂，用 gdb 可能也无法获得你想要的信息，是不是就没辙了。

方法4：show processlist

如果备份程序使用的特定用户执行备份，如果是 root 用户备份，那 time 值越大的是持锁会话的概率越大，如果业务也用 root 访问，重点是 state 和 info 为空的，这里有个小技巧可以快速筛选，筛选后尝试 kill 对应 ID，再观察是否还有 wait global read lock 状态的会话。
  1. mysql>pager awk '/username/{if (length($7) == 4) {print $0}}'|sort -rk6
  2. mysql>show processlist
如果以上方法全部无效，最后释放终极大招…
方法5：重启试试！
如果你有更好的方法，可以留言分享。

社区近期动态

No.1

Mycat 问题免费诊断


诊断范围支持：

Mycat 的故障诊断、源码分析、性能优化

服务支持渠道：

  1. 技术交流群，进群后可提问

    QQ群（669663113）

  2. 社区通道，邮件&电话

    osc@actionsky.com

  3. 现场拜访，线下实地，1天免费拜访

关注“爱可生开源社区”公众号，回复关键字“Mycat”，获取活动详情。


No.2

社区技术内容征稿


征稿内容：

  1. 格式：.md/.doc/.txt

  2. 主题：MySQL、分布式中间件DBLE、数据传输组件DTLE相关技术内容

  3. 要求：原创且未发布过

  4. 奖励：作者署名；200元京东E卡+社区周边

投稿方式：

  1. 邮箱：osc@actionsky.com

  2. 格式：[投稿]姓名+文章标题

  3. 以附件形式发送，正文需注明姓名、手机号、微信号，以便小编及时联系


分类： 技术文章故障分析
标签：

相关文章

技术分享

故障分析 | 一则 MySQL 从节点 hung 死问题分析

作者通过 MySQL 从节点的一个 hung 问题，对数据库连接、日志 阅读更多…

故障分析

故障分析 | MySQL 执行 Online DDL 操作报错空间不足？

在 MySQL 中执行 Online DDL 之前，需要保证在三个方面 阅读更多…

技术文章

故障分析 | MySQL 无法修改主键？原来是因为这个参数

同事咨询了一个问题，TDSQL（for MySQL）中的某张表主键需要 阅读更多…