概念描述

mysql最常见的表级锁有3类，包括：

• 全局读锁
• MDL锁
• 表级锁

此类锁不易定位，到了mysql5.7才有了performance_schema.metadata_locks表，来记录这些锁信息。下面说明它们的排查方法：

测试验证

全局读锁 一般由flush table with read lock语句产生，解锁语句是unlock tables，被阻塞的sql线程等待类型为Waiting for global read lock。

mysql默认未记录server层的mdl锁监控，先开启：

#开启MDL锁监控
mysql> update performance_schema.setup_instruments set ENABLED='YES' where name like 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from performance_schema.setup_instruments where name like 'wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES     | NO    |
+----------------------------+---------+-------+
1 row in set (0.00 sec)

在session1设置全局读锁，session2更新一条记录，在session3中定位锁：

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

#session2
mysql> begin;
mysql> update t1 set name='' where aid=1;
#阻塞中...

#session3
#先尝试innodb层的锁相关视图是否能查到相关信息，结果没有任何信息。
mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.11 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)

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 2154
Purge done for trx's n:o < 2154 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283320765647512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283320765651872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283320765651000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283320765650128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283320765649256, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283320765648384, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283320765646640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

#使用metadata_locks视图查看相关信息，可以查到相关锁信息。
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        |         1845934596576 | SHARED              | EXPLICIT      | GRANTED     |        |              53 |              4 |
| COMMIT      | NULL          | NULL        |         1845934596672 | SHARED              | EXPLICIT      | GRANTED     |        |              53 |              4 |
| GLOBAL      | NULL          | NULL        |         1845934599648 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     |        |              45 |             58 |
+-------------+---------------+-------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+