共计 7987 个字符，预计需要花费 20 分钟才能阅读完成。
本篇文章给大家分享的是有关如何理解 MYSQL RC 模式 insert update 可能死锁的情况，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。
涉及的语句为
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;;
以上就是如何理解 MYSQL RC 模式 insert update 可能死锁的情况，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。
