初步认知MySQLmetadatalock(MDL)_MySQL

bitsCN.com 概述

MDL意味着DDL，一旦DDL被阻塞，那么面向该表的所有Query都会被挂起，包括Select，不过5.6作了改进，5.5可通过参数控制

假如没有mdl

会话1：mysql> select version();+------------+| version()  |+------------+| 5.1.72-log |+------------+1 row in set (0.00 sec)mysql> select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where id=1;+----+--------+| id | name   |+----+--------+|  1 | python |+----+--------+1 row in set (0.04 sec)会话2：mysql> alter table t add column comment varchar(200) default 'I use Python';Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0会话1：mysql> select * from t where id=1;Empty set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where id=1;+----+--------+--------------+| id | name   | comment      |+----+--------+--------------+|  1 | python | I use Python |+----+--------+--------------+1 row in set (0.00 sec)

与上面的不同，在5.5 MDL拉长了生命长度，与事务同生共死，只要事务还在，MDL就在，由于事务持有MDL锁，任何DDL在事务期间都休息染指，下面是个例子

会话1：mysql> select version();+------------+| version()  |+------------+| 5.5.16-log |+------------+1 row in set (0.01 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t order by id;+----+------+| id | name |+----+------+|  1 | a    ||  2 | e    ||  3 | c    |+----+------+3 rows in set (0.00 sec)会话2：mysql> alter table t add column cc char(10) default 'c lang'; <<===Hangs会话3：mysql> show processlist;+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------+| Id | User | Host      | db   | Command | Time | State                           | Info                                                  |+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------+|  2 | root | localhost | db1  | Sleep   |  191 |                                 | NULL                                                  ||  3 | root | localhost | db1  | Query   |  125 | Waiting for table metadata lock | alter table t add column cc char(10) default 'c lang' ||  4 | root | localhost | NULL | Query   |    0 | NULL                            | show processlist                                      |+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------+

mysql> show profiles;+----------+---------------+-------------------------------------------------------+| Query_ID | Duration      | Query                                                 |+----------+---------------+-------------------------------------------------------+|        1 | 1263.64100500 | alter table t add column dd char(10) default ' Elang' |+----------+---------------+-------------------------------------------------------+1 row in set (0.00 sec)mysql> show profile for query 1;+------------------------------+------------+| Status                       | Duration   |+------------------------------+------------+| starting                     |   0.000124124 || checking permissions         |   0.000015 || checking permissions         |   0.000010 || init                         |   0.000023 || Opening tables               |   0.000063 || System lock                  |   0.000068 || setup                        |   0.000082 || creating table               |   0.034159 || After create                 |   0.000185 || copy to tmp table            |   0.000309 || rename result table          | 999.999999 || end                          |   0.004457 || Waiting for query cache lock |   0.000024 || end                          |   0.000029 || query end                    |   0.000009 || closing tables               |   0.000030 || freeing items                |   0.000518 || cleaning up                  |   0.000015 |+------------------------------+------------+18 rows in set (0.00 sec)

案例

监控

lock_wait_timeout

mysql> show variables like 'lock_wait_timeout';+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| lock_wait_timeout | 31536000 |+-------------------+----------+1 row in set (0.00 sec)

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000

诊断

Deep Search

智能文献、网页检索与分析工具。AI赋能，洞悉万象，让知识检索与总结触手可及

下载

Connection #1:create table t1 (id int) engine=myisam;set @@autocommit=0;select * from t1;Connection #2:alter table t1 rename to t2; <-- Hangs 

对于InnoDB表： 

create table t3 (id int) engine=innodb;create table t4 (id int) engine=innodb;delimiter |CREATE TRIGGER t3_trigger AFTER INSERT ON t3  FOR EACH ROW BEGIN    INSERT INTO t4 SET id = NEW.id;  END;|delimiter ;

Connection #1:begin;insert into t3 values (1);

Connection #2:drop trigger if exists t3_trigger; <-- Hangsmysql> SHOW ENGINE INNODB STATUS/G;............------------TRANSACTIONS------------Trx id counter BF03Purge done for trx's n:o < BD03 undo n:o < 0History list length 82LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 4, OS thread handle 0xa7d3fb90, query id 40 localhost rootshow engine innodb status---TRANSACTION BF02, ACTIVE 38 sec2 lock struct(s), heap size 320, 0 row lock(s), undo log entries 2MySQL thread id 2, OS thread handle 0xa7da1b90, query id 37 localhost root.........

TRANSACTIONSIf this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.

会话1：mysql> show status like 'Open%tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 26    |  <==当前打开的表数量| Opened_tables | 2     |  <==已经打开的表数量+---------------+-------+2 rows in set (0.00 sec)会话2：mysql> alter table t add column Oxx char(20) default 'ORACLE';Query OK, 3 rows affected (0.05 sec)Records: 3  Duplicates: 0  Warnings: 0会话1：mysql> select * from t order by id;+----+------+--------+--------+---------+---------+-------+--------+--------+--------+--------+| id | name | cc     | dd     | EE      | ff      | OO    | OE     | OF     | OX     | Oxx    |+----+------+--------+--------+---------+---------+-------+--------+--------+--------+--------+|  1 | a    | c lang |  Elang |  Golang |  Golang | MySQL | ORACLE | ORACLE | ORACLE | ORACLE ||  2 | e    | c lang |  Elang |  Golang |  Golang | MySQL | ORACLE | ORACLE | ORACLE | ORACLE ||  3 | c    | c lang |  Elang |  Golang |  Golang | MySQL | ORACLE | ORACLE | ORACLE | ORACLE |+----+------+--------+--------+---------+---------+-------+--------+--------+--------+--------+3 rows in set (0.00 sec)mysql> show status like 'Open%tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 27    || Opened_tables | 3     |+---------------+-------+2 rows in set (0.00 sec)会话2：mysql> alter table t add column Oxf char(20) default 'ORACLE';Query OK, 3 rows affected (0.06 sec)Records: 3  Duplicates: 0  Warnings: 0会话1：mysql> show status like 'Open%tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 26    || Opened_tables | 3     |+---------------+-------+2 rows in set (0.00 sec) 

结论：

当需要对"热表"做DDL，需要特别谨慎，否则，容易造成MDL等待，导致连接耗尽或者拖垮Server