一、元数据锁等待
1、元数据锁的定义
MDL 全称为 metadata lock，即元数据锁，一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。元数据锁适用对象包含：table、schema、procedures, functions, triggers, scheduled events、tablespaces 。
有以下特点：
- 元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请MDL锁。
- DML操作需要metadata读锁，DDL操作需要metadata写锁，加锁过程是系统自动控制，无法直接干预。
- 读锁和写锁之间相互阻塞，写锁和写锁之间互相阻塞，读锁和读锁之间不会产生阻塞（因为DML之间的阻塞是通过innodb引擎的行锁来进行的）。
- DDL 的写锁请求优先级高于 DML的读锁。
- MDL锁保护的是事务级别的，只有等到事务结束后才会释放。
- 同时操作多个表时，按出现的先后顺序逐个表去获取元数据锁。
- 单个DML语句，在语句执行完后会立即释放元数据锁。
- DDL 语句、LOCK TABLES 和其他类似语句按名称顺序获取锁，对于隐式使用的表(例如外键关系中也必须锁定的表)可能会以不同的顺序获取锁。
加入元数据锁的目的：
- 事务隔离问题：比如在RR隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求。
- 数据复制问题：比如会话A执行了多条更新语句期间，会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update，这样会导致复制出现错误。
2、快速查看是否有元数据锁等待产生
show processlist或select * from information_schema.processlist，查看结果集中STATE列是否有Waiting for table metadata lock显示，有则为产生了元数据锁等等。
[5.7.37-log]>select * from information_schema.processlist;
+----+------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------+
| 10 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.processlist |
| 5 | root | localhost | performance_schema | Sleep | 164 | | NULL |
| 9 | root | localhost | test | Query | 22 | Waiting for table metadata lock | alter table t1 add addr varchar(100) |
| 8 | root | localhost | test | Sleep | 24 | | NULL |
+----+------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------+
4 rows in set (0.00 sec)
3、开启并监控元数据锁信息
1）永久生效，在配置文件的[mysqld]节点下，加入开启参数
[mysqld]
performance_schema_instrument='wait/lock/metadata/sql/mdl=ON'
2) 临时生效，数据库重启后，变回默认值
update performance_schema.setup_instruments set ENABLED='Yes',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
3）监控元数据锁信息：都记录在performance_schema.metadata_locks表中
[5.7.37-log]>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 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | 140398722883648 | SHARED_READ | TRANSACTION | GRANTED | | 28 | 8 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
1 row in set (0.00 sec)
4、模拟元数据锁等待
--session 1
[5.7.37-log]>begin;
Query OK, 0 rows affected (0.00 sec)
[5.7.37-log]>select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | NULL |
| 4 | NULL |
| 5 | NULL |
+------+------+
3 rows in set (0.00 sec)
--session 2
[5.7.37-log]>alter table t1 add addr varchar(100);
--session 3 # 查看元数据锁等待
select a.object_schema locked_schema,
a.object_name locked_table,
"metadata lock" locked_type,
c.processlist_id waiting_processlist_id,
c.processlist_time waiting_time,
c.processlist_info waiting_query,
c.processlist_state waiting_state,
d.processlist_id blocking_processlist_id,
d.processlist_time blocking_time,
e.sql_text blocking_query,
concat('kill ',d.processlist_id, ';') sql_kill_blocking_connection
from performance_schema.metadata_locks a join performance_schema.metadata_locks b
on a.object_schema=b.object_schema and a.object_name=b.object_name
and a.lock_status='pending' and b.lock_status='granted'
and a.owner_thread_id<>b.owner_thread_id
join performance_schema.threads c
on a.owner_thread_id=c.thread_id
join performance_schema.threads d
on b.owner_thread_id=d.thread_id
join performance_schema.events_statements_current e
on b.owner_thread_id = e.thread_id\G
*************************** 1. row ***************************
locked_schema: test
locked_table: t1
locked_type: metadata lock
waiting_processlist_id: 5
waiting_time: 13
waiting_query: alter table t1 add addr varchar(100)
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 4
blocking_time: 42
blocking_query: select * from t1
sql_kill_blocking_connection: kill 4
1 row in set (0.00 sec)
二、行锁等待
有锁阻塞时，可以通过show engine innodb status命令，及表information_schema.innodb_lock_waits进行查看堵塞信息，也可以通过innodb_lock_waits表结合其他表，查看阻塞与被阻塞的进程信息
show engine innodb status：
[5.7.37-log]>show engine innodb status\G
------------
TRANSACTIONS
------------
Trx id counter 641553
Purge done for trx's n:o < 641542 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421875474013808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 641552, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 140399866201856, query id 75 localhost root updating
update t1 set name='abcd' where id=1
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 148 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t1` trx id 641552 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000500; asc ;;
1: len 6; hex 00000009b52b; asc +;;
2: len 7; hex c7000002530110; asc S ;;
3: len 4; hex 80000001; asc ;;
4: SQL NULL;
------------------
---TRANSACTION 641542, ACTIVE 1491 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 4, OS thread handle 140399866472192, query id 30 localhost root
information_schema.innodb_lock_waits：
[5.7.37-log]>select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 641553 | 641553:148:3:3 | 641542 | 641542:148:3:3 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
查看阻塞信息：
select
wt.thread_id waiting_thread_id,
wt.processlist_id waiting_processlist_id,
wt.processlist_time waiting_time,
wt.processlist_info waiting_query,
bt.thread_id blocking_thread_id,
bt.processlist_id blocking_processlist_id,
bt.processlist_time blocking_time,
c.sql_text blocking_query,
concat('kill ',bt.processlist_id, ';') sql_kill_blocking_connection
from information_schema.innodb_lock_waits l join information_schema.innodb_trx b
on b.trx_id = l.blocking_trx_id
join information_schema.innodb_trx w
on w.trx_id = l.requesting_trx_id
join performance_schema.threads wt
on w.trx_mysql_thread_id=wt.processlist_id
join performance_schema.threads bt
on b.trx_mysql_thread_id=bt.processlist_id
join performance_schema.events_statements_current c
on bt.thread_id=c.thread_id\G
*************************** 1. row ***************************
waiting_thread_id: 30
waiting_processlist_id: 5
waiting_time: 27
waiting_query: update t1 set name='abcd' where id=1
blocking_thread_id: 29
blocking_processlist_id: 4
blocking_time: 1681
blocking_query: select * from t1 for update
sql_kill_blocking_connection: kill 4;
1 row in set, 1 warning (0.00 sec)
查看引起堵塞的事务SQL
select
wt.thread_id waiting_thread_id,
wt.processlist_id waiting_processlist_id,
wt.processlist_time waiting_time,
wt.processlist_info waiting_query,
bt.thread_id blocking_thread_id,
bt.processlist_id blocking_processlist_id,
bt.processlist_time blocking_time,
c.sql_text blocking_query,
concat('kill ', bt.processlist_id, ';') sql_kill_blocking_connection
from
information_schema.innodb_lock_waits l
join information_schema.innodb_trx b on b.trx_id = l.blocking_trx_id
join information_schema.innodb_trx w on w.trx_id = l.requesting_trx_id
join performance_schema.threads wt on w.trx_mysql_thread_id = wt.processlist_id
join performance_schema.threads bt on b.trx_mysql_thread_id = bt.processlist_id
join (
select a.thread_id,group_concat(a.sql_text order by a.event_id SEPARATOR ';\n') sql_text
from
(
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_current
union
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_history
) a
join (
select
thread_id,
max(event_id) max_event_id,
max(case when event_name = 'statement/sql/begin' then event_id else 0 end) max_begin_id,
max(case when event_name = 'statement/sql/rollback' then event_id else 0 end) max_rollback_id,
max(case when event_name = 'statement/sql/commit' then event_id else 0 end) max_commit_id
from
(
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_current
union
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_history
) tmp
group by thread_id
) b on a.thread_id = b.thread_id
where
a.event_id = b.max_event_id
or (
a.event_id >= max_begin_id
and max_begin_id > greatest(max_rollback_id, max_commit_id)
)
group by a.thread_id
) c on bt.thread_id = c.thread_id
\G
三、未提交事务
select
a.trx_id,
a.trx_state,
a.trx_started,
b.processlist_time,
b.thread_id,
b.processlist_id,
b.processlist_user,
b.processlist_host,
b.processlist_db,
c.sql_text,
concat('kill ',b.processlist_id, ';') sql_kill_connection
from information_schema.innodb_trx a join performance_schema.threads b
on a.trx_mysql_thread_id=b.processlist_id
join performance_schema.events_statements_current c
on b.thread_id=c.thread_id\G
*************************** 1. row ***************************
trx_id: 641560
trx_state: RUNNING
trx_started: 2022-10-30 10:49:22
processlist_time: 74
thread_id: 29
processlist_id: 4
processlist_user: root
processlist_host: localhost
processlist_db: test
sql_text: select * from t1 for update
sql_kill_connection: kill 4;
*************************** 2. row ***************************
trx_id: 641561
trx_state: LOCK WAIT
trx_started: 2022-10-30 10:50:28
processlist_time: 8
thread_id: 30
processlist_id: 5
processlist_user: root
processlist_host: localhost
processlist_db: test
sql_text: update t1 set name='abcd' where id=1
sql_kill_connection: kill 5;
2 rows in set (0.00 sec)
查看完整未提交的事务SQL
select
a.trx_id,
a.trx_state,
a.trx_started,
b.processlist_time,
b.thread_id,
b.processlist_id,
b.processlist_user,
b.processlist_host,
b.processlist_db,
c.sql_text,
concat('kill ',b.processlist_id, ';') sql_kill_connection
from information_schema.innodb_trx a join performance_schema.threads b
on a.trx_mysql_thread_id=b.processlist_id
join (
select a.thread_id,group_concat(a.sql_text order by a.event_id SEPARATOR ';\n') sql_text
from (
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_current
union
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_history
) a
join (
select thread_id,
max(event_id) max_event_id,
max(case when event_name = 'statement/sql/begin' then event_id else 0 end) max_begin_id,
max(case when event_name = 'statement/sql/rollback' then event_id else 0 end) max_rollback_id,
max(case when event_name = 'statement/sql/commit' then event_id else 0 end) max_commit_id
from (
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_current
union
select thread_id,event_id,event_name,sql_text
from performance_schema.events_statements_history
) tmp
group by thread_id
) b on a.thread_id = b.thread_id
where a.event_id = b.max_event_id
or (
a.event_id >= max_begin_id
and max_begin_id > greatest(max_rollback_id, max_commit_id)
)
group by a.thread_id
) c
on b.thread_id=c.thread_id
\G
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
