锁 - 行级锁怎么加的?
开篇结论
加锁的对象是索引,加锁的基本单位是 next-key lock,它是由记录锁和间隙锁组合而成的,next-key lock 是左开右闭区间,而间隙锁是左开右开区间。
在只使用记录锁或者间隙锁就能避免幻读现象的场景下, next-key lock 就会退化成记录锁或间隙锁。
假设这个表,id 是主键索引(唯一索引),age 是普通索引(非唯一索引),name 是普通的列。数据如下:
唯一索引等值查询
- 当查询的记录是「存在」的,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
- 当查询的记录是「不存在」的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」。
记录存在的情况
假设事务 A 执行了这条等值查询语句,查询的记录是存在于表中的。
select * from user where id = 1 for update;
那么,事务 A 会为 id 为 1 的这条记录就会加上 X 型的记录锁。
接下来,如果有其他事务,对 id 为 1 的记录进行更新或者删除操作的话,这些操作都会被阻塞
为什么这里退化为了记录锁?
原因在于这里仅靠记录锁也能避免幻读的问题。
- 当其他事务,对 id 为 1 的记录进行更新或者删除或插入id为1的数据的操作时,由于记录锁,这些操作都会被阻塞,也就不会出现前后两次查询的结果集不同,也就避免了幻读的问题。
记录不存在的情况
假设事务 A 执行了这条等值查询语句,查询的记录是不存在于表中的。
select * from user where id = 2 for update;
此时事务 A 在 id = 5 记录的主键索引上加的是间隙锁,锁住的范围是 (1, 5)。
接下来,如果有其他事务插入 id 值为 2、3、4 这一些记录的话,这些插入语句都会发生阻塞。
为什么这里退化为了间隙锁?
原因在于这里仅靠间隙锁也能避免幻读的问题。
- 如果这里加的是next-key lock,那就意味着其他事务无法更新/删除 id = 5 这条记录,但实际上即使更新/删除 id = 5 这条记录,也不会出现前后两次查询的结果集不同,查不到的还是查不到。
唯一索引范围查询
当唯一索引进行范围查询时,会对每一个扫描到的索引加 next-key 锁,然后如果遇到下面这些情况,会退化成记录锁或者间隙锁:
- 针对大于等于的范围查询,因为存在等值查询的条件,那么如果等值查询的记录是存在于表中,那么该记录的索引中的 next-key 锁会退化成记录锁。
- 针对小于或者小于等于的范围查询,要看条件值的记录是否存在于表中:
- 当条件值的记录不在表中,那么不管是小于还是小于等于条件的范围查询,扫描到终止范围查询的记录时,该记录的索引的 next-key 锁会退化成间隙锁,其他扫描到的记录,都是在这些记录的索引上加 next-key 锁。
- 当条件值的记录在表中,如果是小于条件的范围查询,扫描到终止范围查询的记录时,该记录的索引的 next-key 锁会退化成间隙锁,其他扫描到的记录,都是在这些记录的索引上加 next-key 锁;如果小于等于条件的范围查询,扫描到终止范围查询的记录时,该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录,都是在这些记录的索引上加 next-key 锁。
针对 大于或者大于等于 的范围查询
针对 大于 的范围查询的情况
假设事务 A 执行了这条范围查询语句:
select * from user where id > 15 for update;
此时,事务 A 在主键索引上加了两个next-key 锁:
- 在 id = 20 这条记录的主键索引上,加了范围为 (15, 20] 的 next-key 锁,意味着其他事务即无法更新或者删除 id = 20 的记录,同时无法插入 id 值为 16、17、18、19 的这一些新记录。
- 在特殊记录( supremum pseudo-record)的主键索引上,加了范围为 (20, +∞] 的 next-key 锁,意味着其他事务无法插入 id 值大于 20 的这一些新记录。
这里没有next-key锁都没有退化。
原因在于需要保证 前后两次查询 id>15 的结果集相同。即需要保证 id>15不会出现新纪录(间隙锁),并且已存在的记录不改变(记录锁)。
针对 大于等于 的范围查询的情况。
假设事务 A 执行了这条范围查询语句:
select * from user where id >= 15 for update;
此时事务 A 在主键索引上加了三个 X 型 的锁,分别是:
- 在 id = 15 这条记录的主键索引上,加了记录锁,范围是 id = 15 这一行记录;意味着其他事务无法更新或者删除 id = 15 的这一条记录;
- 在 id = 20 这条记录的主键索引上,加了 next-key 锁,范围是 (15, 20] 。意味着其他事务即无法更新或者删除 id = 20 的记录,同时无法插入 id 值为 16、17、18、19 的这一些新记录。
- 在特殊记录( supremum pseudo-record)的主键索引上,加了 next-key 锁,范围是 (20, +∞] 。意味着其他事务无法插入 id 值大于 20 的这一些新记录。
这里包含了一个等值查询操作,退化为了记录锁。原因同 唯一索引等值查询
针对 小于或者小于等于 的范围查询
针对 小于/小于等于 的范围查询时,查询条件值的记录 不存在 表中的情况
假设事务 A 执行了这条范围查询语句,注意查询条件值的记录(id 为 6)并不存在于表中。
select * from user where id < 6 for update;
此时,事务 A 在主键索引上加了三个 X 型的锁:
- 在 id = 1 这条记录的主键索引上,加了范围为 (-∞, 1] 的 next-key 锁,意味着其他事务无法更新或者删除 id = 1 的这一条记录,同时也无法插入 id 小于 1 的这一些新记录。
- 在 id = 5 这条记录的主键索引上,加了范围为 (1, 5] 的 next-key 锁,意味着其他事务无法更新或者删除 id = 5 的这一条记录,同时也无法插入 id 值为 2、3、4 的这一些新记录。
- 在 id = 10 这条记录的主键索引上,加了范围为 (5, 10) 的间隙锁,意味着其他事务无法插入 id 值为 6、7、8、9 的这一些新记录。
针对 小于或者小于等于 的唯一索引范围查询,如果条件值的记录不在表中,那么不管是「小于」还是「小于等于」的范围查询,扫描到终止范围查询的记录时,该记录中索引的 next-key 锁会退化成间隙锁,其他扫描的记录,则是在这些记录的索引上加 next-key 锁。
针对 小于等于 的范围查询时,查询条件值的记录 存在 表中的情况。
假设事务 A 执行了这条范围查询语句,注意查询条件值的记录(id 为 5)存在于表中。
select * from user where id <= 5 for update;
此时,事务 A 在主键索引上加了 2 个 X 型的锁:
- 在 id = 1 这条记录的主键索引上,加了范围为 (-∞, 1] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 1 的这一条记录,同时也无法插入 id 小于 1 的这一些新记录。
- 在 id = 5 这条记录的主键索引上,加了范围为 (1, 5] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 5 的这一条记录,同时也无法插入 id 值为 2、3、4 的这一些新记录。
为了避免幻读,这里的锁都不会退化
针对 小于 的范围查询时,查询条件值的记录 存在 表中的情况。
假设事务 A 执行了这条范围查询语句,注意查询条件值的记录(id 为 10)并不存在于表中。
select * from user where id < 10 for update;
此时,事务 A 在主键索引上加了三个 X 型的锁:
- 在 id = 1 这条记录的主键索引上,加了范围为 (-∞, 1] 的 next-key 锁,意味着其他事务无法更新或者删除 id = 1 的这一条记录,同时也无法插入 id 小于 1 的这一些新记录。
- 在 id = 5 这条记录的主键索引上,加了范围为 (1, 5] 的 next-key 锁,意味着其他事务无法更新或者删除 id = 5 的这一条记录,同时也无法插入 id 值为 2、3、4 的这一些新记录。
- 在 id = 10 这条记录的主键索引上,加了范围为 (5, 10) 的间隙锁,意味着其他事务无法插入 id 值为 6、7、8、9 的这一些新记录。
这里加锁与 “针对 小于/小于等于 的范围查询时,查询条件值的记录 不存在 表中的情况” 一致
非唯一索引等值查询
当使用非唯一索引进行等值查询的时候,因为存在两个索引,一个是主键索引,一个是非唯一索引(二级索引),所以在加锁时,同时会对这两个索引都加锁,但是对主键索引加锁的时候,只有满足查询条件的记录才会对它们的主键索引加锁。
针对非唯一索引等值查询时,查询的记录存不存在,加锁的规则也会不同:
- 当查询的记录「存在」时,由于不是唯一索引,所以肯定存在索引值相同的记录,于是非唯一索引等值查询的过程是一个扫描的过程,直到扫描到第一个不符合条件的二级索引记录就停止扫描,然后在扫描的过程中,对扫描到的二级索引记录加的是 next-key 锁,而对于第一个不符合条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。
- 当查询的记录「不存在」时,扫描到第一条不符合条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会对主键索引加锁。
记录不存在的情况
假设事务 A 对非唯一索引(age)进行了等值查询,且表中不存在 age = 25 的记录。
select * from user where age = 25 for update;
定位到第一条不符合查询条件的二级索引记录,即扫描到 age = 39,于是该二级索引的 next-key 锁会退化成间隙锁,范围是 (22, 39)。
此时事务 A 在 age = 39 记录的二级索引上(INDEX_NAME: index_age ),加了范围为 (22, 39) 的 X 型间隙锁。
此时,如果有其他事务插入了 age 值为 23、24、25、26、....、38 这些新记录,那么这些插入语句都会发生阻塞。
原因在于需要避免出现幻读,所以需要加这个间隙锁
但是对于插入 age = 39 记录的语句,在一些情况是可以成功插入的,而一些情况则无法成功插入
当有一个事务持有二级索引的间隙锁 (22, 39) 时,什么情况下,可以让其他事务的插入 age = 22 或者 age = 39 记录的语句成功?又是什么情况下,插入 age = 22 或者 age = 39 记录时的语句会被阻塞?
插入 age = 22的记录
- 当其他事务插入一条 age = 22,id = 3 的记录的时候,在二级索引树上定位到插入的位置,而该位置的下一条是 id = 10、age = 22 的记录,该记录的二级索引上没有间隙锁,所以这条插入语句可以执行成功。
- 当其他事务插入一条 age = 22,id = 12 的记录的时候,在二级索引树上定位到插入的位置,而该位置的下一条是 id = 20、age = 39 的记录,正好该记录的二级索引上有间隙锁,所以这条插入语句会被阻塞,无法插入成功。
插入age = 39的记录
- 当其他事务插入一条 age = 39,id = 3 的记录的时候,在二级索引树上定位到插入的位置,而该位置的下一条是 id = 20、age = 39 的记录,正好该记录的二级索引上有间隙锁,所以这条插入语句会被阻塞,无法插入成功。
- 当其他事务插入一条 age = 39,id = 21 的记录的时候,在二级索引树上定位到插入的位置,而该位置的下一条记录不存在,也就没有间隙锁了,所以这条插入语句可以插入成功。
当有一个事务持有二级索引的间隙锁 (22, 39) 时,插入 age = 22 或者 age = 39 记录的语句是否可以执行成功,关键还要考虑插入记录的主键值,因为「二级索引值(age列)+主键值(id列)」才可以确定插入的位置,确定了插入位置后,就要看插入的位置的下一条记录是否有间隙锁,如果有间隙锁,就会发生阻塞,如果没有间隙锁,则可以插入成功。
记录存在的情况
假设事务 A 对非唯一索引(age)进行了等值查询,且表中存在 age = 22 的记录。
select * from user where age = 22 for update;
- 由于不是唯一索引,所以肯定存在值相同的记录,于是非唯一索引等值查询的过程是一个扫描的过程,最开始要找的第一行是 age = 22,于是对该二级索引记录加上范围为 (21, 22] 的 next-key 锁。同时,因为 age = 22 符合查询条件,于是对 age = 22 的记录的主键索引加上记录锁,即对 id = 10 这一行加记录锁。
- 接着继续扫描,扫描到的第二行是 age = 39,该记录是第一个不符合条件的二级索引记录,所以该二级索引的 next-key 锁会退化成间隙锁,范围是 (22, 39)。
- 停止查询。
此时,事务 A 对主键索引和二级索引都加了 X 型的锁:
- 主键索引:
- 在 id = 10 这条记录的主键索引上,加了记录锁,意味着其他事务无法更新或者删除 id = 10 的这一行记录。
- 二级索引(非唯一索引):
- 在 age = 22 这条记录的二级索引上,加了范围为 (21, 22] 的 next-key 锁,意味着其他事务无法更新或者删除 age = 22 的这一些新记录,不过对于插入 age = 20 和 age = 21 新记录的语句,在一些情况是可以成功插入的,而一些情况则无法成功插入。
- 在 age = 39 这条记录的二级索引上,加了范围 (22, 39) 的间隙锁。意味着其他事务无法插入 age 值为 23、24、..... 、38 的这一些新记录。不过对于插入 age = 22 和 age = 39 记录的语句,在一些情况是可以成功插入的,而一些情况则无法成功插入。
插入age = 22 和 age = 21的情况
在 age = 22 这条记录的二级索引上,加了范围为 (21, 22] 的 next-key 锁,意味着其他事务无法更新或者删除 age = 22 的记录,针对是否可以插入 age = 21 和 age = 22 的新记录,分析如下:
- 是否可以插入 age = 21 的新记录,还要看插入的新记录的 id 值,如果插入 age = 21 新记录的 id 值小于 5,那么就可以插入成功,因为此时插入的位置的下一条记录是 id = 5,age = 21 的记录,该记录的二级索引上没有间隙锁。如果插入 age = 21 新记录的 id 值大于 5,那么就无法插入成功,因为此时插入的位置的下一条记录是 id = 10,age = 22 的记录,该记录的二级索引上有间隙锁。
- 是否可以插入 age = 22 的新记录,还要看插入的新记录的 id 值,其他事务插入 age 值为 22 的新记录时,如果插入的新记录的 id 值小于 10,那么无法插入成功;如果插入的新记录的 id 大于 10,还要看该新记录插入的位置的下一条记录是否有间隙锁,如果没有间隙锁则可以插入成功,如果有间隙锁,则无法插入成功(这里即使由于有间隙锁,无法插入成功)。
插入age = 39 的情况
在 age = 39 这条记录的二级索引上,加了范围 (22, 39) 的间隙锁。意味着其他事务无法插入 age 值为 23、24、..... 、38 的这一些新记录,针对是否可以插入age = 39 的新记录,分析如下:
- 是否可以插入 age = 39 的新记录,还要看插入的新记录的 id 值,其他事务插入 age 值为 39 的新记录时,如果插入的新记录的 id 值小于 20,那么插入语句会发生阻塞,如果插入的新记录的 id 大于 20,则可以插入成功。
这里加的三个锁,都是为了避免幻读现象的发生。记录锁,避免了age = 22,id = 10这条记录被更新或删除;另外两个二级索引锁,都避免了被新插入age = 22的记录
非唯一索引范围查询
非唯一索引范围查询,索引的 next-key lock 不会有退化为间隙锁和记录锁的情况,也就是非唯一索引进行范围查询时,对二级索引记录加锁都是加 next-key 锁。
事务 A 的这条范围查询语句:
select * from user where age >= 22 for update;
此时,事务 A 对主键索引和二级索引都加了 X 型的锁:
- 主键索引(id 列):
- 在 id = 10 这条记录的主键索引上,加了记录锁,意味着其他事务无法更新或者删除 id = 10 的这一行记录。
- 在 id = 20 这条记录的主键索引上,加了记录锁,意味着其他事务无法更新或者删除 id = 20 的这一行记录。
- 二级索引(age 列):
- 在 age = 22 这条记录的二级索引上,加了范围为 (21, 22] 的 next-key 锁,意味着其他事务无法更新或者删除 age = 22 的这一些新记录,不过对于是否可以插入 age = 21 和 age = 22 的新记录,还需要看新记录的 id 值,与前面一致,不再赘述。
- 在 age = 39 这条记录的二级索引上,加了范围为 (22, 39] 的 next-key 锁,意味着其他事务无法更新或者删除 age = 39 的这一些记录,也无法插入 age 值为 23、24、25、...、38 的这一些新记录。不过对于是否可以插入age = 39 的新记录,还需要看新记录的 id 值,与前面一致,不再赘述。
- 在特殊的记录(supremum pseudo-record)的二级索引上,加了范围为 (39, +∞] 的 next-key 锁,意味着其他事务无法插入 age 值大于 39 的这些新记录。
在 age >= 22 的范围查询中,明明查询 age = 22 的记录存在并且属于等值查询,为什么不会像唯一索引那样,将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁?
这是因为 age 字段是非唯一索引,不具有唯一性,所以如果只加记录锁(记录锁无法防止插入,只能防止删除或者修改),就会导致其他事务插入一条 age = 22 的记录,这样前后两次查询的结果集就不相同了,出现了幻读现象。
没有加索引的查询
如果锁定读查询语句,没有使用索引列作为查询条件,或者查询语句没有走索引查询,导致扫描是全表扫描。那么,每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞。
不只是锁定读查询语句不加索引才会导致这种情况,update 和 delete 语句如果查询条件不加索引,那么由于扫描的方式是全表扫描,于是就会对每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表。
因此,在线上在执行 update、delete、select ... for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。当然,带了索引可能也是走的全表扫描,但是不带索引肯定是全表扫描。