mysql中的元数据锁

为了保证事务在执行的过程中，不会因为ddl语句导致事务失败，mysql中出现了元数据锁。下面简单测试下

session A:

test>begin
    -> ;
Query OK, 0 rows affected (0.01 sec)


.test>insert into t2 values('mmm',77);
Query OK, 1 row affected (0.01 sec)

sessionB:

drop table t2;

会话B挂起来了，看下锁的信息,在innodb_locks及innodb_lock_wait中没有看到相关的信息，但是在show engine innodb status中能看到对应的元数据锁信息。

test>show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2016-04-19 23:21:49 7fc180261700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 36 srv_active, 0 srv_shutdown, 540015 srv_idle
srv_master_thread log flush and writes: 540051
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 93
OS WAIT ARRAY INFO: signal count 95
Mutex spin waits 36, rounds 454, OS waits 12
RW-shared spins 56, rounds 1710, OS waits 57
RW-excl spins 2, rounds 739, OS waits 24
Spin rounds per wait: 12.61 mutex, 30.54 RW-shared, 369.50 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 2489
Purge done for trx's n:o < 2489 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 58, OS thread handle 0x7fc180230700, query id 1498 192.168.0.101 user1 Waiting for table metadata lock
drop table t2
---TRANSACTION 2478, ACTIVE 225 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 57, OS thread handle 0x7fc180261700, query id 1504 192.168.0.101 user1 init
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
203 OS file reads, 667 OS file writes, 498 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2365241, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1801269
Log flushed up to   1801269
Pages flushed up to 1801269
Last checkpoint at  1801269
0 pending log writes, 0 pending chkp writes
179 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 128974
Buffer pool size   65528
Free buffers       65220
Database pages     307
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 178, created 129, written 463
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 307, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   8191
Free buffers       8152
Database pages     38
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32, created 6, written 156
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 38, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   8191
Free buffers       8183
Database pages     8
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 4, written 23
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   8191
Free buffers       8183
Database pages     8
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 4, written 23
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   8191
Free buffers       8119
Database pages     72
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 4, written 35
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 72, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   8191
Free buffers       8108
Database pages     83
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 62, created 21, written 92
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 83, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   8191
Free buffers       8117
Database pages     74
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 70, written 99
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 74, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   8191
Free buffers       8175
Database pages     16
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 12, written 18
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   8191
Free buffers       8183
Database pages     8
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 8, written 17
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 29496, id 140466865665792, state: sleeping
Number of rows inserted 13, updated 9, deleted 0, read 128
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

有时候，因为客户端的应用没有正确释放资源，会导致ddl等待元数据锁，比如下面的：

mysql> show full processlist ;
+---------+------+---------------------+--------+---------+------+---------------------------------+----------------------------------------+
| Id      | User | Host                | db     | Command | Time | State                           | Info                                   |
+---------+------+---------------------+--------+---------+------+---------------------------------+----------------------------------------+
| 1875027 | root | localhost           | baixyu | Query   |  243 | Waiting for table metadata lock | create index idx_date on ts(purchased) |
| 1877495 | root | xxx.xxx.xxx.21:60143 | baixyu | Sleep   |  553 |                                 | NULL                                   |
| 1882616 | root | localhost           | NULL   | Query   |    0 | starting                        | show full processlist                  |
+---------+------+---------------------+--------+---------+------+---------------------------------+---------------------

我在21上启动多线程插入数据，手工停止脚本，随后在表上创建索引的时候，提示等待元数据锁，processlist中没有任何的命令操作。只有一个从21上过来sleep状态的连接，kill掉这个连接后，在执行就正常了