數(shù)據(jù)庫版本
> 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兩個表狀態(tài)
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死鎖原理，發(fā)現(xiàn)這應該是這個版本的bug
所以還是需要減少這類語句的使用。
不過我在新安裝的MySQL8.0上重新走了以上步驟，是沒有問題的。
關于鎖比較全的博客：
Mysql Innodb 中的鎖
