欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-08 20:21:00
前言 想必玩过mysql的人对waiting for table metadata lock肯定不会陌生,一般都是进行alter操作时被堵住了,导致了我们在show pr...

前言

想必玩过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锁,直到事务结束才释放。

总结

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