mysql 元数据锁

mysql主机规划中，因为系统是从oracle迁移过来的，为了减少数据库切换给应用带来不必要的麻烦，很多配置都按照oracle来做修改，在RC模式、autocommit关闭情况下，在对一张表创建主键的时候一直hang住，

mysql> alter table PROD_SPEC  add constraint PK_PROD_SPEC primary key (PROD_SPEC_ID);
该表只有600多条数据，一直hang了十几分钟。查看processlist，寻找是否有事件阻塞了它：

mysql> show processlist;
+----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host      | db    | Command | Time | State                           | Info                                                                          | Rows_sent | Rows_examined |
+----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------------------------+-----------+---------------+
| 62 | root | localhost | crmdb | Sleep   |  114 |                                 | NULL                                                                          |         1 |           669 |
| 63 | root | localhost | crmdb | Query   |   92 | Waiting for table metadata lock | alter table PROD_SPEC  add constraint PK_PROD_SPEC primary key (PROD_SPEC_ID) |         0 |             0 |
| 64 | root | localhost | crmdb | Query   |    0 | init                            | show processlist                                                              |         0 |             0 |
+----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------------------------+-----------+---------------+
3 rows in set (0.00 sec)

发现该条创建主键语句一直wait，等待一个元数据锁，那么究竟是哪个sql，哪个回话阻塞了它呢:

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 319977
                 trx_state: RUNNING
               trx_started: 2014-08-21 22:40:18
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 62
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.05 sec)

事务状态为runing，查询是哪个进程，哪个sql干的：

mysql> select * from performance_schema. events_statements_current\G
*************************** 1. row ***************************
              THREAD_ID: 90
               EVENT_ID: 29
           END_EVENT_ID: 29
             EVENT_NAME: statement/sql/select
                 SOURCE: mysqld.cc:976
            TIMER_START: 43538900202992000
              TIMER_END: 43538901067652000
             TIMER_WAIT: 864660000
              LOCK_TIME: 277000000
               SQL_TEXT: select count(1) from prod_spec
                 DIGEST: b8de7bac3fea58d13adbf91cb0e80d0b
            DIGEST_TEXT: SELECT COUNT (?) FROM `prod_spec` 
         CURRENT_SCHEMA: crmdb
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 669
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL

kill掉该进程之后创建主键语句成功

mysql> kill 62;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table PROD_SPEC  add constraint PK_PROD_SPEC primary key (PROD_SPEC_ID);
Query OK, 0 rows affected (34 min 10.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

为什么一个已经结束的查询会阻塞ddl语句呢，这与mysql5.5版本以上的metadata locking机制有关系。

查找metadata locking相关消息，mysql在5.5.3版本引入了元数据锁机制，从5.5.3开始DDL语句以一个隔离的事务行为方式执行元数据的修改。也就是说，任何已经开始的事务将一直持有表的元数据锁直到事务提交。由于开始的事务会持有事务关联的所有表的元数据锁，所以任何DDL操作在前面的事务提交前是不能够执行的。

引入MDL后，主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。

在RC模式、autocommit关闭情况下，dba习惯性的select一个表，即使查询结束了，该回话还持有元数据锁，其他session在做对表、index、colum、主键等的增、删、改都会被柱塞。