mysql(3) - 锁机制

1 背景知识

1.1 快照读

select * from table where [case];

读取事物序列号对应的快照(readView) ,所以不会出现幻读,也不会触发锁;

关于 mysql 的log ,readView相关,可参读 : mysql事物(1) - 背景知识:log,readView

1.2 当前读

select * from table where [case] for update / in share mode; 
insert , update ,delete

读取最新版本数据,会触发锁;

1.3 隔离级别

不同的隔离级别,对待锁的处理也是不一样的,这里我们以MYSQL 5.7 ,innoDB引擎下,RR(repeatable-read)级别探讨;

隔离级别相关,参读 : mysql事物(2) - 隔离级别

2 示例表

2.1 表名

t

2.2 表结构和数据

3 悲观锁 (关键字 : for update)

3.1 表级

举例1 :

select * from t where name= "张三" for update;

现象 : 触发锁表 原因 : 无索引,使用当前读 , 锁住整张表,保证下次当前读的一致性;

3.2 间隙级

聚簇索引 / 非聚簇索引 参读 : mysql事物(0) - 索引结构

举例1 - 主键索引(聚簇索引) - 非等值查询:

select * from t where id > 1 for update;

现象 : 出现间隙锁,间隙锁的字段为id , 范围为 (1,∞) 范围如图 :

原因 : 要保证下次当前读不出现幻读,即结果还是一样,需要保证该范围不要更新数据;

举例2 - 主键索引(聚簇索引) - 等值空查询:

select * from t where id = 2 for update;

现象 : 出现间隙锁,间隙锁的字段为id , 范围为 [1,3]; 如图 :

原因 : 要保证不能插入id=2 的新纪录,则需要固定住其前后最近的索引指针;

举例3 - 辅助索引(非聚簇索引)

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 的这一行;

3.3 行级(单行)

举例1 - 辅助索引(聚簇索引) - 等值非空查询

select * from t where id = 1 for update;

现象 : 出现行锁,无间隙锁; 原因 : 只需要锁住这一行,下次当前读的结果就是一致的;

4 半乐观锁 (共享锁 , 关键字 : IN SHARE MODE )

5.1 行级(多行)

举例1

select... LOCK IN SHARE MODE;

现象 : 查询结果集的每一行都加了行锁,在共享锁锁未释放前,这些行都不能被修改,可以被并发读;

5 乐观锁 (关键字 : 无,需程序实现 )

5.1 行级(单行)

举例1

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,故失败;