+---------------+-------+

1 row in set (0.00 sec)

?

mysql> update uu_test set sex='M' where id=1;

?

然后在B终端中执行一条 DDL:

?

alter table uu_test add index(userId);

结果你会发现B终端中的 该条DDL会一直被阻塞，在A查看：

?

mysql> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+

| Id | User | Host ?| db ? | Command | Time | State ? | Info ?|

+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+

| ?1 | root | localhost | aazj | Query ? |0 | init| show processlist ?|

| ?2 | root | localhost | aazj | Query ? | ?351 | Waiting for table metadata lock | alter table uu_test add index(userId) |

| ?4 | root | localhost | NULL | Sleep ? | 2900 | | NULL ?|

+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+

3 rows in set (0.00 sec)

?

发现是被 A 中为提交事物持有的 metadata lock 所阻塞。可以看到未提交事务的危害有多大！！！！！！它会一直持有 metadata lock.

?

14. 实验五：

?

在终端A中执行一个时间较长的DDL:

?

mysql> alter table uu_test add index(user_homeTel);

Query OK, 0 rows affected (12.86 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

然后在DDL执行完之前，马上在B终端中执行：

?

mysql> update uu_test set user_Sex='M' where userId=1;

Query OK, 0 rows affected (0.14 sec)

Rows matched: 256 ?Changed: 0 ?Warnings: 0

我们看到DDL的执行期间，并没有阻塞其它事务中的update, select, delete 等等语句。也就是说DDL语句对 metadata lock 的持有是瞬时的，并不会再其执行期间一直持有。这点一定要注意。这就是 DDL 语句和 事务还有lock table xxx write语句的区别：DDL语句并不会再执行期间一直持有metadata lock，而是在执行的开始瞬时持有metadata lock，马上释放；而事务会在事务期间一直持有metadata lock；lock table xxx write语句也会一直持有metadata lock指定unlock语句发出。

?

15. 实验六(DDL最大的危害)：

?

首先在A终端中设置autocommit=off; 然后随便执行一个select/update/delete语句，一直不提交，占用 metadata lock:

?

mysql> select userId,user_Sex from uu_test limit 2;

+--------+----------+

| userId | user_Sex |

+--------+----------+

| ?1 | M|

| ?2 | F|

+--------+----------+

2 rows in set (0.09 sec)

?

然后在终端B中执行一条 DDL，很明显它会被上面的 metadata lock 阻塞：

然后我们在C终端中对同一个表uu_test执行随便一条：select/update/delete语句，神奇的情况发生！！！！！

可以看到C终端中的对同表uu_test一条select语句尽然被阻塞了！！！！！！

?

看下终端D中的show processlist:

可以看到：DDL 语句 alter table uu_test add index(user_QQ) 被 未提交的事务阻塞，然后DDL语句进而阻塞了其后事务中所有的针对同表uu_test的任何语句。以为他们都要获得 metadada lock。这应该是DDL语句的最大危害之处。同理可以推断：长事物长时间持有 metadata lock, 会阻塞其它DDL语句对metada lock的互斥申请，然后该DDL语句阻塞其后所有的涉及到该database objects的所有语句。这里按照我们的正常逻辑，C中的语句应该不会被阻塞才对啊？难道是为了防止DDL语句对metadata lock的申请，发生饥饿现象。所以才阻塞了C中的语句。或者对metadata lock的申请维持了一个FIFO的队列？

?

然后我们在A终端中执行提交：commit; 然后 B 中的DDL语句立即获得 metadata lock，然后又马上释放；然后C中的 select 也成功获得metadata lock. B中的DDL语句因为执行时间长，它会在C执行完之后，才执行完成。这也说明了DDL语句对metadata lock的持有是瞬时的，并不会在执行期间一直持有(不然C也不会再B之前执行完成)。

?

16. 总结：

?

1）metadata lock保护的是元数据，也就是database object(表结构等元数据)，而不是表中的数据；

?

2）每一个在运行中的事务涉及到的database object，都必须获得metadata lock，然后在事务结束时进行释放(parepare语句除外)；

?

3）DDL 语句以及lock table xxx write 和 事务 对 metadata lock 存在互斥争用；

?

普通的update,select,delete并不会在metadata lock上争用，也就是多个运行中的事物可以同时持有同一个database object上的metadata lock.

?

4）mysql终端默认是autocommit=on，千万不要将mysql工具默认修改成autocommit=off； 而JDBC连接默认是 autocommit=off的；

?