MySQL学习笔记（四）—MySQL不同隔离级别加锁实战

表记录如下：

pid(int)	name(varchar)	num(int)
1	a	100
2	b	200
3	c	300
7	d	200

Condition：

• PID为主键索引
• 共享锁（读锁）：简称S锁
• 互斥锁（写锁）：简称X锁
• MySQL的行锁都是加在索引上，称为记录锁（Record）
• 数据库引擎：InnoDB

一、数据库隔离级别为：RC / RU(读已提交/读未提交)，where条件非索引

1. select * from table where num = 200

不加任何锁，快照读

2. select * from table where num > 200

不加任何锁，快照读

3. select * from table where num = 200 lock in share mode

num = 200，对应pid为2，7两条记录，这两条记录的主键索引加S锁，采用当前读

4. select * from table where num > 200 lock in share mode

num > 200的记录只有pid = 3，该条记录主键索引加S锁，采用当前读

5. select * from table where num = 200 for update

num = 200，对应pid为2，7两条记录，这两条记录主键索引加X锁，采用当前读

6. select * from table where num > 200 for update

num > 200的记录只有pid = 3，该条记录主键索引加X锁，采取当前读

二、数据库隔离级别为：RC / RU(读已提交/读未提交)，where条件为主键索引

1. select * from table where pId = 2

不加任何锁，快照读

2. select * from table where pId > 2

不加任何锁，快照读

3. select * from table where pId = 2 lock in share mode

查询结果对应记录的主键索引加S锁，采用当前读

4. select * from table where pId > 2 lock in share mode

pid > 2，查询结果是3,7两条记录，在记录对应的主键索引加S锁，采用当前读

5. select * from table where pId = 2 for update

pid =2，对应记录的主键索引加X锁，采用当前读

6. select * from table where pId > 2 for update

pid > 2，查询结果是3,7两条记录，在记录对应的主键索引加X锁，采取当前读

三、数据库隔离级别为：RC / RU(读已提交/读未提交)，where条件为联合索引

1. select * from table where num = 200

不加任何锁，快照读

2. select * from table where num > 200

不加任何锁，快照读

3. select * from table where num = 200 lock in share mode

num = 200，对应pid为2，7两条记录，这两条记录的联合索引加S锁，回表去主键索引查询，对应记录主键索引加S锁，采用当前读

4. select * from table where num > 200 lock in share mode

num > 200的记录只有pid = 3，该条记录联合索引加S锁，回表去主键索引查询，对应记录主键索引加S锁，采用当前读

5. select * from table where num = 200 for update

num = 200，对应pid为2，7两条记录，这两条记录联合索引加X锁，回表去主键索引查询，对应记录主键索引加X锁，采用当前读

6. select * from table where num > 200 for update

num > 200的记录只有pid = 3，该条记录联合索引加X锁，回表去主键索引查询，对应记录主键索引加X锁，采取当前读

在RC/RU级别下：

• where条件非索引查询加锁方式和主键索引查询加锁方式很相似，都是查询结果的主键索引加锁。联合索引是在查询结果的联合索引和对应的主键索引加锁，加锁类型保持一致。

• MySQL本身对非索引查询做了优化，非索引查询会扫描全表所有记录进行加锁，但是会把不满足条件的锁立即释放掉，所以看起来结果一样

四、数据库隔离级别为：RR / Serializable(可重复读/串行)，where条件为非索引

1. select * from table where num = 200

RR：不加任何锁，快照读
Serializable：全表所有主键索引加S锁，在(-∞,1](1,2](2,3](3,7](7,+∞)上加Gap锁(间隙锁)，相当于锁住整个表，采取当前读

2. select * from table where num > 200

RR：不加任何锁，快照读
Serializable：全表所有主键索引加S锁，在(-∞,1](1,2](2,3](3,7](7,+∞)上加Gap锁(间隙锁)，相当于锁住整个表，采取当前读

3. select * from table where num = 200 lock in share mode

num = 200，对应pid为2，7两条记录，全表所有主键索引加S锁，在(-∞,1](1,2](2,3](3,7](7,+∞)上加Gap锁(间隙锁)，相当于锁住整个表，采取当前读

4. select * from table where num > 200 lock in share mode

num > 200的记录只有pid = 3，全表所有主键索引加S锁，在(-∞,1](1,2](2,3](3,7](7,+∞)上加Gap锁(间隙锁)，相当于锁住整个表，采取当前读

