概述
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.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; <-- 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; <-- Hangs mysql> SHOW ENGINE INNODB STATUS\G; .... .... .... ------------ TRANSACTIONS ------------ Trx id counter BF03 Purge done for trx's n:o < BD03 undo n:o < 0 History list length 82 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 4, OS thread handle 0xa7d3fb90, query id 40 localhost root show engine innodb status ---TRANSACTION BF02, ACTIVE 38 sec 2 lock struct(s), heap size 320, 0 row lock(s), undo log entries 2 MySQL thread id 2, OS thread handle 0xa7da1b90, query id 37 localhost root ... ... ...
TRANSACTIONS If 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
會話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