间隙锁素材图
2024-03-12 10:41:29 0 举报间隙锁素材图
作者其他创作
大纲/内容
19
20
21
22
39
1
15
5
10
诸葛亮
关羽
刘备
曹操
周瑜
蜀
魏
吴
特殊记录，标记最小记录
记录锁: id = 10
...
age列(非唯一索引)
id列(主键索引)
name列
事务A
事务B
事务C
begin;
select * from person where id = 1 for update;
update person set age = 20 where id = 1;//阻塞
delete from person where id = 1;//阻塞
第一个锁，next-key锁：（-∞，1]
id(主键)
age
name
courtry
age列
courtry列
font color=\"#000000\
height列
执行步骤
set session transaction isolation level read committed;
2
3
select * from person p where country = '吴';
4
select * from person p where country = '吴';（若读取到孙权，则发生了不可重复读）
set session transaction isolation level read committed;
select* from person where id = 66;
update person set name = '张飞' where id =66;
select* from person where id = 66;（若id=66的name为张飞则发生了不可重复读）
infimumrecord
第一个锁，记录锁：15
记录锁: id = 20
supremum pseudo-record
第二个锁，间隙锁：（1，5)
50
80
25
id列(主键)
set session transaction isolation level repeatable read;
select * from person p where country = '吴';
6
update person set age = age + 1 where country = '吴';
7
8
commit;
set session transaction isolation level read uncommitted;
select* from person where id = 66;（若id=66的name为张飞则发生了脏读）
rollback;
supremum pseudo-record
178
155
169
176
182
给 id 为1的这条记录加X 型的记录锁
update person set name = '黄忠' where id =1;
update person set name = '张飞' where id =1;
第三个锁，间隙锁：（5，10)
第二个锁，next-key锁：（1，5]
0 条评论
下一页
