概述
随着5.5.3引入MDL,更多的Query被“Waiting for table metadata lock”给'炕'了
SHOW PROCESSLIST的输出也有之前的"Locked"变得粒度更加细的'Waiting for table metadata lock'
引入MDL,当需要访问、修改表结构时,都需要对元数据上锁(读/写)
MDL在Server层,保护表数据结构,而非数据本身。Server比之前版本繁忙多了
随着5.5.3引入MDL,更多的Query被“Waiting for table metadata lock”给'炕'了
SHOW PROCESSLIST的输出也有之前的"Locked"变得粒度更加细的'Waiting for table metadata lock'
引入MDL,当需要访问、修改表结构时,都需要对元数据上锁(读/写)
MDL在Server层,保护表数据结构,而非数据本身。Server比之前版本繁忙多了
MDL意味着DDL,一旦DDL被阻塞,那么面向该表的所有Query都会被挂起,包括Select,不过5.6作了改进,5.5可通过参数控制
假如没有MDL
在5.1版本,MDL的生命随语句的结束而释放,并非在事务提交后结束,意味着事务不是真正的隔离
因为同一条Query在2次执行期间倘若被插入了DDL,那么得到的结果将会不同,下面是个例子
会话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;
+----+------+-----------+------+---------+------+---------------------------------+-----------------------