mysql并发插入引起的死锁

[米开朗琪罗夫斯基]

这两天大数据有个mysql实例经常产生死锁报警，查了网上的很多文档，明白了个大概，但是网上很多分享都是两个事务操作同一条记录，这个产生死锁很好理解，但是我查看了错误日志后，发现是两条不同的insert语句，有着不同的主键产生了死锁，一直理解不了为什么，两个事务在insert两条不同的记录也会产生死锁，当前的MySQL版本是5.6的，事务隔离级别是RR，自己想了好几天，还是很困惑。请求大牛们指教。

表结构如下：
CREATE TABLE `realtime_xueke_study_lesson` (
`daytime` bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘时间戳’,
`userid` bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,
`lesson_id` bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘课程id’,
`type` tinyint(4) NOT NULL DEFAULT ‘0’,
`v_time` double NOT NULL DEFAULT ‘0’ COMMENT ‘观看时长(秒)’,
`video_time` double NOT NULL DEFAULT ‘0’ COMMENT ‘视频时长’,
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`utime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`flag` tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘区分不同的topic。1,2,3’,
PRIMARY KEY (`daytime`,`userid`,`lesson_id`,`type`) USING BTREE,
KEY `idx_userid` (`userid`),
KEY `idx_lessonid` (`lesson_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

报错信息如下：
2018-09-03 07:19:07 7fd8339c8700InnoDB: transactions deadlock detected, dumping detailed information.
2018-09-03 07:19:07 7fd8339c8700
*** (1) TRANSACTION:
TRANSACTION 3466549211, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 18 lock struct(s), heap size 2936, 17 row lock(s), undo log entries 20
MySQL thread id 7760242, OS thread handle 0x7fd72dcba700, query id 1607314748 x.x.x.x bigdata_user update
INSERT INTO realtime_xueke_study_lesson (userid,lesson_id,v_time,daytime,type,flag) VALUES (10316873,14106,5.48,1535904000000,1,6) ON DUPLICATE KEY UPDATE v_time = 5.48,flag = 6
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 43898 page no 37589 n bits 280 index `PRIMARY` of table `data_report`.`realtime_xueke_study_lesson` trx id 3466549211 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 3466549210, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
12 lock struct(s), heap size 2936, 12 row lock(s), undo log entries 13
MySQL thread id 7745349, OS thread handle 0x7fd8339c8700, query id 1607314751 x.x.x.x bigdata_user update
INSERT INTO realtime_xueke_study_lesson (userid,lesson_id,v_time,daytime,type,flag) VALUES (13631301,15055,9.559,1535904000000,1,6) ON DUPLICATE KEY UPDATE v_time = 9.559,flag = 6
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 43898 page no 37589 n bits 280 index `PRIMARY` of table `data_report`.`realtime_xueke_study_lesson` trx id 3466549210 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 43898 page no 34727 n bits 216 index `PRIMARY` of table `data_report`.`realtime_xueke_study_lesson` trx id 3466549210 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

[米开朗琪罗夫斯基]

自己顶一下，不要沉啊

[xieyixieyi]

mysql在并发插入的时候，出现duplicate异常时 会默认加锁
官方解释如下：
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

[xieyixieyi]

大体的意思是：insert会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非next-key锁（当然更不是gap锁了），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁（简称I锁吧），并发的事务可以对同一个gap加I锁。如果insert 的事务出现了duplicate-key error ，事务会对duplicate index record加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的insert都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的insert操作是会发生死锁的。
可以参考这个内容：
https://blog.csdn.net/kezhong_wxl/article/details/76682710