Mysql-Mysql到底是如何上锁的?

Mysql

发布日期: 2023-02-20

1. 什么样的SQL语句会加行级锁?

普通的select语句属于快照读,它不会通过加锁来实现并发安全,而是通过MVCC实现并发安全

如果要在查询的时候对记录加行级锁,那么就可以使用以下两种方式,这两种查询会给相关的记录加锁,这种查询方式叫做锁定读

//对读取的记录加S锁
select ... lock in share mode;
//对读取的记录加X锁
select ... for update

注意:锁的特性是必须在事务中执行,当事务结束之后,锁就被释放了,因此在加锁之前须需要开启事务

除了这两种查询语句会加行级锁之外,update和delete类型都会加独占锁

//对操作的记录加独占锁
update table ... where id = 1;
//对操作的记录加独占锁
delete from table ... where id = 1;

2. 具体加锁过程?

行级锁的加锁规则比较复杂,在不同的场景下加锁的形式是不同的

加锁的对象是索引
加锁的基本单位是next-key lock,它是由记录锁和间隙锁组合而成的
查找过程中访问到的对象才会加锁

next- key lock的区间是(],而gap lock的加锁区间是()

但是,next-key lock在一些场景下会退化成记录锁或者间隙锁,比如说在使用gap lock或者record lock就能够解决幻读问题的情况下,就会使得next-key lock退化为记录锁或者间隙锁。

3. 使用唯一索引的等值查询过程是如何加锁的?

当使用唯一索引进行等值查询的时候,查询的记录存在与否,会导致加锁的规则不一致

当查询的记录是存在的时候,在索引树上定位到这一条记录后,会将记录的索引中的next-key lock退化为record lock

如果记录存在,那么就是能够精准命中这条记录,因此在这种情况下,就会为这条记录加上一个X型的独占锁,如果有其他事务对这条记录进行update或者delete的话,就都会被阻塞

有什么命令可以分析加了什么锁?

select * from performance_schema.data_lock\G;

因为锁退化为了record lock,因此加了两种锁

表级意向X锁:代表当前表中有记录被上锁了
行级X锁:代表id = 1 的那条记录被上锁了,其他记录不能够修改

如果 LOCK_MODE 为 X，说明是 next-key 锁；

如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；

如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

为什么唯一索引的等值查询并且记录存在的场景下会导致记录中的索引next-key lock会退化为记录锁?

首先要搞清楚,为什么要退化?这个锁明明也能解决问题啊,原因在于,当锁的粒度越大,越容易发生死锁

原因就是在唯一索引等值查询并且查询记录存在的场景下,仅靠记录锁也能够避免幻读的问题

幻读:当一个事务前后两次查询的结果集的数量不相同的时候,就认为发生了幻读,因此,要避免幻读的发生,就是要避免某一个范围内被插入或者删除

那么分析我们的原场景

由于主键具有唯一性,所以其他事务插入id = 1的时候,会因为主键冲突,导致无法插入id = 1的新记录,这样的话事务在多次查询id = 1的时候,就不会出现新的记录,解决了第一个插入异常问题
那么要解决的问题就是,避免这条记录被删除,那么很简单,只需要给这条记录上一个记录锁,就可以对其他事务的操作做一个阻塞操作,从而解决了第二个删除异常的问题

因此,从这个角度上看,记录锁就能够解决幻读问题,那么next-key lock就会退化为记录锁

当查询的记录是不存在的时候,在索引树找到第一条大于这条查询记录的记录后,就会将记录中的索引nect-key lock退化为gap lock

如果使用二级索引进行锁定读查询的时候,除了会对二级索引项加一个行级锁之外,还会对查询到的记录的主键索引上加上记录锁。

select * from user where id = 2 for update;

假设事务A执行了这条等值查询语句,查询的记录是不存在于表中的,通过查询的SQL可知,加的锁是一个意向X锁+X型的间隙锁

为什么会退化为间隙锁?

原因依然是通过间隙锁就能够解决幻读的问题

我们知道,如果要防止再次查询id = 2的时候出现新的记录,那么就最保险的做法就是禁止在2附近的区间插入数据,那么在这里的话如何确定的呢?首先事务会通过索引的查询规则,查找到大于这条记录的第一条记录,那么这就是第一个边界,然后再去查询这第一个边界的上一条记录,就能够确定出来这个区间了

因此只需要加上一个间隙锁就能够防止其他事务插入id = 2的记录了

至于为什么不加记录锁,这是因为记录锁要上锁的前提是这个记录存在,而记录都不存在了,那么自然就不能上这个锁了

