一、问题描述   
        msyql5.5版本，innodb存储引擎使用rr隔离级别。
       最近在生产环境中，遇到了一个死锁的问题，查看死锁日志如下：
        LATEST DETECTED DEADLOCK
        ------------------------
        150202 13:49:59
        *** (1) TRANSACTION:
        TRANSACTION E5AD52, ACTIVE 12 sec inserting
        mysql tables in use 1, locked 1
        LOCK WAIT 10 lock struct(s), heap size 1248, 6 row lock(s), undo log entries 2
        MySQL thread id 65009655, OS thread handle 0x7f3b862b9700, query id 799179431 192.168.10.14 root update
        insert into ebay_commodity_condition (condition_value, condition_index, category_condition_id, commodity_id, condition_id) values ('New with tags', 1000, '40288a8e48c5ffb00148ee7908aa032b', '40288a8e4b3c5193014b48d692e902a1',         '40288a8e4b3c5193014b48d6935302a2')
        *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 0 page no 55282 n bits 232 index `Commodity_id_index` of table  ebay_commodity_condition` trx id E5AD52 lock_mode X locks gap before rec insert intention waiting
        Record lock, heap no 108 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
         0: len 30; hex 343032383861393234613766313666333031346138306330326131373030; asc 40288a924a7f16f3014a80c02a1700; (total 32 bytes);
         1: len 30; hex 343032383861393234613766313666333031346138306332303061663030; asc 40288a924a7f16f3014a80c200af00; (total 32 bytes);

        *** (2) TRANSACTION:                
         TRANSACTION E5AD46, ACTIVE 12 sec inserting
        mysql tables in use 1, locked 1
        8 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 2
        MySQL thread id 65009635, OS thread handle 0x7f3b8537d700, query id 799179771 192.168.10.14 root update
        insert into ebay_commodity_condition (condition_value, condition_index, category_condition_id, commodity_id, condition_id) values ('New with tags', 1000, '40288a8e48c5ffb00148ee7908aa032b', '40288a8e4b3c5193014b48d69a1902ab',         '40288a8e4b3c5193014b48d69a8402ac')
        *** (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 0 page no 55282 n bits 232 index `Commodity_id_index` of table  ebay_commodity_condition` trx id E5AD46 lock_mode X locks gap before rec
        Record lock, heap no 108 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
         0: len 30; hex 343032383861393234613766313666333031346138306330326131373030; asc 40288a924a7f16f3014a80c02a1700; (total 32 bytes);
        1:len 30; hex 343032383861393234613766313666333031346138306332303061663030; asc 40288a924a7f16f3014a80c200af00; (total 32 bytes);

        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
       再来查看表结构：
       ebay_commodity_condition | CREATE TABLE `ebay_commodity_condition` (
          `condition_id` varchar(255) NOT NULL DEFAULT '' COMMENT '商品状况id',
          `condition_value` varchar(255) DEFAULT NULL COMMENT '状况取值',
          `condition_index` int(11) DEFAULT NULL COMMENT '状况序列号',
          `category_condition_id` varchar(255) DEFAULT NULL COMMENT '分类状况id',
          `commodity_id` varchar(255) DEFAULT NULL COMMENT '所属商品',
          PRIMARY KEY (`condition_id`),
          KEY `Commodity_id_index` (`commodity_id`),
          KEY `index_category_condition_id` (`category_condition_id`) USING BTREE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ebay商品状况'
       发现死锁可能是因为事物中的两个索引引起的。

        二、原理说明：
           MySQL在5.5.3版本引入了metadata lock                    
     他的本意是解决之前版本事务隔离特性的几个bug,但是引入的问题也不小.
     先查看下线上系统的运行的mysql版本，刚好是5.5.3之后的版本；
        mysql> select @@version;
        +----------------+
        | @@version      |
        +----------------+
        | 5.5.37-cll-lve |
        +----------------+
        1 row in set (0.00 sec)

     查看事物的隔离级别：   
    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)
    
    innodb可重复读隔离级别,也就是说在一个事务中,无论运行多少次查询,结果都必须是一致的.
    (innodb不仅支持可重复读,并且使用间隙锁在可重复读级别避免了幻读,当然这也带来了很多问题..)
    所以它记录的不是每个查询语句的LSN,而是事务第一个语句发生时的LSN,无论第一个语句是查询,还是修改.
    innodb在可重复读的级别下,查询用事务开始时的LSN应用MVCC,与Oracle不同的是,innodb查询回滚段中小于事务开始的LSN的数据版本,
    而oracle查询回滚段中小于语句SCN的数据版本.
    也就是说,同样都是MVCC,oracle是语句级的,innodb是事务级的

    这里有一个问题,按说事务包括查询是因为可重复读隔离级别的需要,但是innodb读提交隔离级别同样也将查询作为了事务的一部分.
    可能是因为架构或者代码实现层面的问题吧.
    不管怎么样,Innodb就是这么做了.

   然后再说说metadata lock
   在5.5.3之前,metadata lock是语句级的,这实际上破坏了事务的一致性.
   比如一个事务,在可重复读隔离级别,运行两次查询,居然结果不一致.
   
   正是因为metadata lock是语句级造成的问题,
    在两个查询的间隔,另外一个会话执行了truncate table.
    所以再次运行查询,没有任何结果.