MYSQL RC模式insert update 可能死鎖的情況

gaopengtttt發表於2017-07-11
MySql模式
涉及的語句為
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;(堵塞)
                                               
死鎖

鎖結構：

點選(此處)摺疊或開啟

  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;;

  16. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  17. 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)
  18. Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  19.  0: len 4; hex 80000018; asc ;;
  20.  1: len 6; hex 00000004ecf8; asc ;;
  21.  2: len 7; hex ef000001f80110; asc ;;
  22.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  24. ---TRANSACTION 322808, ACTIVE 43 sec starting index read
  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 2, OS thread handle 140734663980800, query id 408 localhost root updating
  28. update testlll set name='gaopeng1' where id=25
  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 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  31. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  32.  0: len 4; hex 80000019; asc ;;
  33.  1: len 6; hex 00000004ecf9; asc ;;
  34.  2: len 7; hex f0000001f90110; asc ;;
  35.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  37. ------------------
  38. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  39. TABLE LOCK table `test`.`testlll` trx id 322808 lock mode IX
  40. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  41. 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)
  42. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  43.  0: len 4; hex 80000019; asc ;;
  44.  1: len 6; hex 00000004ecf9; asc ;;
  45.  2: len 7; hex f0000001f90110; asc ;;
  46.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  48. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  49. 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)
  50. Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  51.  0: len 4; hex 80000018; asc ;;
  52.  1: len 6; hex 00000004ecf8; asc ;;
  53.  2: len 7; hex ef000001f80110; asc ;;
  54.  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');(堵塞)


死鎖

鎖結構:

點選(此處)摺疊或開啟

  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;;


  19. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  20. 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)
  21. Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  22.  0: len 4; hex 8000001a; asc ;;
  23.  1: len 6; hex 00000004eed3; asc ;;
  24.  2: len 7; hex d2000002330110; asc 3 ;;
  25.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  28. ---TRANSACTION 323283, ACTIVE 14 sec inserting
  29. mysql tables in use 1, locked 1
  30. LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
  31. MySQL thread id 3, OS thread handle 140734663714560, query id 368 localhost root update
  32. insert into testlll values(27,'gaopeng')
  33. ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
  34. 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)
  35. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  36.  0: len 4; hex 8000001b; asc ;;
  37.  1: len 6; hex 00000004eed4; asc ;;
  38.  2: len 7; hex d3000002a10110; asc ;;
  39.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  42. ------------------
  43. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  44. TABLE LOCK table `test`.`testlll` trx id 323283 lock mode IX
  45. ---lock strcut(2):(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 S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  47. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  48.  0: len 4; hex 8000001b; asc ;;
  49.  1: len 6; hex 00000004eed4; asc ;;
  50.  2: len 7; hex d3000002a10110; asc ;;
  51.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  54. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  55. 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)
  56. Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  57.  0: len 4; hex 8000001a; asc ;;
  58.  1: len 6; hex 00000004eed3; asc ;;
  59.  2: len 7; hex d2000002330110; asc 3 ;;
  60.  3: len 7; hex 67616f70656e67; asc gaopeng;;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2141893/，如需轉載，請註明出處，否則將追究法律責任。

相關文章