加入收藏 | 设为首页 | 会员中心 | 我要投稿 银川站长网 (https://www.0951zz.com/)- 云通信、基础存储、云上网络、机器学习、视觉智能!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

如何领会MYSQL RC模式insert update可能死锁的情况

发布时间:2023-08-08 13:17:49 所属栏目:MySql教程 来源:
导读:本篇文章给大家分享的是有关如何理解MYSQL RC模式insert update可能死锁的情况,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。涉及的语句为updat

本篇文章给大家分享的是有关如何理解MYSQL RC模式insert update可能死锁的情况,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。

涉及的语句为

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可能死锁的情况,小编相信有部分知识点可能是我们日常工作会见到或用到的。

(编辑:银川站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章