你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL RC模式insert update 可能死锁的情况

[日期:2017-07-12] 来源:Linux社区  作者:gaopengtttt [字体: ]

涉及的语句为
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;;

 

linux
相关资讯       MySQL RC模式  insert update 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款