溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

如何理解MYSQL RC模式insert update可能死鎖的情況

發(fā)布時間：2021-11-16 11:28:33 來源：億速云 閱讀：181 作者：柒染 欄目：MySQL數(shù)據(jù)庫

本篇文章給大家分享的是有關(guān)如何理解MYSQL RC模式insert update可能死鎖的情況，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

涉及的語句為
RC模式下
update根據(jù)主鍵更新和insert
其實這樣的問題在RC模式下，要么是簡單update問題，要么是insert造成的主鍵和唯一鍵檢查唯一性時出現(xiàn)問題。
下面以主鍵問題為列子進行分析一下可能出現(xiàn)的情況。

update  where條件更新為主鍵，鎖結(jié)構(gòu)出現(xiàn)在單行主鍵上，輔助索引包含隱含鎖結(jié)構(gòu)，當前讀RC非唯一索引模式?jīng)]有GAP鎖，
insert  插入印象鎖，主鍵和輔助索引上會出現(xiàn)隱含鎖結(jié)構(gòu)，

但是在RC模式下沒有GAP所以插入印象鎖一般不會成為問題


表結(jié)構(gòu)：
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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;(堵塞)
                                               
死鎖

鎖結(jié)構(gòu)：

點擊(此處)折疊或打開

  1. ---TRANSACTION 322809, ACTIVE 30 sec starting index read

  2. mysql tables in use 1, locked 1

  3. 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1

  4. MySQL thread id 3, OS thread handle 140734663714560, query id 409 localhost root updating

  5. update testlll set name='gaopeng1' where id=24

  6. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  7. TABLE LOCK table `test`.`testlll` trx id 322809 lock mode IX

  8. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  9. 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)

  10. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  11.  0: len 4; hex 80000019; asc ;;

  12.  1: len 6; hex 00000004ecf9; asc ;;

  13.  2: len 7; hex f0000001f90110; asc ;;

  14.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  15. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  16. 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)

  17. Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  18.  0: len 4; hex 80000018; asc ;;

  19.  1: len 6; hex 00000004ecf8; asc ;;

  20.  2: len 7; hex ef000001f80110; asc ;;

  21.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  22. ---TRANSACTION 322808, ACTIVE 43 sec starting index read

  23. mysql tables in use 1, locked 1

  24. LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1

  25. MySQL thread id 2, OS thread handle 140734663980800, query id 408 localhost root updating

  26. update testlll set name='gaopeng1' where id=25

  27. ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

  28. 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)

  29. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  30.  0: len 4; hex 80000019; asc ;;

  31.  1: len 6; hex 00000004ecf9; asc ;;

  32.  2: len 7; hex f0000001f90110; asc ;;

  33.  3: len 7; hex 67616f70656e67; asc gaopeng;;


    ------------------

  34. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  35. TABLE LOCK table `test`.`testlll` trx id 322808 lock mode IX

  36. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  37. 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)

  38. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  39.  0: len 4; hex 80000019; asc ;;

  40.  1: len 6; hex 00000004ecf9; asc ;;

  41.  2: len 7; hex f0000001f90110; asc ;;

  42.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  43. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  44. 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)

  45. Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  46.  0: len 4; hex 80000018; asc ;;

  47.  1: len 6; hex 00000004ecf8; asc ;;

  48.  2: len 7; hex ef000001f80110; asc ;;

  49.  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;(堵塞)
死鎖


這種情況比較簡單不打印出鎖結(jié)構(gòu)


情況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');(堵塞)


死鎖

鎖結(jié)構(gòu):

點擊(此處)折疊或打開

  1. ---TRANSACTION 422212176315800, not started

  2. 0 lock struct(s), heap size 1160, 0 row lock(s)

  3. ---TRANSACTION 323284, ACTIVE 10 sec inserting

  4. mysql tables in use 1, locked 1

  5. 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1

  6. MySQL thread id 2, OS thread handle 140734663980800, query id 369 localhost root update

  7. insert into testlll values(26,'gaopeng')

  8. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  9. TABLE LOCK table `test`.`testlll` trx id 323284 lock mode IX

  10. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  11. 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)

  12. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  13.  0: len 4; hex 8000001b; asc ;;

  14.  1: len 6; hex 00000004eed4; asc ;;

  15.  2: len 7; hex d3000002a10110; asc ;;

  16.  3: len 7; hex 67616f70656e67; asc gaopeng;;



  17. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  18. 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)

  19. Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  20.  0: len 4; hex 8000001a; asc ;;

  21.  1: len 6; hex 00000004eed3; asc ;;

  22.  2: len 7; hex d2000002330110; asc 3 ;;

  23.  3: len 7; hex 67616f70656e67; asc gaopeng;;



  24. ---TRANSACTION 323283, ACTIVE 14 sec inserting

  25. mysql tables in use 1, locked 1

  26. LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1

  27. MySQL thread id 3, OS thread handle 140734663714560, query id 368 localhost root update

  28. insert into testlll values(27,'gaopeng')

  29. ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

  30. 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)

  31. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  32.  0: len 4; hex 8000001b; asc ;;

  33.  1: len 6; hex 00000004eed4; asc ;;

  34.  2: len 7; hex d3000002a10110; asc ;;

  35.  3: len 7; hex 67616f70656e67; asc gaopeng;;

    ------------------

  36. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  37. TABLE LOCK table `test`.`testlll` trx id 323283 lock mode IX

  38. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  39. 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)

  40. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  41.  0: len 4; hex 8000001b; asc ;;

  42.  1: len 6; hex 00000004eed4; asc ;;

  43.  2: len 7; hex d3000002a10110; asc ;;

  44.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  45. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

  46. 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)

  47. Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  48.  0: len 4; hex 8000001a; asc ;;

  49.  1: len 6; hex 00000004eed3; asc ;;

  50.  2: len 7; hex d2000002330110; asc 3 ;;

  51.  3: len 7; hex 67616f70656e67; asc gaopeng;;

以上就是如何理解MYSQL RC模式insert update可能死鎖的情況，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降?。希望你能通過這篇文章學到更多知識。更多詳情敬請關(guān)注億速云行業(yè)資訊頻道。

向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手