本篇文章給大家分享的是有關如何理解MYSQL RC模式insert update可能死鎖的情況，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。
涉及的語句為
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可能死鎖的情況，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
