mysql replace into 锁_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的触发器执行)

REPLACEworks exactly likeINSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEYor aUNIQUEindex, the old row is deleted before the new row is inserted. SeeSection 13.2.5, “INSERT Syntax”.

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

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