MySQL表结构变更,不可不知的Metadata Lock

时间:2019-05-11 09:51来源:计算机教程
在线上进行DDL操作时,相对于其可能带来的系统负载,其实,我们最担心的还是MDL其可能导致的阻塞问题。 前言 一旦DDL操作因获取不到MDL被阻塞,后续其它针对该表的其它操作都会被

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

前言

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

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

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                   |
 ---- ----------------- ----------- ----------- --------- ------ --------------------------------- ------------------------------------ 
8 rows in set (0.00 sec)

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

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

一旦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)

MDL引入的背景

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

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

MDL引入的背景

 

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

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

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

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

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

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

session1> select * from t1;
 ------ ------ 
| id  | name |
 ------ ------ 
|    1 | a    |
|    2 | b    |
 ------ ------ 
2 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 |
 ------ ------ ------ 
2 rows in set (0.00 sec)
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隔离级别,但在开启事务的情况下,第二次查询却没有结果。

可以看到,虽然是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> 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)
session1> select * from slowtech.t1;
 ------ ------ ------ 
| id   | name | c1   |
 ------ ------ ------ 
|    1 | a    | NULL |
 ------ ------ ------ 
1 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/*!*/;

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

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

# 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执行的是alter table操作,其依旧会被阻塞,阻塞时间受innodb_lock_wait_timeout参数限制。

编辑:计算机教程 本文来源:MySQL表结构变更,不可不知的Metadata Lock

关键词: