杰晶网络

MySQL表结构变更你不可不知的Metadata Lock详解
数据库 2024/11/24 佚名
3 2

前言

想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生，一般都是进行alter操作时被堵住了，导致了我们在show processlist 时，看到线程的状态是在等metadata lock。本文会对MySQL表结构变更的Metadata Lock进行详细的介绍。

在线上进行DDL操作时，相对于其可能带来的系统负载，其实，我们最担心的还是MDL其可能导致的阻塞问题。

一旦DDL操作因获取不到MDL被阻塞，后续其它针对该表的其它操作都会被阻塞。典型如下，如阻塞稍久的话，我们会看到Threads_running飙升，CPU告警。

mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State  | Info  |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 122 | Waiting on empty queue | NULL  |
| 9 | root | localhost | NULL | Sleep | 57 |   | NULL  |
| 12 | root | localhost | employees | Query | 40 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
| 13 | root | localhost | employees | Query | 35 | Waiting for table metadata lock | select * from slowtech.t1 |
| 14 | root | localhost | employees | Query | 30 | Waiting for table metadata lock | select * from slowtech.t1 |
| 15 | root | localhost | employees | Query | 19 | Waiting for table metadata lock | select * from slowtech.t1 |
| 16 | root | localhost | employees | Query | 10 | Waiting for table metadata lock | select * from slowtech.t1 |
| 17 | root | localhost | employees | Query | 0 | starting  | show processlist  |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)

如果发生在线上，无疑会影响到业务。所以，一般建议将DDL操作放到业务低峰期做，其实有两方面的考虑，1. 避免对系统负载产生较大影响。2. 减少DDL被阻塞的概率。 

MDL引入的背景

MDL是MySQL 5.5.3引入的，主要用于解决两个问题，

RR事务隔离级别下不可重复读的问题

如下所示，演示环境，MySQL 5.5.0。

session1> begin;
Query OK, 0 rows affected (0.00 sec)

session1> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
rows in set (0.00 sec)

session2> alter table t1 add c1 int;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

session1> select * from t1;
Empty set (0.00 sec)

session1> commit;
Query OK, 0 rows affected (0.00 sec)

session1> select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
+------+------+------+
rows in set (0.00 sec)

可以看到，虽然是RR隔离级别，但在开启事务的情况下，第二次查询却没有结果。

主从复制问题

包括主从数据不一致，主从复制中断等。

如下面的主从数据不一致。

session1> create table t1(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

session1> begin;
Query OK, 0 rows affected (0.00 sec)

session1> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

session2> truncate table t1;
Query OK, 0 rows affected (0.46 sec)

session1> commit;
Query OK, 0 rows affected (0.35 sec)

session1> select * from t1;
Empty set (0.00 sec)

再来看看从库的结果

session1> select * from slowtech.t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
row in set (0.00 sec)

看看binlog的内容，可以看到，truncate操作记录在前，insert操作记录在后。

# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;

# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT/*!*/;

# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1,'a')
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT/*!*/;

如果会话2执行的是drop table操作，还会导致主从中断。

有意思的是，如果会话2执行的是alter table操作，其依旧会被阻塞，阻塞时间受innodb_lock_wait_timeout参数限制。

mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State  | Info   |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL  | show processlist  |
| 58 | root | localhost | slowtech | Sleep | 1062 |   | NULL   |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
rows in set (0.00 sec)

MDL的基本概念

首先，看看官方的说法，

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session.

The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.

A metadata lock on a table prevents changes to the table's structure.

This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

从上面的描述可以看到，

1. MDL出现的初衷就是为了保护一个处于事务中的表的结构不被修改。

2. 这里提到的事务包括两类，显式事务和AC-NL-RO（auto-commit non-locking read-only）事务。显式事务包括两类：1. 关闭AutoCommit下的操作，2. 以begin或start transaction开始的操作。AC-NL-RO可理解为AutoCommit开启下的select操作。

3. MDL是事务级别的，只有在事务结束后才会释放。在此之前，其实也有类似的保护机制，只不过是语句级别的。

需要注意的是，MDL不仅仅适用于表，同样也适用于其它对象，如下表所示，其中，"等待状态"对应的是"show processlist"中的State。

MySQL表结构变更你不可不知的Metadata Lock详解 

为了提高数据库的并发度，MDL被细分为了11种类型。

  • MDL_INTENTION_EXCLUSIVE
  • MDL_SHARED
  • MDL_SHARED_HIGH_PRIO
  • MDL_SHARED_READ
  • MDL_SHARED_WRITE
  • MDL_SHARED_WRITE_LOW_PRIO
  • MDL_SHARED_UPGRADABLE
  • MDL_SHARED_READ_ONLY
  • MDL_SHARED_NO_WRITE
  • MDL_SHARED_NO_READ_WRITE
  • MDL_EXCLUSIVE

常用的有MDL_SHARED_READ,MDL_SHARE D_WRITE及MDL_EXCLUSIVE，其分别用于SELECT操作，DML操作及DDL操作。其它类型的对应操作可参考源码sql/mdl.h。

对于MDL_EXCLUSIVE，官方的解释是，

/*
An exclusive metadata lock.
A connection holding this lock can modify both table's metadata and data.
No other type of metadata lock can be granted while this lock is held.
To be used for CREATE/DROP/RENAME TABLE statements and for execution of
certain phases of other DDL statements.
*/

简而言之，MDL_EXCLUSIVE是独占锁，在其持有期间是不允许其它类型的MDL被授予，自然也包括SELECT和DML操作。

这也就是为什么DDL操作被阻塞时，后续其它操作也会被阻塞。

关于MDL的补充

1. MDL的最大等待时间由lock_wait_timeout参数决定，其默认值为31536000（365天）。在使用工具进行DDL操作时，这个值就不太合理。事实上，pt-online-schema-change和gh-ost对其就进行了相应的调整，其中，前者60s，后者3s。

2. 如果一个SQL语法上有效，但执行时报错，如，列名不存在，其同样会获取MDL锁，直到事务结束才释放。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。

标签：
metadata,mysqllock,mysql表结构
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
www.imxmx.com 杰晶网络
8,675无损音乐
1,324高清电影
213破解软件
120,141站长资源

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新动态

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网