MySQL 排查全局锁

本文实验参考自《千金良方：MySQL性能优化法则金字塔》performance_schema相关章节
数据库版本：5.7.36单机

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.36-log |
+------------+
1 row in set (0.00 sec)

session 1:

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

session 2:

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

session 3:

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               9 |
+-----------------+
1 row in set (0.00 sec)mysql> show processlist;
+----+------+-----------+-------------+---------+------+----------+------------------+
| Id | User | Host      | db          | Command | Time | State    | Info             |
+----+------+-----------+-------------+---------+------+----------+------------------+
|  7 | root | localhost | backup_test | Sleep   |   37 |          | NULL             |
|  8 | root | localhost | backup_test | Sleep   |    9 |          | NULL             |
|  9 | root | localhost | backup_test | Query   |    0 | starting | show processlist |
+----+------+-----------+-------------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

session 1:开启全局读锁

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

session 2：修改任意一个表的数据

mysql> use backup_test
mysql> select * from full_backup;
+----+----------+-------+
| id | name     | class |
+----+----------+-------+
|  1 | wanger   |     9 |
|  2 | lisan    |     1 |
|  3 | zhangsan |     6 |
|  4 | si       |     2 |
|  5 | wangmazi |     4 |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> update full_backup set class=3 where id=1; //发现被阻塞

session 3:

mysql> show processlist;
+----+------+-----------+-------------+---------+------+------------------------------+-------------------------------------------+
| Id | User | Host      | db          | Command | Time | State                        | Info                                      |
+----+------+-----------+-------------+---------+------+------------------------------+-------------------------------------------+
|  7 | root | localhost | backup_test | Sleep   |  141 |                              | NULL                                      |
|  8 | root | localhost | backup_test | Query   |   49 | Waiting for global read lock | update full_backup set class=3 where id=1 |
|  9 | root | localhost | backup_test | Query   |    0 | starting                     | show processlist                          |
+----+------+-----------+-------------+---------+------+------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

发现processlist_id=8的会话在等一个全局读锁
使用常规的排查锁的表查询都为空

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 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;查询事务这块儿也没有什么有用的信息

------------
TRANSACTIONS
------------
Trx id counter 6939
Purge done for trx's n:o < 6939 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421678777934336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421678777933424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421678777932512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421678777931600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

查询performance_schema.metadata_locks表发现详情如下

mysql> select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G;
*************************** 1. row ***************************OBJECT_TYPE: GLOBAL	#globalOBJECT_SCHEMA: NULLOBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140202765115488LOCK_TYPE: SHARED   #共享锁LOCK_DURATION: EXPLICIT	#显示获取LOCK_STATUS: GRANTED  #已获取锁SOURCE:OWNER_THREAD_ID: 33		#thread_id=33OWNER_EVENT_ID: 2604
*************************** 2. row ***************************OBJECT_TYPE: COMMIT	#commitOBJECT_SCHEMA: NULLOBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140202765018160LOCK_TYPE: SHARED	#共享锁LOCK_DURATION: EXPLICIT	#显示获取LOCK_STATUS: GRANTED	#已获取锁SOURCE:OWNER_THREAD_ID: 33		#thread_id=33OWNER_EVENT_ID: 2724
*************************** 3. row ***************************OBJECT_TYPE: GLOBAL	#globalOBJECT_SCHEMA: NULLOBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140202564139616LOCK_TYPE: INTENTION_EXCLUSIVE	#意向锁LOCK_DURATION: STATEMENT			#通过语句LOCK_STATUS: PENDING				#为获取到锁，等待SOURCE:OWNER_THREAD_ID: 34					#thread_id=34OWNER_EVENT_ID: 402
3 rows in set (0.00 sec)

通过上面的查询结果可以看到，thread_id=34的线程在等待thread_id=33的线程持有的锁。

mysql> select * from performance_schema.events_statements_current where thread_id=33\G;
*************************** 1. row ***************************THREAD_ID: 33EVENT_ID: 2598END_EVENT_ID: 2728EVENT_NAME: statement/sql/flushSOURCE:TIMER_START: 523558101594285000TIMER_END: 523558104347773000TIMER_WAIT: 2753488000LOCK_TIME: 0SQL_TEXT: flush table with read lockDIGEST: 172f9471494a101656e6cb75da3e2726DIGEST_TEXT: FLUSH TABLE WITH READ LOCKCURRENT_SCHEMA: backup_testOBJECT_TYPE: NULLOBJECT_SCHEMA: NULLOBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: NULLMYSQL_ERRNO: 0RETURNED_SQLSTATE: 00000MESSAGE_TEXT: NULLERRORS: 0WARNINGS: 0ROWS_AFFECTED: 0ROWS_SENT: 0ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0CREATED_TMP_TABLES: 0SELECT_FULL_JOIN: 0SELECT_FULL_RANGE_JOIN: 0SELECT_RANGE: 0SELECT_RANGE_CHECK: 0SELECT_SCAN: 0SORT_MERGE_PASSES: 0SORT_RANGE: 0SORT_ROWS: 0SORT_SCAN: 0NO_INDEX_USED: 0NO_GOOD_INDEX_USED: 0NESTING_EVENT_ID: NULLNESTING_EVENT_TYPE: NULLNESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)

通过如上查询，可以看到thread_id=33的线程执行的是FLUSH TABLE WITH READ LOCK锁定的，

mysql> select processlist_id,thread_id from performance_schema.threads where thread_id=33 or thread_id=34;
+----------------+-----------+
| processlist_id | thread_id |
+----------------+-----------+
|              7 |        33 |
|              8 |        34 |
+----------------+-----------+
2 rows in set (0.01 sec)

查询到线程对应的processlist_id，跟业务进行沟通是否可以进行kill等操作

kill 7;
kill掉之后，session 2的update立马执行成功

select sys.ps_thread_id(processlist_id);  --根据processlist_id查出线程号的函数
select connection_id(); --查找当前连接会话的processlist_id;
select sys.ps_thread_id(connection_id());  --查找当前连接会话的线程号锁排查相关表
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;