4. 使用唯一索引的范围查询过程是如何加锁的?

当唯一索引进行范围查询的时候,会对每一个扫描到的索引加一个next-key锁,(]区间,如果遇到下面的情况,就会退化为记录锁或者间隙锁

情况1:当查询条件是>=的范围查询,因为存在着等值查询的条件,如果等值查询的记录是存在于表中的,那么这个记录就会退化为record锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 20 | 香克斯    |  39 |
+----+-----------+-----+
1 row in set (0.01 sec)

问你这种情况下加了什么锁?首先它要找的第一行是id = 20,由于这个查询不是一个等值的查询,所以对该主键索引要加的是范围为(15,20]的锁,在这个过程中,由于id = 20的记录被记录到了,因此不会退化

第二个,这个查询条件是id > 15,因此会查询到后续所有的记录,但是20就是最后一条记录,因此在这时候,就会使用该页中的最后一条记录作为正无穷的一个标识,也就是再加一个(20,+无穷),这种情况下一共加了两个锁

来看这一条查询语句

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id >= 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 15 | 乌索普    |  20 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.00 sec)

在这条查询语句中,首先查询到了id = 15记录是存在的,因此此时next-key lock将会退化为记录锁,也就是先上一把锁,将id = 15的记录给锁住

接着,由于是范围查询,因此主键索引next-key lock就会退化为记录锁,继续向后扫描

接着扫描到id = 20,于是上了一个(15,20]的next-key lock

然后是无穷范围的(20,+无穷]

情况2:针对< || <=的范围查询,要看条件值是否存在于表中

当条件值的记录不在表中的时候,那么不管是小于还是小于等于条件的范围查询都会退化为间隙锁,这是因为表中的记录不在表中,因此这种情况下不需要锁住那一条记录

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id < 6 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
|  5 | 索隆   |  21 |
+----+--------+-----+
3 rows in set (0.00 sec)

来看这个查询

①注意索引树的结构,是从小的索引值指向大的索引值,因此先查询到id = 1 的记录,此时访问到了id = 1的对象,因此需要对这条记录加锁,因此加的第一个锁是(-无穷,1]

②然后查询到第二条记录,id = 5,于是再上一把锁(1,5]

③然后接着扫描到6,发现并没有6这个对象,于是上了一个间隙锁(5,6)

结束

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id <= 5 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
|  5 | 索隆   |  21 |
+----+--------+-----+
2 rows in set (0.00 sec)

首先这个加锁的过程和之前的加锁过程也是类似的,只不过:

②然后查询到第二条记录,id = 5,于是再上一把锁(1,5]

在这之后就结束了,因为后续不涉及到访问

5. 使用非唯一索引的等值查询过程是如何加锁的?

关于非索引的查询,就是其结束条件改变了而已,也就是什么时候停止或者什么时候开始

我们以例子的方式进行理解

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age = 25 for update;
Empty set (0.00 sec)

在这个例子中,首先age是一个等值的查询,然后对应的记录不在表中,我们来分析一下加了哪些锁

在这个例子中,25夹在22和39之间,那么避免在这个区间产生幻读的现象,因此加了一把锁(22,39),退化为间隙锁

同时,知道一个原则:访问到了的对象就会加锁,我们利用二级索引进行查询,而且没有做索引覆盖,所以肯定要回表查别的数据,因此在主表中,主索引(10,20)就会被上锁

二级索引树是按照二级索引值（age列）按顺序存放的，在相同的二级索引值情况下，再按主键 id 的顺序存放。知道了这个前提，我们才能知道执行插入语句的时候，插入的位置的下一条记录是谁。

插入age = 22,id = 3能成功吗?

可以成功,因为都没有锁的冲突

插入age = 22,id = 12能成功吗

不能够成功,因为id=12在间隙(10,20)中

来看看关于等值查询-值存在的现象

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age = 22 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 10 | 山治   |  22 |
+----+--------+-----+
1 row in set (0.00 sec)

由于非唯一索引的限制,而且为了避免再插入age = 22的情况,因此先分析对二级索引的加锁情况:

第一把锁:锁住本记录因此需要加一个next-key lock,锁的范围是(21,22],可以避免左边被插入22
第二把锁:锁住右边的间隙,因此需要加一个gap lock,锁的范围是(22,39)

同时,由于需要回表,因此主索引中的也需要上锁:

第一把锁,锁住本记录,因此需要加一个record lock,锁住10

6. 使用非唯一索引的范围查询过程是如何加锁的?

根据原则,我们直接来看sql吧

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age >= 22  for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 10 | 山治      |  22 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.01 sec)