mysql全局锁定_MySQL 快速定位令人头疼的全局锁-爱可生

此方法仅适用于 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)

定位到锁会话 ID 直接 kill 该会话即可。

方法2：利用 events_statements_history 视图

此方法适用于 MySQL 5.6 以上版本，启用 performance_schema.eventsstatements_history(5.6 默认未启用，5.7 默认启用)，该表会 SQL 历史记录执行，如果请求太多，会自动清理早期的信息，有可能将上锁会话的信息清理掉。过程如下。

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)

方法3：利用 gdb 工具

如果上述两种都用不了或者没来得及启用，可以尝试第三种方法。利用 gdb 找到所有线程信息，查看每个线程中持有全局锁对象，输出对应的会话 ID，为了便于快速定位，我写成了脚本形式。也可以使用 gdb 交互模式，但 attach mysql 进程后 mysql 会完全 hang 住，读请求也会受到影响，不建议使用交互模式。

#!/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}

但实际环境可能会比较复杂，用 gdb 可能也无法获得你想要的信息，是不是就没辙了。

方法4：show processlist

如果备份程序使用的特定用户执行备份，如果是 root 用户备份，那 time 值越大的是持锁会话的概率越大，如果业务也用 root 访问，重点是 state 和 info 为空的，这里有个小技巧可以快速筛选，筛选后尝试 kill 对应 ID，再观察是否还有 wait global read lock 状态的会话。

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

mysql>show processlist

如果以上方法全部无效，最后释放终极大招...

方法5：重启试试！

如果你有更好的方法，可以留言分享。

关键字：爱可生、MySQL数据库、数据库运维管理、开源数据库解决方案