MySQL表结构怎么变更Metadata Lock
发布时间：2022-01-18 11:34:38 所属栏目：MySql教程 来源：互联网
导读：本篇文章为大家展示了MySQL表结构怎样变更Metadata Lock，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。 想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生，一般都是进行alter操作时被堵住了，
|
本篇文章为大家展示了MySQL表结构怎样变更Metadata Lock，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。 想必玩过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锁，直到事务结束才释放。 （编辑：拼字网 - 核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