作者:高鹏(八怪),《深入理解MySQL主从原理32讲》系列的作者
系列链接:https://www.jianshu.com/nb/43148932
原文出处:https://www.jianshu.com/p/3e57a428d2a2
一、案例模拟二、死锁分析三、关于锁模式的变化四、关于LOCK_ORDINARY[next_key_lock]来历最后
环境:版本5.7.29,RR隔离级别
一、案例模拟
CREATE TABLE `t8` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`d_id` varchar(40) NOT NULL DEFAULT '',
`b_id` varchar(40) NOT NULL DEFAULT '',
`is_dropped` tinyint(1) NOT NULL DEFAULT '0',
`u_c` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `DealerAndBrokerAndDropped` (`d_id`,`b_id`,`is_dropped`)
) ENGINE=InnoDB ;
insert into t8 values(1,1,1,0,'a');
insert into t8 values(2,2,2,0,'a');
insert into t8 values(3,3,3,0,'a');
insert into t8 values(4,4,4,0,'a');
insert into t8 values(5,5,5,0,'a');
insert into t8 values(6,6,6,0,'a');
insert into t8 values(7,7,7,0,'a');
insert into t8 values(8,8,8,0,'a');
insert into t8 values(9,9,9,0,'a');
insert into t8 values(10,10,10,0,'a');
insert into t8 values(11,11,11,0,'a');
执行语句如下:
S1 | S2 |
---|---|
begin | |
select u_c from t8 where d_id='1' and b_id='1' and is_dropped=0 for update; | |
select u_c from t8 where d_id='1&# |