mysql之神奇的死锁及解决思路

network

   一、问题描述   
　　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>
　　         MySQL thread>
　　         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>
　　         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>
　　         MySQL thread>
　　         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>
　　         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.
　　所以再次运行查询,没有任何结果.