MySQL Cases-MySQL找出谁持有全局读锁

原创

姚崇

修改于 2021-09-18 11:35:17

1.1K0

修改于 2021-09-18 11:35:17

文章被收录于专栏：MySQL故障优化案例

全部关于锁文章

全局读锁https://cloud.tencent.com/developer/article/1869375
表锁 https://cloud.tencent.com/developer/article/1869546
MDLhttps://cloud.tencent.com/developer/article/1869667
MySQL找出谁持有行锁（RR级别）https://cloud.tencent.com/developer/article/1869793
MySQL找出谁持有行锁（RC级别）https://cloud.tencent.com/developer/article/1869900

找出谁持有全局读锁flush table with read lock

全局读锁通常是由flush table with read lock;这类语句添加的。在各种备份工具为了得到一致性备份，已经在具备主从复制架构的环境中做主备切换时常常使用这类语句。另外还有一种情况，可是最难排查的一种情况，就是线上系统权限约束不规范，各种人员使用的数据库账号都有RELOAD权限，都可以对数据库加全局读锁。

在MySQL5.7之前的版本中，要排查谁持有全局读锁，通常在数据库层面是很难直接查询到有用数据的（innodb_locks表也只能记录InnoDB层面的锁信息，而全局读锁是Server层面的锁，所以无法查询到）。从MySQL5.7版本开始提供了performance_schema.metadata_locks表，用来记录一些Server层的锁信息(包含全局读锁和MDL锁等)。下面通过一个示例来演示如何使用performance_schema找出谁持有全局读锁。

这里提一个额外的信息，施加flush table with read lock 的session如果在加锁的情况下session中断了，那么所施加的锁也就释放了，不需要执行unlock tables;

回归正题

首先，开启一个会话，执行加全局读锁的语句。

# 执行加锁语句
mysql> flush table with read lock;
Query OK, 0 rows affected (0.60 sec)

# 查询以下加锁线程的process id，以使与后续排查过程对应
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|            1016 |
+-----------------+
1 row in set (0.00 sec)

然后开启第二个会话，执行可能对数据在成修改的任意语句，以update操作为例：

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|            1015 |
+-----------------+
1 row in set (0.00 sec)

update sbtest1 set pad = 'xxx' where id = 21; ## 此时操作被阻塞

接下来，开启第三个会话，开始使用一些手段进行排查

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.05 sec)

mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

show engine innodb status\G 仍然找不到

从5.7开始提供了performance_schema.metadata_locks，该表记录了各种Server层的锁信息，包括全局读锁和MDL锁等信息

mysql> select * from performance_schema.metadata_locks where owner_thread_id !=sys.ps_thread_id(connection_id());
+-------------+---------------+-------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| 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        |       140488516290880 | SHARED              | EXPLICIT      | GRANTED     |        |            1041 |             42 |
| COMMIT      | NULL          | NULL        |       140488514750160 | SHARED              | EXPLICIT      | GRANTED     |        |            1041 |           2326 |
| GLOBAL      | NULL          | NULL        |       140488514986368 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     |        |            1040 |           1058 |
+-------------+---------------+-------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
3 rows in set (0.01 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           |       140488516290880 | SHARED              | EXPLICIT      | GRANTED     |        |            1041 |             42 |
| COMMIT      | NULL               | NULL           |       140488514750160 | SHARED              | EXPLICIT      | GRANTED     |        |            1041 |           2326 |
| GLOBAL      | NULL               | NULL           |       140488514986368 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     |        |            1040 |           1058 |
| TABLE       | performance_schema | metadata_locks |       140488447698320 | SHARED_READ         | TRANSACTION   | GRANTED     |        |            1042 |           1040 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
4 rows in set (0.00 sec)

持有锁的内存线程ID为1041，被阻塞的内部线程ID为1040。

如果是生产环境，综合上述信息，通过在proecss id值对应的User、Host、db信息，大致判断属于什么业务用户，找相关人员询问清楚，再进行处理。

查询全局读锁阻塞者脚本

在默认的performance_schema级别下，即可

select /* default performance_schema level*/
concat('kill ',l.id,';') as kill_command,
e.THREAD_ID,
e.event_name,
e.CURRENT_SCHEMA,
e.SQL_TEXT,
round(e.TIMER_WAIT / 1000 / 1000 / 1000 /1000,2) as "TIMER_WAIT(s)",
l.host,
l.db,
l.state,
DATE_SUB(NOW(), INTERVAL(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - e.TIMER_START/1000/1000/1000/1000 second) AS 'start_time' 
from performance_schema.events_statements_history e inner join information_schema.PROCESSLIST l 
on e.THREAD_ID = sys.ps_thread_id(l.id) 
and e.event_name = 'statement/sql/flush' 
order by e.TIMER_START; 
+--------------+-----------+---------------------+----------------+----------------------------+---------------+-----------+------+-------+----------------------------+
| kill_command | THREAD_ID | event_name          | CURRENT_SCHEMA | SQL_TEXT                   | TIMER_WAIT(s) | host      | db   | state | start_time                 |
+--------------+-----------+---------------------+----------------+----------------------------+---------------+-----------+------+-------+----------------------------+
| kill 161;    |       186 | statement/sql/flush | test           | flush table with read lock |          0.00 | localhost | test |       | 2021-05-06 13:46:46.209185 |
+--------------+-----------+---------------------+----------------+----------------------------+---------------+-----------+------+-------+----------------------------+
1 row in set (0.01 sec)

更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码