select * from table where [case];
读取事物序列号对应的快照(readView) ,所以不会出现幻读,也不会触发锁;
关于 mysql 的log ,readView相关,可参读 : mysql事物(1) - 背景知识:log,readView
select * from table where [case] for update / in share mode;
insert , update ,delete
读取最新版本数据,会触发锁;
不同的隔离级别,对待锁的处理也是不一样的,这里我们以MYSQL 5.7 ,innoDB引擎下,RR(repeatable-read)级别探讨;
隔离级别相关,参读 : mysql事物(2) - 隔离级别
t
id(主键索引) | age(普通索引) | name(无索引) | stock |
---|---|---|---|
1 | 14 | 张三 | 100 |
3 | 17 | 李四 | 100 |
4 | 17 | 王五 | 100 |
select * from t where name= "张三" for update;
现象 : 触发锁表 原因 : 无索引,使用当前读 , 锁住整张表,保证下次当前读的一致性;
聚簇索引 / 非聚簇索引 参读 : mysql事物(0) - 索引结构
select * from t where id > 1 for update;
现象 : 出现间隙锁,间隙锁的字段为id , 范围为 (1,∞) 范围如图 :
原因 : 要保证下次当前读不出现幻读,即结果还是一样,需要保证该范围不要更新数据;
select * from t where id = 2 for update;
现象 : 出现间隙锁,间隙锁的字段为id , 范围为 [1,3]; 如图 :
原因 : 要保证不能插入id=2 的新纪录,则需要固定住其前后最近的索引指针;
select * from t where age = 15 for update;
现象 : 出现间隙锁,间隙锁的字段为age , 范围分别为 [14,17(id为3的记录)]; 如图 :
原因 : 非聚簇索引可重复,当索引对应值重复时,只需要锁住最近的等值索引范围即可,远近的排序规则为主键索引(此例表现为锁到 (3,17,"李四")这一行,而不是 (4,17,"王五") 这一行);
实验 :
前提条件为 select * from t where age = 15 for update;
未提交事物
## session1
update t set name = "实验" where id = 3;
## session2
update t set name = "实验" where id = 4;
现象 : session1 执行失败, session2 执行成功; 原因 : age = 15 不存在,要锁住 其两侧的值,保证下次查询结果一致;但是间隙锁的右边界有2个17,此时会按照主键索引排序,只锁到id=3 的这一行;
select * from t where id = 1 for update;
现象 : 出现行锁,无间隙锁; 原因 : 只需要锁住这一行,下次当前读的结果就是一致的;
select... LOCK IN SHARE MODE;
现象 : 查询结果集的每一行都加了行锁,在共享锁锁未释放前,这些行都不能被修改,可以被并发读;
update t set stock = stock-1 where id = 4 and stock-1 > 0;
现象 : 可以并发查询,修改,当库存为1时,两个session同时减库存,则只有一个会成功; 实验 :
## session1
session1 begin transaction
update t set stock = 1 where id = 4 ;
session1 commit transaction
## session2
session2 begin transaction
update t set stock = stock-1 where id = 4 and stock-1 > 0;
session3 begin transaction
update t set stock = stock-1 where id = 4 and stock-1 > 0;
session2 commit transaction
session3 commit transaction
现象 : session2 成功,session3 失败; 原因 : 提交事物时,session3 使用当前读,读取最新的库存已经是0了, 0-1 > 0 为 false,故失败;