应用-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找出谁持有全局读锁。

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

# 执行加锁语句
mysql> flush table with read lock;
Query OK, 0 rows affected (0.60 sec)
 
# 查询以下加锁线程的process id，以使与后续排查过程对应
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              4  |
+-----------------+
1 row in set (0.00 sec)

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

mysql> select * from a limit 1\G
*************************** 1. row ***************************
 id: 1
  k: 2523
  c: 66732467966-31166435533-24160857174-47700857015-95528431613-72420310257-00729886733-14082739850-34910057147-23412468124
pad: 69449313224-10645335122-68404111484-06065403356-59643719520
1 row in set (0.00 sec)
 
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)
 
update a set id = '111111' where id = 1; ## 此时操作被阻塞

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

mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+-----------------------------------------+
| Id | User | Host      | db   | Command | Time | State                        | Info                                    |
+----+------+-----------+------+---------+------+------------------------------+-----------------------------------------+
|  4 | root | localhost | wjj  | Sleep   |  121 |                              | NULL                                    |
|  5 | root | localhost | wjj  | Query   |   12 | Waiting for global read lock | update a set id = '111111' where id = 1 |
|  6 | root | localhost | NULL | Query   |    0 | starting                     | show processlist                        |
+----+------+-----------+------+---------+------+------------------------------+-----------------------------------------+
3 rows in set (0.00 sec)

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

查询processlist信息，这里只能看到process id为5的线程State为Waiting for global read lock；表示正在等待全局读锁

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.05 sec)
 
Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.

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

Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release.

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        |       140473091514592 | SHARED              | EXPLICIT      | GRANTED     |        |              30 |           1594 |
| COMMIT      | NULL          | NULL        |       140473080812752 | SHARED              | EXPLICIT      | GRANTED     |        |              30 |           1727 |
| GLOBAL      | NULL          | NULL        |       140473079550816 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     |        |              31 |           1132 |
+-------------+---------------+-------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
3 rows in set (0.00 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           |       140473091514592 | SHARED              | EXPLICIT      | GRANTED     |        |              30 |           1594 |
| COMMIT      | NULL               | NULL           |       140473080812752 | SHARED              | EXPLICIT      | GRANTED     |        |              30 |           1727 |
| GLOBAL      | NULL               | NULL           |       140473079550816 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     |        |              31 |           1132 |
| TABLE       | performance_schema | metadata_locks |       140473280831248 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              32 |            342 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
4 rows in set (0.00 sec)

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

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

mysql> select THREAD_ID,PROCESSLIST_ID,THREAD_OS_ID from performance_schema.threads;
+-----------+----------------+--------------+
| THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
+-----------+----------------+--------------+
|         1 |           NULL |         1560 |
..........
|        24 |           NULL |         1584 |
|        25 |           NULL |         1583 |
|        26 |           NULL |         1587 |
|        27 |              1 |         1588 |
|        30 |              4 |         1593 |
|        31 |              5 |         2636 |
|        32 |              6 |         2651 |
+-----------+----------------+--------------+
28 rows in set (0.00 sec)

查询全局读锁阻塞者脚本

select /*查询全局读锁阻塞者脚本*/
       p.THREAD_ID,
       l.ID as processlist_id,
       p.event_name,
       round(p.TIMER_WAIT / 1000 / 1000 / 1000 /1000,2) as "TIMER_WAIT(s)",
       p.CURRENT_SCHEMA,
       p.SQL_TEXT,
       l.host,
       l.DB,
       l.STATE,
       ml.LOCK_TYPE,
       ml.LOCK_DURATION,
       ml.LOCK_STATUS,
       ml.OWNER_EVENT_ID
from performance_schema.events_statements_current p
inner join performance_schema.metadata_locks ml
inner join information_schema.PROCESSLIST l
on ml.OWNER_THREAD_ID = p.THREAD_ID
and sys.ps_thread_id(l.id) = ml.OWNER_THREAD_ID
and ml.OWNER_THREAD_ID != sys.ps_thread_id(connection_id());

posted @ 2021-04-12 16:27 叶雷锋。阅读(315) 评论(0) 收藏举报

刷新页面返回顶部

应用-flush table with read lock 全局读锁定位

找出谁持有全局读锁

查询全局读锁阻塞者脚本

公告