表结构

CREATE TABLE `student_task` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增 id',
  `studentId` int(11) NOT NULL COMMENT '学生 id',
  `episodeId` int(11) unsigned NOT NULL COMMENT '课程 id',
  `ticketId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'ticket id',
  `lessonId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '班课 id',
  `prestudyTaskStatus` int(11) unsigned NOT NULL COMMENT '预习任务状态',
  `prestudyTaskNum` int(11) unsigned NOT NULL COMMENT '预习任务数量',
  `rolePlayTaskStatus` int(11) unsigned NOT NULL COMMENT '角色扮演任务状态',
  `rolePlayTaskNum` int(11) unsigned NOT NULL COMMENT '角色扮演任务数量',
  `homeworkTaskStatus` int(11) unsigned NOT NULL COMMENT '作业任务状态',
  `homeworkTaskNum` int(11) unsigned NOT NULL COMMENT '作业任务数量',
  `animationTaskStatus` int(11) unsigned NOT NULL COMMENT '动画任务状态',
  `animationTaskNum` int(11) unsigned NOT NULL COMMENT '动画任务数量',
  `replayTaskStatus` int(11) unsigned NOT NULL COMMENT '回放任务状态',
  `replayTaskdNum` int(11) unsigned NOT NULL COMMENT '回放任务数量',
  `createdTime` bigint(20) NOT NULL COMMENT '创建时间',
  `updatedTime` bigint(20) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `studentId_lessonId_episodeId` (`studentId`,`lessonId`,`episodeId`),
  KEY `episodeId` (`episodeId`),
  KEY `ticketId` (`ticketId`),
  KEY `lessonId` (`lessonId`)
) ENGINE=InnoDB AUTO_INCREMENT=458301510 DEFAULT CHARSET=utf8mb4

隔离级别 RR

死锁日志
2020-01-08 16:55:44 0x7fc3dc431700
*** (1) TRANSACTION:
TRANSACTION 971006683, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2615633, OS thread handle 140479196354304, query id 1868675169 10.11.93.112 studenttask update
INSERT INTO student_task SET prestudyTaskStatus = 0,prestudyTaskNum = 0,rolePlayTaskStatus = 0,rolePlayTaskNum = 0,homeworkTaskStatus = 0,homeworkTaskNum = 0,animationTaskStatus = 0,animationTaskNum = 0,replayTaskStatus = 0,replayTaskdNum = 0,updatedTime = 1578473744880,createdTime = 1578473744880,studentId = 155052010,episodeId = 121635098,lessonId = 3153809,ticketId = 884505443 ON DUPLICATE KEY UPDATE ticketId = 884505443
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 119 page no 2621373 n bits 624 index studentId_lessonId_episodeId of table tutor_student_task.student_task trx id 971006683 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 893de7ea; asc = ;;
1: len 4; hex 00301f91; asc 0 ;;
2: len 4; hex 0740011e; asc @ ;;
3: len 8; hex 800000001b316983; asc 1i ;;

*** (2) TRANSACTION:
TRANSACTION 971006682, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 2616899, OS thread handle 140478316992256, query id 1868675168 10.11.93.112 studenttask update
INSERT INTO student_task SET prestudyTaskStatus = 0,prestudyTaskNum = 0,rolePlayTaskStatus = 0,rolePlayTaskNum = 0,homeworkTaskStatus = 0,homeworkTaskNum = 0,animationTaskStatus = 0,animationTaskNum = 0,replayTaskStatus = 0,replayTaskdNum = 0,updatedTime = 1578473744880,createdTime = 1578473744880,studentId = 155052010,episodeId = 121635101,lessonId = 3153809,ticketId = 884505439 ON DUPLICATE KEY UPDATE ticketId = 884505439
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 119 page no 2621373 n bits 624 index studentId_lessonId_episodeId of table tutor_student_task.student_task trx id 971006682 lock_mode X locks gap before rec
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 893de7ea; asc = ;;
1: len 4; hex 00301f91; asc 0 ;;
2: len 4; hex 0740011e; asc @ ;;
3: len 8; hex 800000001b316983; asc 1i ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 119 page no 2621373 n bits 624 index studentId_lessonId_episodeId of table tutor_student_task.student_task trx id 971006682 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 893de7ea; asc = ;;
1: len 4; hex 00301f91; asc 0 ;;
2: len 4; hex 0740011e; asc @ ;;
3: len 8; hex 800000001b316983; asc 1i ;;

*** WE ROLL BACK TRANSACTION (1)