一个死锁的问题,求大师指点

yangjustins

先说下背景,mysql版本是5.5.41(线上很久了,没升级)    隔离级别是readcommitted
先给表结构:
mysql> show create table sys_seq\G
*************************** 1. row ***************************
       Table: sys_seq
Create Table: CREATE TABLE `sys_seq` (
  `id` bigint(6) NOT NULL AUTO_INCREMENT COMMENT '自增字段',
  `name` varchar(32) NOT NULL COMMENT 'seq名字，为日后清理和统计用',
  `raw_add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
UNIQUE KEY uk_trade_seq_id(`id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=127368 DEFAULT CHARSET=utf8 COMMENT='系统表，用于生成序列号'


大概描述下死锁信息,都是同一个sql,只是是由不同的事务造成的.这个sql是一个自定义函数.
函数内容如下:
BEGIN
        declare v_title bigint(20);
        INSERT INTO sys_seq(`name`) VALUES (`xxkey`  );
        SELECT COUNT(1) INTO v_title FROM sys_seq WHERE `name` = `xxkey`  ;
        return  v_title;  
END

这个函数的大概意思就是插入一条,然后取这个表的总数..根据这个总数.用到别的地方.


现在来看看死锁信息,在show engine innodb status\G下.
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION BB18EEEB, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 76 row lock(s), undo log entries 1
MySQL thread id 52479047, OS thread handle 0x40f28940, query id 3870726576 10.17.4.133 newC_nirvana_pos Sending data
SELECT COUNT(1) INTO v_title FROM sys_seq WHERE `name` =  NAME_CONST('xxkey',_utf8'SettleSerialNo' COLLATE 'utf8_general_ci')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 943 page no 8 n bits 832 index `uk_trade_seq_id` of table `yjf_nirvana_pos`.`sys_seq` trx id BB18EEEB lock_mode X locks rec but not gap waiting
Record lock, heap no 112 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 800000000000006f; asc        o;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

*** (2) TRANSACTION:
TRANSACTION BB18EEDD, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 263
mysql tables in use 1, locked 1
223 lock struct(s), heap size 47544, 297 row lock(s), undo log entries 1
MySQL thread id 52479374, OS thread handle 0x4d108940, query id 3870726537 10.17.4.135 newC_nirvana_pos Sending data
SELECT COUNT(1) INTO v_title FROM sys_seq WHERE `name` =  NAME_CONST('xxkey',_utf8'twoSettleNo' COLLATE 'utf8_general_ci')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 943 page no 8 n bits 832 index `uk_trade_seq_id` of table `yjf_nirvana_pos`.`sys_seq` trx id BB18EEDD lock_mode X locks rec but not gap
Record lock, heap no 112 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 800000000000006f; asc        o;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 113 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000070; asc        p;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 122 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000079; asc        y;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 123 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 800000000000007a; asc        z;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 164 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000a3; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000a4; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 166 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000a5; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 167 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000a6; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 172 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000ab; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 173 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000ac; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 174 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000ad; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 175 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000ae; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000b3; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 185 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000b8; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 186 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000b9; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 189 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000bc; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 194 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000c1; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 195 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000c2; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 196 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000c3; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 197 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000c4; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 209 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000d0; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 221 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000dc; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 244 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000f3; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 245 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000f4; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 246 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000f5; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 247 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000f6; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 252 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000fb; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 253 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000fc; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 254 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000fd; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

Record lock, heap no 255 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000000000fe; asc         ;;
1: len 11; hex 74776f536574746c654e6f; asc twoSettleNo;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 943 page no 233 n bits 600 index `uk_trade_seq_id` of table `yjf_nirvana_pos`.`sys_seq` trx id BB18EEDD lock_mode X locks rec but not gap waiting
Record lock, heap no 532 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 800000000001f017; asc         ;;
1: len 14; hex 536574746c6553657269616c4e6f; asc SettleSerialNo;;




初步判断是因为唯一索引导致的间歇锁....但为什么第二个事务的select会上x锁????? 这是最大的疑问...当然,也请大神解读下..是不是因为间隙锁导致的死锁...非常感谢!

Anssende

在name上加索引尝试一下,可能是name上无索引,导致所有记录被锁.

yangjustins

回复 2# Anssende


    为什么要在name上加索引???   不是已经有联合索引了? 这个跟name有无索引...没什么关系吧....

love100

        INSERT INTO sys_seq(`name`) VALUES (`xxkey`  );
        SELECT COUNT(1) INTO v_title FROM sys_seq WHERE `name` = `xxkey`  ;
==》这个两个sql是在同一个事务内吗？

love100

回滚了哪个事务？你提供的log中没有体现出现，log不全.

yangward

能给个重现的方法吗

rhino11

post:

show variables like 'autocommit';