初步认知MySQLmetadatalock(MDL) - 数据库编程

概述

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

初步认知MySQLmetadatalock(MDL)(一)