t a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.
?
自动提交模式(mysql命令行工具默认是自动提交模式)，语句一执行完马上就释放metadata lock，因为他是自动提交的单语句事务。
?
8.
?
Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.
?
事务中的metadata lock直到事务结束才释放，但是有一个特例：事务中的prepare(一般用在存储过程中的动态语句)语句一执行完马上释放对应的metadata lock.
?
9.
?
Before MySQL 5.5, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.
?
MySQL 5.5 引入了metadata lock，取代了之前版本中的等价物。
?
但是metadata lock和她之前的等价物有一个区别：metadata lock直到事务结束才释放，而她的等价物是语句执行完就马上释放。metadata lock这样做的目的是为了保证 binary log 顺序的正确。
?
10. 实验一(lock table xxx write 语句; 实验环境Centos下的mysql5.6.27)
?
首先在终端A执行：
?
mysql> lock table cats write;
Query OK, 0 rows affected (0.01 sec)
然后在终端B执行：
?
select * from cats;
你会发现被阻塞了。
?
然后在终端A中执行：
?
mysql> show processlist;
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
| Id | User | Host ?| db ?| Command | Time | State ? | Info ? |
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
| ?1 | root | localhost | ngx_lua | Query ? | 2940 | Waiting for table metadata lock | select * from cats |
| ?2 | root | localhost | ngx_lua | Query ? |0 | init| show processlist ? |
| ?3 | root | localhost | NULL| Sleep ? | 2913 | | NULL ? |
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
3 rows in set (0.00 sec)
?
你可以看到 select * from cats 被阻塞的原因是：Waiting for table metadata lock
?
而那个 Sleep 中的正是 lock table cats wirte 语句。它也持有了 cats 表上的 metadata lock 的，排斥其它任何事务对该metadata lock的申请。
?
到这里你可能会问：说好的MVCC呢？说好的 select 语句可以使用MVCC，不需要用到锁呢？
?
?
然后我们执行 kill 3 试图将 lock table cats write 这个session杀掉，期望他是否metadata lock:
?
?
mysql> kill 3
-> ;
Query OK, 0 rows affected (0.00 sec)
?
mysql> show processlist;
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
| Id | User | Host ?| db ?| Command | Time | State ? | Info ? |
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
| ?1 | root | localhost | ngx_lua | Query ? | 3605 | Waiting for table metadata lock | select * from cats |
| ?2 | root | localhost | ngx_lua | Query ? |0 | init| show processlist ? |
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
2 rows in set (0.00 sec)
?
mysql> unlock table cats;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cats' at line 1
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
?
mysql> show processlist;
+----+------+-----------+---------+---------+------+-------+
