概述
假如没有MDL
会话1： mysql> select version(); +------------+ | version() | +------------+ | ⑤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)
会话1： mysql> select version(); +------------+ | version() | +------------+ | ⑤⑤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 | +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------+
回滚了会话1的事务后,我们看下alter主要被谁炕了
mysql> show profiles; +----------+---------------+-------------------------------------------------------+ | Query_ID | Duration | Query | +----------+---------------+-------------------------------------------------------+ | 1 | 126③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
show open tables：当前打开的表,与flush tables结合,能判断表的活性
诊断
对于MyISAM表：
当发现Query由于"Waiting for table metadata lock"而挂起等待MyISAM表,这是因为该表已经被包含在一个未提交的事务,无论其是否有意而且常见的锁追踪技巧此时也没有作用,比如InnoDB Lock Monitor, InnoDB Status, mysqladmin debug output, INFORMATION_SCHEMA等事实上,在select一张表之前先SET AUTOCOMMIT=0,那么之后,直到该事务提交或结束后其它DDL才能够得到该表的控制权
测试：
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表：
倘若在InnoDB表遇见此问题,我们能够用SHOW ENGINE INNODB STATUS,优先查看transactions部分
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
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解释：
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.
除此之外,我们还可以利用information_schema,以下是几个有用SQL：① 锁等待
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的关系
先明白table cache所解决的问题域：fd(文件描述符)打开/关闭太过频繁导致资源消耗那么它是如何解决的?tc通过cache所有打开的fd,当有新的连接请求时不需重新打开,结束后也不用关闭
DDL操作终究会被阻塞,即使table cache含有其所需的fd.MySQL认为,tc旧条目必须失效访问该表的DDL操作必须重新打开fd,下面是个测试
会话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)
结论：
参考资料：
By 迦叶
Good Luck
以上就是土嘎嘎小编为大家整理的初步认知MySQL metadata lock_MDL)相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！