初步认知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';  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.000124 || 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

诊断

Connection #1:create table t1 (id int) engine=myisam;set @@autocommit=0;select * from t1;Connection #2:alter table t1 rename to t2; 
对于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;  SHOW ENGINE INNODB STATUS/G;............------------TRANSACTIONS------------Trx id counter BF03Purge done for trx's n:o 
TRANSACTIONSIf this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.
SELECT * FROM INNODB_LOCK_WAITS
SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS)
SELECT INNODB_LOCKS.* FROM INNODB_LOCKS JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID)
SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT'

与table cache的关系
会话1：mysql> show status like 'Open%tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 26    |   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
						bitsCN.com