欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

初步认知MySQLmetadatalock(MDL)_MySQL

程序员文章站 2022-05-29 22:58:04
...
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
相关标签: mysql