2018-09-19线上采购死锁

Today 总结 死锁

2018-09-19T19:53:58.315961+08:00 2461767 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2018-09-19T19:53:58.315987+08:00 2461767 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 494216479, 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 2461759, OS thread handle 140237770991360, query id 2826738242 192.168.20.133 today_user update
INSERT INTO code_gen_info SET
              prefix = '3811700403',
              `date` = '2018-09-19 19:53:58.315',
              code_length = 4,
              max_code = 2
            ON DUPLICATE KEY UPDATE max_code = 2
2018-09-19T19:53:58.316042+08:00 2461767 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1139 page no 293 n bits 632 index uniq_pre_date of table `purchase_db`.`code_gen_info` trx id 494216479 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 183 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 33383131373030353032; asc 3811700502;;
 1: len 3; hex 8fc524; asc   $;;
 2: len 8; hex 000000000001318f; asc       1 ;;
2018-09-19T19:53:58.316177+08:00 2461767 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 494216480, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2461767, OS thread handle 140237737445120, query id 2826738243 192.168.20.102 today_user update
INSERT INTO code_gen_info SET
              prefix = '3811700403',
              `date` = '2018-09-19 19:53:58.315',
              code_length = 4,
              max_code = 2
            ON DUPLICATE KEY UPDATE max_code = 2
2018-09-19T19:53:58.316205+08:00 2461767 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1139 page no 293 n bits 632 index uniq_pre_date of table `purchase_db`.`code_gen_info` trx id 494216480 lock_mode X locks gap before rec
Record lock, heap no 183 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 33383131373030353032; asc 3811700502;;
 1: len 3; hex 8fc524; asc   $;;
 2: len 8; hex 000000000001318f; asc       1 ;;
2018-09-19T19:53:58.316335+08:00 2461767 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1139 page no 293 n bits 632 index uniq_pre_date of table `purchase_db`.`code_gen_info` trx id 494216480 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 183 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 33383131373030353032; asc 3811700502;;
 1: len 3; hex 8fc524; asc   $;;
 2: len 8; hex 000000000001318f; asc       1 ;;
2018-09-19T19:53:58.316459+08:00 2461767 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

表结构：

CREATE TABLE `code_gen_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `prefix` varchar(10) NOT NULL DEFAULT '' COMMENT '前缀, 如32, 3511712000',
  `date` date DEFAULT NULL COMMENT '日期',
  `max_code` int(11) NOT NULL DEFAULT '1' COMMENT '当前最大编码值',
  `code_length` int(11) NOT NULL DEFAULT '4' COMMENT '递增编码长度',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_pre_date` (`prefix`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=388033 DEFAULT CHARSET=utf8mb4 COMMENT='编码生成信息表'

日志分析：

• 事务1
  

• 事务2
  

说明：上述两个事务发生在不同节点的服务器上，线程1 和 线程2 几乎在同一个时间查询code_gen_info 的记录，并希望通过select for update的方式把查询到的记录锁住。首先，select for update 在where条件能匹配出记录的情况下，会把该记录锁住（X Lock）,然而，在案发的时刻，数据库中没有符合条件的记录。此时，两个事务在select for update 时仅仅拿到了gap锁。
关键点是： gap锁之间是兼容的，也就是说，两个线程都愉快地以为拿到了排它锁，并继续往后执行insert语句。
双方在执行insert 语句时，都需要都申请插入意向锁，然鹅插入意向锁与gap锁是不兼容的锁，导致两个事务都在等待对方释放gap锁。从而死锁产生。
备注：
MySQL 5.7.14
RR隔离级别