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

bglmmz 2016-02-19 11:31:17

数据是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

...全文

763 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

bglmmz 2016-02-22

打赏
举报

经过2天运行，没有再出现deadlock的问题了。思路就是上面说的，在delete前，先检查下有没有满足条件的记录，没有的话，就不delete了，这样就避免了gap lock问题了。

if(this.tideHourlyMapper.listByTideId(tide.getTideId()) !=null && 长度>0){
    this.tideHourlyMapper.deleteByTideId(tide.getTideId());
}
this.tideHourlyMapper.insertBatch(tideHourlyList);

bglmmz 2016-02-19

打赏
举报

多谢楼上的回答，是叶金荣大虾吗？这个是叶金荣大虾群里，叶老大回答我的。哈哈。。受那个群里的大虾提醒，看了一个帖子 http://narcissusoyf.iteye.com/blog/1637309 会不会是同样的原因呢？我照着这个思路修改了代码，放到现网去跑了，过几天来更新结果。

test_234 2016-02-19

打赏
举报

死锁发生在 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的写入，这样相邻的概率就很低了，不容易再发生死锁