如何理解MYSQL RC模式insert update可能死锁的细况
发布时间：2021-12-20 11:30:09 所属栏目：通讯 来源：互联网
导读：本篇文章给大家分享的是有关如何理解MYSQL RC模式insert update可能死锁的情况，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 涉及的语句为 RC模式下 update根据主键更新和insert 其实
|
本篇文章给大家分享的是有关如何理解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可能死锁的情况，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。 （编辑：佛山站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读