mysql之神奇的死鎖及解決思路

   一、問題描述   
        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.
    所以再次執行查詢,沒有任何結果.