MySQL RC模式insert update 可能死锁的情况

134次阅读

共计 7795 个字符，预计需要花费 20 分钟才能阅读完成。

涉及的语句为
RC 模式下
update 根据主键更新和 insert
其实这样的问题在 RC 模式下，要么是简单 update 问题，要么是 insert 造成的主键和唯一键检查唯一性时出现问题。
下面以主键问题为列子进行分析一下可能出现的情况。

update where 条件更新为主键，锁结构出现在单行主键上，辅助索引包含隐含锁结构，当前读 RC 非唯一索引模式没有 GAP 锁，
insert 插入印象锁，主键和辅助索引上会出现隐含锁结构，

但是在 RC 模式下没有 GAP 所以插入印象锁一般不会成为问题

表结构：
+———+———————————————————————————————————————————————————————————–+
| Table | Create Table |
+———+———————————————————————————————————————————————————————————–+
| testlll | CREATE TABLE `testlll` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 |
+———+———————————————————————————————————————————————————————————–+

情况 1
insert
update

TX1：TX2：
insert into testlll(name) values(‘gaopeng’);
insert into testlll(name) values(‘gaopeng’);
update testlll set name=’gaopeng1′ where id=25;(堵塞)
update testlll set name=’gaopeng1′ where id=24;(堵塞)

死锁

锁结构：

—TRANSACTION 322809, ACTIVE 30 sec starting index read

MySQL tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140734663714560, query id 409 localhost root updating
update testlll set name=’gaopeng1′ where id=24
—lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
TABLE LOCK table `test`.`testlll` trx id 322809 lock mode IX
—lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322809 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 00000004ecf9; asc ;;
2: len 7; hex f0000001f90110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

—lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322809 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000018; asc ;;
1: len 6; hex 00000004ecf8; asc ;;
2: len 7; hex ef000001f80110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

—TRANSACTION 322808, ACTIVE 43 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140734663980800, query id 408 localhost root updating
update testlll set name=’gaopeng1′ where id=25
——- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 00000004ecf9; asc ;;
2: len 7; hex f0000001f90110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

——————
—lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
TABLE LOCK table `test`.`testlll` trx id 322808 lock mode IX
—lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 00000004ecf9; asc ;;
2: len 7; hex f0000001f90110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

—lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000018; asc ;;
1: len 6; hex 00000004ecf8; asc ;;
2: len 7; hex ef000001f80110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;
情况 2
update
update

TX1：TX2：
update testlll set name=’gaopeng1′ where id=22;
update testlll set name=’gaopeng1′ where id=25;
update testlll set name=’gaopeng1′ where id=25;(堵塞)
update testlll set name=’gaopeng1′ where id=22;(堵塞)
死锁

这种情况比较简单不打印出锁结构

情况 3
insert
insert

TX1：TX2:
insert into testlll values(26,’gaopeng’);
insert into testlll values(27,’gaopeng’);
nsert into testlll values(27,’gaopeng’);(堵塞)
insert into testlll values(26,’gaopeng’);(堵塞)

死锁

锁结构:

—TRANSACTION 422212176315800, not started

0 lock struct(s), heap size 1160, 0 row lock(s)
—TRANSACTION 323284, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140734663980800, query id 369 localhost root update
insert into testlll values(26,’gaopeng’)
—lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
TABLE LOCK table `test`.`testlll` trx id 323284 lock mode IX
—lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323284 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001b; asc ;;
1: len 6; hex 00000004eed4; asc ;;
2: len 7; hex d3000002a10110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

—lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323284 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001a; asc ;;
1: len 6; hex 00000004eed3; asc ;;
2: len 7; hex d2000002330110; asc 3 ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

—TRANSACTION 323283, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140734663714560, query id 368 localhost root update
insert into testlll values(27,’gaopeng’)
——- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001b; asc ;;
1: len 6; hex 00000004eed4; asc ;;
2: len 7; hex d3000002a10110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

——————
—lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
TABLE LOCK table `test`.`testlll` trx id 323283 lock mode IX
—lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001b; asc ;;
1: len 6; hex 00000004eed4; asc ;;
2: len 7; hex d3000002a10110; asc ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

—lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001a; asc ;;
1: len 6; hex 00000004eed3; asc ;;
2: len 7; hex d2000002330110; asc 3 ;;
3: len 7; hex 67616f70656e67; asc gaopeng;;

正文完

星哥说事-微信公众号