快速定位MySQL的MDL锁等待
演示环境MySQL版本mysql5.7.22
查看是否持有MDL锁等待:
特别说明:
通过sys.schema_table_lock_waits视图 可以查看当前连接线程的MDL锁等待信息，显示哪些会话被MDL锁阻塞，是谁阻塞了这些会话，数据来源 performance_schema下的threads,metadata_locks,events_statements_current表，此视图是在MySQL5.7.9中新增的
使用视图sys.schema_table_lock_waits 查看MDL锁等待，首先需要开启与MDL锁等待事件相关的instruments:
'tidb03' root@localhost 17:21:58 (none)>update performance_schema.setup_instruments set ENABLED='no',timed='no' where name like 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
'tidb03' root@localhost 17:14:11 (none)>call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
+-----------------------+
| summary |
+-----------------------+
| Enabled 1 instruments |
+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
显示的1代表 MDL锁等待事件相关的instruments未开启
显示的0代表 MDL锁等待事件相关的instruments开启：
update performance_schema.setup_instruments set ENABLED='yes',timed='yes' where name like 'wait/lock/metadata/sql/mdl';
'tidb03' root@localhost 17:22:51 (none)>select * from performance_schema.setup_instruments where name like 'wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES | YES |
+----------------------------+---------+-------+
'tidb03' root@localhost 16:53:54 (none)>call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
+-----------------------+
| summary |
+-----------------------+
| Enabled 0 instruments |
+-----------------------+
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
会话一：发起一个update更新记录的事务
'tidb03' root@localhost 16:46:37 test001>select * from test001;
+----+----------+----------+---------------------+
| id | username | password | create_time |
+----+----------+----------+---------------------+
| 1 | 大米 | abc123 | 2021-09-19 13:31:07 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)
'tidb03' root@localhost 16:46:40 test001>
'tidb03' root@localhost 16:46:40 test001>
'tidb03' root@localhost 16:46:40 test001>
'tidb03' root@localhost 16:46:41 test001>begin;
Query OK, 0 rows affected (0.00 sec)
'tidb03' root@localhost 16:46:47 test001>update test001 set username='小白' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
会话二：发起aler table 操作
'tidb03' root@localhost 16:47:22 test001>alter table test001 add index idx_username(username);
会话三: 查看视图sys.schema_table_lock_waits定位MDL锁等待
'tidb03' root@localhost 16:51:08 (none)>select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
object_schema: test001 #发生MDL锁等待的schema名称
object_name: test001 #正在等待MDL锁的表名称
waiting_thread_id: 206617 #正在等待MDL锁的线程id
waiting_pid: 206592 #正在等待MDL锁的 processlist id
waiting_account: root@localhost #正在等待MDL锁与线程相关的账户名称
waiting_lock_type: EXCLUSIVE #被阻塞的线程正在等待的MDL锁类型
waiting_lock_duration: TRANSACTION #该字段来自元数据锁子系统中的锁定事件，值为:STATEMENT,TRANSACTION,EXPLICIT;STATEMENT和TRANSACTION表示语句或者事务结束时会释放的锁。
XPLICIT表示在语句和事务结束时会被保留，需要显式释放的锁:例如flush table with read lock 获取的全局的锁
waiting_query: alter table test001 add index idx_username(username) ##正在等待MDL锁线程对应的sql
waiting_query_secs: 206 ##正在等待MDL锁的语句已经等待了多长时间
waiting_query_rows_affected: 0 ##受等待MDL锁语句影响的数据行数,此字段来自performance_schema.events_statements_current表,此表记录的是语句事件,
如果语句是多表连结查询的话,则整体sql语句可能已经执行了一份部分的DML语句，所以即使该sql被当前其他线程阻塞了，被阻塞线程的这个字段的数值也可能是大于0的
waiting_query_rows_examined: 0 ## 原理同上
blocking_thread_id: 206617 ##持有MDL锁的线程id
blocking_pid: 206592 ##持有MDL锁的processlist id
blocking_account: root@localhost
blocking_lock_type: SHARED_UPGRADABLE ##持有MDL锁的锁类型
blocking_lock_duration: TRANSACTION ##和字段waiting_lock_duration解释相同
sql_kill_blocking_query: KILL QUERY 206592 ##生成的KILL 持有MDL锁的查询语句
sql_kill_blocking_connection: KILL 206592 ##生成的KILL 持有MDL锁的对应的会话语句
*************************** 2. row ***************************
object_schema: test001
object_name: test001
waiting_thread_id: 206617
waiting_pid: 206592
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table test001 add index idx_username(username)
waiting_query_secs: 206
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 206628
blocking_pid: 206603
blocking_account: root@localhost
blocking_lock_type: SHARED_WRITE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 206603
sql_kill_blocking_connection: KILL 206603
2 rows in set (0.01 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