5. select * from table where num = 200 for update

num = 200，对应pid为2，7两条记录，全表所有主键索引加X锁，在(-∞,1](1,2](2,3](3,7](7,+∞)上加Gap锁(间隙锁)，相当于锁住整个表，采取当前读

6. select * from table where num > 200 for update

num > 200的记录只有pid = 3，全表所有主键索引加X锁，在(-∞,1](1,2](2,3](3,7](7,+∞)上加Gap锁(间隙锁)，相当于锁住整个表，采取当前读

五、数据库隔离级别为：RR / Serializable(可重复读/串行)，where条件为主键索引

1. select * from table where pId = 2

RR：不加任何锁，快照读
Serializable：在pid=2的主键索引加S锁，不存在Gap锁

2. select * from table where pId > 2

RR：不加任何锁，快照读
Serializable：pid > 2，查询结果是3,7两条记录，在对应记录主键索引加S锁，同时在(2,3](3,7](7,+∞)上加Gap锁，采用当前读

3. select * from table where pId = 2 lock in share mode

pid =2，对应记录的主键索引加S锁，不存在Gap锁，采用当前读

4. select * from table where pId > 2 lock in share mode

pid > 2，查询结果是3,7两条记录，在对应记录主键索引加S锁，同时在(2,3](3,7](7,+∞)上加Gap锁，采用当前读

5. select * from table where pId = 2 for update

pid =2，对应记录的主键索引加X锁，不存在Gap锁，采用当前读

6. select * from table where pId > 2 for update

pid > 2，查询结果是3,7两条记录，在对应记录主键索引加X锁，同时在(2,3](3,7](7,+∞)上加Gap锁，采用当前读

7. select * from table where pId = 6 [lock in share mode|for update]

该记录不存在，在(3,7]上加Gap锁

8. select * from table where pId > 18 [lock in share mode|for update]

该记录不存在，范围查询，在主键索引(7,+∞)上加gap lock

六、数据库隔离级别为：RR / Serializable(可重复读/串行)，where条件为联合索引

1. select * from table where num = 200

RR：不加任何锁，快照读
Serializable：num = 200，对应pid为2，7两条记录，在记录对应的联合索引上加S锁，对应主键索引加S锁，在联合索引(100,200](200,300]加上gap lock，采取当前读

2. select * from table where num > 200

RR：不加任何锁，快照读
Serializable：num > 200的记录只有pid = 3，在对应记录的联合索引上加S锁，对应主键索引上加S锁，在联合索引(200,300](300,+∞)上加Gap锁，采取当前读

3. select * from table where num = 200 lock in share mode

num = 200，对应pid为2，7两条记录，在记录对应的联合索引上加S锁，对应主键索引加S锁，在联合索引(100,200](200,300]加上gap lock，采取当前读

4. select * from table where num > 200 lock in share mode

num > 200的记录只有pid = 3，在对应记录的联合索引上加S锁，对应主键索引上加S锁，在联合索引(200,300](300,+∞)上加Gap锁，采取当前读

5. select * from table where num = 200 for update

num = 200，对应pid为2，7两条记录，在记录对应的联合索引上加X锁，对应主键索引加S锁，在联合索引(100,200](200,300]加上gap lock，采取当前读

6. select * from table where num > 200 for update

num > 200的记录只有pid = 3，在对应记录的联合索引上加X锁，对应主键索引上加S锁，在联合索引(200,300](300,+∞)上加Gap锁，采取当前读

7. select * from table where num = 250 [lock in share mode|for update]

查询结果为空，会在联合索引(200,300]上加Gap锁，采取当前读

8. select * from table where num > 400 [lock in share mode|for update]

查询结果为空，会在联合索引(400,+∞)上加Gap锁，采取当前读

在RR/Serializable级别下：

• 在可重复读和串行级别下，存在间隙锁，其它两个级别不存在该锁
• 扫描到的行的索引（主键+联合）都会加锁
• 等值查询和范围查询不一样，等值查询的条件如果是主键索引，可以精确定位，不加间隙锁，范围查询会在相应范围加间隙锁
• 条件是联合索引，只在联合索引加X锁，主键索引加S锁

select不显示加锁时，除了Serializable级别，其它级别不会加锁

参考：
https://www.cnblogs.com/rjzheng/p/9950951.html