时间:2021-05-24
前言
想必玩过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: 0session1> 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=0SET 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=0SET 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=0SET TIMESTAMP=1531567950;truncate table t1;# at 7417#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422COMMIT;# at 7444#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0SET 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=0SET TIMESTAMP=1531567944;insert into t1 values(1,'a');# at 7611#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421COMMIT;如果会话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。
为了提高数据库的并发度,MDL被细分为了11种类型。
常用的有MDL_SHARED_READ,MDL_SHARE D_WRITE及MDL_EXCLUSIVE,其分别用于SELECT操作,DML操作及DDL操作。其它类型的对应操作可参考源码sql/mdl.h。
对于MDL_EXCLUSIVE,官方的解释是,
简而言之,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锁,直到事务结束才释放。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对的支持。
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
Java导出oracle表结构实例详解最近用到的,因为plsql是收费的,不让用,找了很多方法终于发现了这个。核心语句SELECTDBMS_METADATA.G
  关于网站建设这些是你不可不知的知识点  想要建设一个优秀的网站,不能什么都不知道就盲目的开始建站,网站建设的流程,以
亚马逊开店攻略,这些亚马逊开店禁止的行为你不可不知,在亚马逊上开店什么最重要,当然是账户安全,但是卖家们在运营的过程中却出现不小心会触碰到一些亚马逊上禁止的行为
MySQL复制表详解如果我们需要完全的复制MySQL的数据表,包括表的结构,索引,默认值等。如果仅仅使用CREATETABLE...SELECT命令,是无法实现
最近因为工作需要才解除到队列服务,不可不说HTTPSQS是个非常高效的一个服务应用。先介绍一些队列的原理和应用:队列(Queue)又称先进先出表(FirstIn