Code Bye

救命：一个看似简单的批量更新的死锁问题～

码拜
数据是mysql 5.6 表引擎是innoDB，DAO是mybatis3。
表结构如下：
一个主表：TIDE，记录每个港口每天潮汐的概述；
另一个潮汐分时记录表：TIDE_HOURLY，记录每个TIDE_ID对应的分时潮汐信息 
create table TIDE
(
  TIDE_ID     INT(10) not null AUTO_INCREMENT comment "记录ID",
  PORT_CODE   VARCHAR(20) not null comment "港口代码",
  TIDE_DATE   Date comment "潮汐日期，格式:yyyy-MM-dd",
  TIDE_BASE   INT(5) comment "潮高基准面",  
  TIDE_RANGE  INT(5) comment "潮差（高潮位-低潮位），单位，cm",  
  PRIMARY KEY (TIDE_ID),
  INDEX (TIDE_DATE)
)
  comment="潮汐表"
  AUTO_INCREMENT = 1
  ENGINE=InnoDB;
create table TIDE_HOURLY
(
  TIDE_HOURLY_ID  INT(10) not null AUTO_INCREMENT comment "记录ID",
  TIDE_ID         INT(10) not null comment "潮汐记录ID",
  TIME_POINT      DateTime not null comment "时间点，格式:yyyy-MM-dd HH:mm",
  TREND           VARCHAR(10) not null comment "涨潮还是退潮。UP:涨潮 / DOWN:退潮",
  HEIGHT          INT(5) not null comment "潮高，单位:cm",
  TIDE_DIFFER     INT(5) comment "当前周期的潮差",
  PAST_DIFFER     INT(5) comment "已经发生的变化",
  RATIO           INT(2) comment "几分潮",
  HOURLY_INDEX         INT(5) COMMENT "指数,1~100",
  HOURLY_INDEX_TEXT    VARCHAR(50) COMMENT "说明",
  PRIMARY KEY (TIDE_HOURLY_ID),
  CONSTRAINT FOREIGN KEY (TIDE_ID) REFERENCES TIDE (TIDE_ID) ON DELETE CASCADE,
  INDEX (TIME_POINT)
)
  comment="潮汐分时表"
  AUTO_INCREMENT = 1
  ENGINE=InnoDB;

业务场景是：
从外部同步潮汐的分时数据到TIDE_HOURLY表，
读取数据时，按TIDE_ID分组，每得到一个TIDE_ID的分组数据，就开一个线程去更新TIDE_HOURLY，调用业务方法updateTideHourly()去更新TIDE_HOURLY表，
在业务方法中，采取先delete from TIDE_HOURLY where tide_id=?，再用批量插入insert into TIDE_HOURLY (x,x,x) values(),(),()的方式。一个业务方法就是一个事务。
业务方法中的代码片段如下

this.tideHourlyMapper.deleteByTideId(tide.getTideId());
this.tideHourlyMapper.insertBatch(tideHourlyList);

所以，表TIDE_HOURLY是多线程并发更新的（或说业务方法是被多线程并发调用的）。
目前经常碰到死锁问题。死锁的部分日志如下，请大虾帮忙分析下，该怎么样解决。
叩首多谢～！

