replace into 引发的死锁问题

线上某个库发生死锁，如下：

1. ------------------------

LATEST DETECTED DEADLOCK

------------------------

140417 11:45:12

*** (1) TRANSACTION:

TRANSACTION 216AA52F, ACTIVE 0 sec updating or deleting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 4

MySQL thread id 6240192, OS thread handle 0x2b53a11c2700, query id 671755133 10.97.51.53 gds_fare update

REPLACE INTO gds_policy_binding_07   (   dep_city, arr_city, agent_id, fare_type, fare_id, binding_status, order_num,   gmt_create, gmt_modified      )   VALUES         (    'WUH',    'WNZ',    1553,    1,    81431003,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    81431004,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    83832489,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    83836367,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    81431005,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    83832485,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    81431006,    1,    0,    NOW(),    NOW()   )

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 789 page no 11573 n bits 496 index `policy_binding_unique` of table `gds_fare`.`gds_policy_binding_07` trx id 216AA52F lock_mode X waiting

*** (2) TRANSACTION:

TRANSACTION 216AA530, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 494

mysql tables in use 1, locked 1

5 lock struct(s), heap size 1248, 7 row lock(s), undo log entries 5

MySQL thread id 6240166, OS thread handle 0x2b537ddaf700, query id 671755135 10.97.24.28 gds_fare update

REPLACE INTO gds_policy_binding_07   (   dep_city, arr_city, agent_id, fare_type, fare_id, binding_status, order_num,   gmt_create, gmt_modified      )   VALUES         (    'WUH',    'WNZ',    1553,    1,    81429153,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    83832490,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    83832487,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    83832478,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    81429154,    1,    0,    NOW(),    NOW()   )      ,      (    'WUH',    'WNZ',    1553,    1,    81431004,    1,    0,    NOW(),    NOW()   )

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 789 page no 11573 n bits 496 index `policy_binding_unique` of table `gds_fare`.`gds_policy_binding_07` trx id 216AA530 lock_mode X

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 789 page no 11573 n bits 496 index `policy_binding_unique` of table `gds_fare`.`gds_policy_binding_07` trx id 216AA530 lock_mode X waiting

*** WE ROLL BACK TRANSACTION (1)

问题是应用断在并发执行该replace语句的时候会发生死锁，死锁的原因是唯一建冲突了。

但是单条DML语句怎么会发生死锁呢？？？

参照mysql ref ，执行replace语句时， 如果旧行和新行（要插入的数据）主键或者唯一健冲突的话，replace语句相当于2条sql：1. delete 就行;2. insert新数据; （如果想验证，可以在表上建insert，delete的触发器来测试，会发现replace语句会触发insert和delete的触发器执行）

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”.

参见： http://dev.mysql.com/doc/refman/5.5/en/replace.html  

             http://dev.mysql.com/doc/refman/5.5/en/insert.html

所以发生死锁的原因就明朗了，那么解决的方法就是用insert ... on duplicate key update...语句来替换replace语句，问题的到解决。

但是insert ... on duplicate key update语句的性能比replace稍微差一点。

参见：http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks/