insert ..on duplicate key update

数据库版本

> select version();
5.7.23-log

表结构

CREATE TABLE `t_live_order_course_extend` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `live_order_course_id` BIGINT(20) NOT NULL,
    `extend_ref_type` TINYINT(4) NOT NULL COMMENT 'wiki: 12848752',
    `extend_value` VARCHAR(2048) NOT NULL COMMENT '1' COLLATE 'utf8mb4_unicode_ci',
    `is_deleted` TINYINT(4) NOT NULL,
    `create_time` DATETIME NOT NULL,
    `last_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `unq_order_course_id_extend_ref_type` (`live_order_course_id`, `extend_ref_type`)
)

同时开启了三个事务

set innodb_lock_wait_timeout = 150;
SET AUTOCOMMIT=0;

BEGIN;
insert into t_live_order_course_extend     (live_order_course_id, extend_ref_type, extend_value, is_deleted, create_time)
values(2086283,11,1,FALSE,now()) ON DUPLICATE KEY UPDATE  extend_value = 2,  is_deleted = 0;

BEGIN;
insert into t_live_order_course_extend     (live_order_course_id, extend_ref_type, extend_value, is_deleted, create_time)
values(2086281,11,1,FALSE,now()) ON DUPLICATE KEY UPDATE  extend_value = 2,  is_deleted = 0;

BEGIN;
insert into t_live_order_course_extend     (live_order_course_id, extend_ref_type, extend_value, is_deleted, create_time)
values(2086282,11,1,FALSE,now()) ON DUPLICATE KEY UPDATE  extend_value = 2,  is_deleted = 0;

查看information_schema.innodb_locks表和information_schema.innodb_locks_wait两个表状态

innodb_locks
innodb_locks_wait

然后提交第一个事务，导致第二个事务死锁，查看最近的一条死锁日志

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-06-21 18:15:25 0x7f4da6fbf700
*** (1) TRANSACTION:
TRANSACTION 115029256, ACTIVE 42 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 25247923, OS thread handle 139971485947648, query id 13745778105 172.22.9.20 qingqingDev update
insert into t_live_order_course_extend     (live_order_course_id, extend_ref_type, extend_value, is_deleted, create_time)
values(2086281,11,1,FALSE,now())  ON DUPLICATE KEY UPDATE  extend_value = 2,  is_deleted = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1655 page no 22 n bits 288 index unq_order_course_id_extend_ref_type of table `qq_live`.`t_live_order_course_extend` trx id 115029256 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 218 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 80000000001fd58b; asc         ;;
 1: len 1; hex 8b; asc  ;;
 2: len 8; hex 00000000000006a9; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 115029225, ACTIVE 45 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 25248118, OS thread handle 139971490739968, query id 13745777462 172.22.9.20 qingqingDev update
insert into t_live_order_course_extend     (live_order_course_id, extend_ref_type, extend_value, is_deleted, create_time)
values(2086282,11,1,FALSE,now())  ON DUPLICATE KEY UPDATE  extend_value = 2,  is_deleted = 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1655 page no 22 n bits 288 index unq_order_course_id_extend_ref_type of table `qq_live`.`t_live_order_course_extend` trx id 115029225 lock_mode X locks gap before rec
Record lock, heap no 218 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 80000000001fd58b; asc         ;;
 1: len 1; hex 8b; asc  ;;
 2: len 8; hex 00000000000006a9; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1655 page no 22 n bits 288 index unq_order_course_id_extend_ref_type of table `qq_live`.`t_live_order_course_extend` trx id 115029225 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 218 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 80000000001fd58b; asc         ;;
 1: len 1; hex 8b; asc  ;;
 2: len 8; hex 00000000000006a9; asc         ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

把锁的相关信息全部补了一遍还是没分析出来这是什么情况
然后看到这篇 INSERT ... ON DUPLICATE KEY UPDATE产生death lock死锁原理，发现这应该是这个版本的bug
所以还是需要减少这类语句的使用。
不过我在新安装的MySQL8.0上重新走了以上步骤，是没有问题的。

关于锁比较全的博客：
Mysql Innodb 中的锁