共計 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 行業資訊頻道。