*** (1) TRANSACTION:
TRANSACTION 271247847, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 46, OS thread handle 0x7f41b8c2a700, query id 981660 localhost 127.0.0.1 root update
insert into tide_hourly (TIDE_ID, TIME_POINT, TREND,
    HEIGHT, TIDE_DIFFER, PAST_DIFFER,
    RATIO, HOURLY_INDEX, HOURLY_INDEX_TEXT)
    values
      
      (31473, "2016-03-04 00:00:00", "DOWN",
      69, 101, 95,
      9, 30, "不适合")
     , 
      (31473, "2016-03-04 01:00:00", "DOWN",
      65, 101, 99,
      9, 30, "不适合")
     , 
      (31473, "2016-03-04 01:47:00", "UP",
      63, 42, 0,
      0, 20, "不适合")
     , 
      (31473, "2016-03-04 02:00:00", "UP",
      64, 42, 1,
      0, 20, "不适合")
     , 
      (31473, "2016-03-04 03:00:00", "UP",
      71, 42, 8,
      1, 75, "适合")
     , 
      (31473, "2016-03-04 04:00:00", "UP",
      86, 42, 23,
      5, 90, "非常适合")
     , 
      (31473, "2016-03-04 05:00:00", "UP",
      99, 42, 36,
      8, 90, "非常适合")
     , 
      (31473, "2016-03-04 06:00:00", "UP",
      105, 4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 567 page no 7132 n bits 816 index `TIDE_ID` of table `tide1_0`.`tide_hourly` trx id 271247847 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 644 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80007af4; asc   z ;;
 1: len 4; hex 800f3b07; asc   ; ;;
*** (2) TRANSACTION:
TRANSACTION 271247844, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 17, OS thread handle 0x7f41c03cd700, query id 981662 localhost 127.0.0.1 root update
insert into tide_hourly (TIDE_ID, TIME_POINT, TREND,
    HEIGHT, TIDE_DIFFER, PAST_DIFFER,
    RATIO, HOURLY_INDEX, HOURLY_INDEX_TEXT)
    values
      
      (31474, "2016-03-04 00:00:00", "DOWN",
      81, 127, 124,
      9, 30, "不适合")
     , 
      (31474, "2016-03-04 00:54:00", "UP",
      78, 69, 0,
      0, 20, "不适合")
     , 
      (31474, "2016-03-04 01:00:00", "UP",
      78, 69, 0,
      0, 20, "不适合")
     , 
      (31474, "2016-03-04 02:00:00", "UP",
      82, 69, 4,
      0, 20, "不适合")
     , 
      (31474, "2016-03-04 03:00:00", "UP",
      90, 69, 12,
      1, 75, "适合")
     , 
      (31474, "2016-03-04 04:00:00", "UP",
      100, 69, 22,
      3, 50, "不适合")
     , 
      (31474, "2016-03-04 05:00:00", "UP",
      113, 69, 35,
      5, 90, "非常适合")
     , 
      (31474, "2016-03-04 06:00:00", "UP",
      127, 69, 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 567 page no 7132 n bits 816 index `TIDE_ID` of table `tide1_0`.`tide_hourly` trx id 271247844 lock_mode X locks gap before rec
Record lock, heap no 644 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80007af4; asc   z ;;
 1: len 4; hex 800f3b07; asc   ; ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 567 page no 7132 n bits 816 index `TIDE_ID` of table `tide1_0`.`tide_hourly` trx id 271247844 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 644 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80007af4; asc   z ;;
 1: len 4; hex 800f3b07; asc   ; ;;
*** WE ROLL BACK TRANSACTION (2)
2016-02-18 01:50:01 7f41b895f700InnoDB: transactions deadlock detected, dumping detailed information.
2016-02-18 01:50:01 7f41b895f700
*** (1) TRANSACTION:
TRANSACTION 271247909, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 0x7f41b8a63700, query id 981880 localhost 127.0.0.1 root update
insert into tide_hourly (TIDE_ID, TIME_POINT, TREND,
    HEIGHT, TIDE_DIFFER, PAST_DIFFER,
    RATIO, HOURLY_INDEX, HOURLY_INDEX_TEXT)
    values
      
      (31463, "2016-03-04 00:00:00", "DOWN",
      104, 142, 120,
      8, 40, "不适合")
     , 
      (31463, "2016-03-04 01:00:00", "DOWN",
      85, 142, 139,
      9, 30, "不适合")
     , 
      (31463, "2016-03-04 01:36:00", "UP",
      82, 144, 0,
      0, 20, "不适合")
     , 
      (31463, "2016-03-04 02:00:00", "UP",
      83, 144, 1,
      0, 20, "不适合")
     , 
      (31463, "2016-03-04 03:00:00", "UP",
      97, 144, 15,
      1, 75, "适合")
     , 
      (31463, "2016-03-04 04:00:00", "UP",
      121, 144, 39,
      2, 40, "不适合")
     , 
      (31463, "2016-03-04 05:00:00", "UP",
      146, 144, 64,
      4, 65, "较适合")
     , 
      (31463, "2016-03-04 06:00:00", "UP",
      1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 567 page no 7132 n bits 1232 index `TIDE_ID` of table `tide1_0`.`tide_hourly` trx id 271247909 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 224 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80007ae8; asc   z ;;
 1: len 4; hex 800f394b; asc   9K;;
*** (2) TRANSACTION:
TRANSACTION 271247911, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 57, OS thread handle 0x7f41b895f700, query id 981882 localhost 127.0.0.1 root update
insert into tide_hourly (TIDE_ID, TIME_POINT, TREND,
    HEIGHT, TIDE_DIFFER, PAST_DIFFER,
    RATIO, HOURLY_INDEX, HOURLY_INDEX_TEXT)
    values
      
      (31461, "2016-03-04 00:00:00", "UP",
      17, 51, 1,
      0, 20, "不适合")
     , 
      (31461, "2016-03-04 01:00:00", "UP",
      23, 51, 7,
      1, 75, "适合")
     , 
      (31461, "2016-03-04 02:00:00", "UP",
      32, 51, 16,
      3, 50, "不适合")
     , 
      (31461, "2016-03-04 03:00:00", "UP",
      43, 51, 27,
      5, 90, "非常适合")
     , 
      (31461, "2016-03-04 04:00:00", "UP",
      53, 51, 37,
      7, 100, "非常适合")
     , 
      (31461, "2016-03-04 05:00:00", "UP",
      62, 51, 46,
      9, 50, "不适合")
     , 
      (31461, "2016-03-04 06:00:00", "UP",
      66, 51, 50,
      9, 50, "不适合")
     , 
      (31461, "2016-03-04 06:44:00", "DOWN",
      67, 14, 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 567 page no 7132 n bits 1232 index `TIDE_ID` of table `tide1_0`.`tide_hourly` trx id 271247911 lock_mode X locks gap before rec
Record lock, heap no 224 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80007ae8; asc   z ;;
 1: len 4; hex 800f394b; asc   9K;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 567 page no 7132 n bits 1232 index `TIDE_ID` of table `tide1_0`.`tide_hourly` trx id 271247911 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 224 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80007ae8; asc   z ;;
 1: len 4; hex 800f394b; asc   9K;;
*** WE ROLL BACK TRANSACTION (2)
2016-02-18 01:50:02 7f41b8c2a700InnoDB: transactions deadlock detected, dumping detailed information.
2016-02-18 01:50:02 7f41b8c2a700
解决方案

100

死锁发生在 tide_hourly 表的 TIDE_ID 索引上，这是一个普通索引，所以有next-key lock
2个事务都要对同一个位置加锁，所以死锁了
早一点的事务加的是 insert intention lock 锁，晚一点的事务加的是 next-key lock
建议的话：
tide_id 相邻的数据，可以都发送到同一个队列里合并写入，你可以看到几个死锁事件里，都是相邻的tide_id；
或，不要把相邻tide_id的数据分别分发到2个线程里写入，可以采用随机分配的方式发给2个线程写入，例如一个负责tide_id=1的写入，一个负责tide_id=1000的写入，这样相邻的概率就很低了，不容易再发生死锁
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明救命：一个看似简单的批量更新的死锁问题～！