您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 我们最担心的还是MDL其可能导致的阻塞问题

我们最担心的还是MDL其可能导致的阻塞问题

发布时间:2019-12-31 00:03编辑:威尼斯官方网站登录浏览(50)

    在线上进行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                   |
    +----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
    8 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    |
    +------+------+
    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)
    

    可以看到,虽然是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 |
    +------+------+------+
    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/*!*/;
    

     

    如果会话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 |
    +----+------+-----------+----------+---------+------+-------------------+---------------------------+
    3 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。

     图片 1

     

    为了提高数据库的并发度,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锁,直到事务结束才释放。

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:我们最担心的还是MDL其可能导致的阻塞问题

    关键词: