一個(gè)死鎖的問題,求大師指點(diǎn)

yangjustins

先說下背景,mysql版本是5.5.41(線上很久了,沒升級)    隔離級別是readcommitted
先給表結(jié)構(gòu):
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名字，為日后清理和統(tǒng)計(jì)用',
  `raw_add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`),
UNIQUE KEY uk_trade_seq_id(`id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=127368 DEFAULT CHARSET=utf8 COMMENT='系統(tǒng)表，用于生成序列號'


大概描述下死鎖信息,都是同一個(gè)sql,只是是由不同的事務(wù)造成的.這個(gè)sql是一個(gè)自定義函數(shù).
函數(shù)內(nèi)容如下:
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

這個(gè)函數(shù)的大概意思就是插入一條,然后取這個(gè)表的總數(shù)..根據(jù)這個(gè)總數(shù).用到別的地方.


現(xiàn)在來看看死鎖信息,在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;;




初步判斷是因?yàn)槲ㄒ凰饕龑?dǎo)致的間歇鎖....但為什么第二個(gè)事務(wù)的select會上x鎖????? 這是最大的疑問...當(dāng)然,也請大神解讀下..是不是因?yàn)殚g隙鎖導(dǎo)致的死鎖...非常感謝!

Anssende

在name上加索引嘗試一下,可能是name上無索引,導(dǎo)致所有記錄被鎖.

yangjustins

回復(fù) 2# Anssende


    為什么要在name上加索引???   不是已經(jīng)有聯(lián)合索引了? 這個(gè)跟name有無索引...沒什么關(guān)系吧....

love100

        INSERT INTO sys_seq(`name`) VALUES (`xxkey`  );
        SELECT COUNT(1) INTO v_title FROM sys_seq WHERE `name` = `xxkey`  ;
==》這個(gè)兩個(gè)sql是在同一個(gè)事務(wù)內(nèi)嗎？

love100

回滾了哪個(gè)事務(wù)？你提供的log中沒有體現(xiàn)出現(xiàn)，log不全.

yangward

能給個(gè)重現(xiàn)的方法嗎

rhino11

post:

show variables like 'autocommit';