GAP锁(间隙锁,对唯一键,普通索引产生的不同影响)

-- (1)先建表,用于证明普通索引,有无间隙锁
-- 创建间隙表,name用的普通索引
drop table if exists  test_gap;
create table test_gap(
id int primary key auto_increment comment '主键',
name varchar(20) comment '名称',
num int comment '数量',
beginTime dateTime comment '开始时间',
endTime dateTime comment '开始时间',
key idx_name(name)
) comment '测试间隙锁表';


insert into test_gap(name,num,beginTime,endTime)
values('a',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day) ),
('b',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day)),
('c',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day)),
('d',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day)),
('e',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day));




-- (2)模拟间隙锁,普通索引,有间隙锁
-- 打印出RR
show variables like '%isolation%';


-- transation 1 命令窗口1


set autocommit = 0;


update test_gap set num = num-1 where num-1>0 and now() between beginTime and endTime  and name = 'e';


-- commit 在窗口1,先别急着提交,等执行transtion 2 的插入语句后,再提交
commit;


-- transtion 2 命令窗口2


set autocommit = 0;


-- 这里的insert会阻塞,需要先去transtion 1手动commit之后,insert才会插入成功
insert into test_gap(name,num,beginTime,endTime)
values('e1',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day) );


commit;








-- (3)建表,用于证明唯一索引,查询条件丰富,也无间隙锁
-- 创建间隙表,注意name用的唯一索引
drop table if exists  test_gap2;
create table test_gap2(
id int primary key auto_increment comment '主键',
name varchar(20) comment '名称',
num int comment '数量',
beginTime dateTime comment '开始时间',
endTime dateTime comment '开始时间',
unique key uk_name(name)
) comment '测试间隙锁表2';


insert into test_gap2(name,num,beginTime,endTime)
values('a',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day) ),
('b',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day)),
('c',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day)),
('d',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day)),
('e',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day));


-- 唯一索引,无间隙锁
-- transation 1 命令窗口1


set autocommit = 0;


update test_gap2 set num = num-1 where num-1>0 and now() between beginTime and endTime  and name = 'e';


-- commit 在窗口1,先别急着提交,等执行transtion 2 的插入语句后,再提交
commit;


-- transtion 2 命令窗口2


set autocommit = 0;


-- 这里的insert是不会阻塞的,尽管transtion 1的commit还没执行
insert into test_gap2(name,num,beginTime,endTime)
values('e1',100,date_sub(now(),interval 1 day),date_sub(now(),interval -1 day) );


commit;
展开阅读全文

没有更多推荐了,返回首页