【MySQL】谈谈锁的类型

前言

MySQL中的锁分为表锁以及行锁，从字面意思就可以得知，表锁是对一整张表进行加锁，而行锁是针对于特定的行。在Server层面，提供了表锁的实现，而行锁则由存储引擎实现。Innodb引擎支持行锁，Myisam则不支持行锁。

下面从锁模式以及加锁方法来大致阐述Mysql中的锁。

锁模式

锁模式分为读锁、写锁、意向锁

读锁

读锁，也称共享锁（Share Lock），可以简称为S锁。

某个事务对某行或某表加了读锁后，其他事务依然可以读取，但不能修改。

可以同时有多个事务对某行或某表加读锁。

写锁

写锁，也称独占锁（Exclusive Lock）、排它锁等，可以简称为X锁。

某个事务对某行或某表加了写锁后，其他事务不可以读取或更改记录。

同一时刻，只能有一个事务对对某行或某表加写锁。

意向锁

这里的意向锁也分为读意向锁（Intention Share Lock，简称IS锁）以及写意向锁（Intention Exclusive Lock，简称IX锁）。

当事务1对表中的某条记录加X锁后，事务2想对整张表加X锁，于是事务2需要遍历该表中的所有记录，判断是否有记录存在X锁。如果有一条记录被加了X锁，则事务2需要等待事务1完成。

这种遍历的方式非常低效，Mysql在后来引入了意向锁的概念，用来解决这种问题。当事务1对某条记录加X锁前，首先需要对表加IX锁。当事务2需要对表加X锁时，只需要判断表上是否含有IX锁，如果有，则进行等待。

当然，意向锁不会和行锁冲突，意向锁只会阻塞对表的S锁或X锁。事务1对表加IX锁，然后对记录a加X锁，事务2需要修改记录b时，并不需要判断是否存在意向锁。

加锁方法

记录锁

记录锁，也称为Record Lock，是行锁中最简单的实现。比如，对于表student，id为主键，name为普通索引，age为普通字段

update student set name="tom" where id=1;

则会锁住主键索引上id为1的索引记录，记住，锁的是索引项，并不是真正的数据行。

update student set age=18 where name='张三';

则首先会在name=张三的二级索引加锁，然后拿到主键id=11后，还会在id=11的主键索引上加锁。不清楚聚集索引与非聚集索引的同学，可以参考我的这一篇文章浅析Innodb的聚集索引与非聚集索引

如果是这样子的语句呢？

update student set name="jack" where age=22;

很明显，没有用到索引列，则Mysql会进行全表扫描，客户端发送更新语句到server后，server首先通知存储引擎取出第一条数据，并对其加上X锁，若满足age=22，则更新name为jack，最后释放锁。接着取出第二条，并依然对其加X锁，但不满足条件的话，则释放锁，一直循环下去。

事实上，当where条件中包含索引列时，Mysql可能也会选择不走索引查询，当执行全表扫描的代价小于（查询二级索引+主键索引，即需要回表）时，Mysql此时会直接进行全表扫描。

间隙锁

间隙锁也称为Gap Lock，顾名思义，锁的是一段间隙，至于这段间隙有多大，什么时候会触发间隙锁，需要分情况讨论。

在可重复读(RR)的隔离级别下，才会有间隙锁的产生。不清楚数据库的隔离级别，可以参考我的另外一篇文章事务隔离级别

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 

以下情况均处于RR的隔离级别下：

现在有这样一张表：其中id为主键索引，a为普通索引

  1. CREATE TABLE t_lock (
  2.   `id` int(11) NOT NULL,
  3.   `a` int(11),
  4.   PRIMARY KEY (`id`) USING BTREE,
  5.   INDEX `index_a`(`a`) USING BTREE
  6. ) ENGINE = InnoDB;

有以下的初始数据：

第一种情况，使用主键索引，查找一条存在的记录。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select * from t_lock where id=1 for update;
  5. +----+------+
  6. | id | a    |
  7. +----+------+
  8. |  1 |    4 |
  9. +----+------+
  10. 1 row in set (0.00 sec)
  11.  
  12. mysql>

select ....for update，使用的是当前读，也称为一致性锁定读，将会在后面进行说明。

这种方式不会产生间隙锁，只会产生一条记录锁，锁住主键索引中id=1的索引项。其他事物更新、新增、删除其他id不受影响。

第二种情况，使用主键索引，使用范围查找记录。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select * from t_lock where id>4 and id<16 for update;
  5. +----+------+
  6. | id | a    |
  7. +----+------+
  8. |  7 |   11 |
  9. +----+------+
  10. 1 row in set (0.00 sec)
  11.  
  12. mysql>

我们尝试在另外一个终端增加数据：

  1. insert into t_lock values(3,8);   成功
  2.  
  3. insert into t_lock values(5,8);   阻塞
  4.  
  5. insert into t_lock values(13,8);  阻塞
  6.  
  7. insert into t_lock values(16,8);  阻塞
  8.  
  9. insert into t_lock values(23,8);  成功

id>4 and id<16将会锁定间隙（4，16），其中不包含7和14，其他事务尝试在该区间内删除、更新、新增记录都会被阻塞。

第三种情况，使用主键索引，查找一条不存在的记录。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select * from t_lock where id=9 for update;
  5. Empty set (0.00 sec)
  6.  
  7. mysql>

在另外一个终端尝试添加数据：

  1. insert into t_lock values(3,8);   成功
  2.  
  3. insert into t_lock values(8,8);   阻塞
  4.  
  5. insert into t_lock values(9,8);   阻塞
  6.  
  7. insert into t_lock values(13,8);  阻塞
  8.  
  9. insert into t_lock values(15,8);  成功

可以看得出来，因为id=9的记录不存在，而9属于（7，14），因此，此时的间隙锁的范围为（7，14）。

以上，讨论的是间隙锁在主键索引上的表现，其实和在唯一索引上的表现相同，以下讨论在普通索引上的表现。

还是原来的表，原来的数据，我复制一下：id为主键索引，a为普通索引

第一种情况，使用普通索引，查找一条存在的记录。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select * from t_lock where a=9 for update;
  5. +----+------+
  6. | id | a    |
  7. +----+------+
  8. |  2 |    9 |
  9. +----+------+
  10. 1 row in set (0.00 sec)
  11.  
  12. mysql>

在另外一个终端尝试添加数据：

  1. insert into t_lock values(2,8);  阻塞
  2.  
  3. insert into t_lock values(9,8);  阻塞
  4.  
  5. insert into t_lock values(100,8); 阻塞
  6.  
  7. insert into t_lock values(8,10);  阻塞
  8.  
  9. insert into t_lock values(3,6);  成功
  10.  
  11. insert into t_lock values(8,11);  成功

实验证明：a=9将会产生间隙锁，锁住的二级索引的范围的规律是，以a=9，id=2为基础，向上寻找最近的二级索引项，即a=6，id=4，再向下寻找最近的二级索引项，即a=11，id=7，锁住的间隙即为二者间隙。

当a相同时，id将会升序排序。

本例中，values(3，6)为a=6，id=4索引的左边，因此能执行成功，同理，values(8，11)为二级索引a=11，id=7的右边，因此也能执行成功。

第二种情况，使用普通索引，使用范围查找记录。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select * from t_lock where a>5 and a<11 for update;
  5. +----+------+
  6. | id | a    |
  7. +----+------+
  8. |  4 |    6 |
  9. |  2 |    9 |
  10. +----+------+
  11. 2 rows in set (0.00 sec)
  12.

在另外一个终端尝试添加数据：

  1. insert into t_lock values(0,4);  成功
  2.  
  3. insert into t_lock values(0,5);  阻塞
  4.  
  5. insert into t_lock values(3,5);  阻塞
  6.  
  7. insert into t_lock values(3,11); 阻塞
  8.  
  9. insert into t_lock values(6,11); 阻塞
  10.  
  11. insert into t_lock values(8,11); 成功

同理，在a>5 and a<11的范围内，找到最左边的二级索引项，即a=4，id=1，那么如果在此索引项的左边，即可插入成功，因此values(0，4)插入成功。

最右边的二级索引项，即a=11，id=7，那么如果在此索引项的右边，即可插入成功，因此values(8，11)插入成功。

第三种情况，使用普通索引，查找一条不存在的记录。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> select * from t_lock where a=10 for update;
  5. Empty set (0.00 sec)
  6.  
  7. mysql>

在另外一个终端尝试添加数据：

  1. insert into t_lock values(3,9); 阻塞
  2.  
  3. insert into t_lock values(6,11); 阻塞
  4.  
  5. insert into t_lock values(8,11); 成功

同理，最左二级索引项为a=9，id=2，因此values(3，9)阻塞，最右二级索引为a=11，id=7，因此values(8，11)成功。

通过二者的对比，可以发现：

在普通索引上，不管怎么查，只要加锁，必定会产生间隙锁。

在主键索引或唯一索引上，只要记录存在，则间隙锁会变成记录锁。

临键锁

临键锁也称为Next-key Lock，是记录锁与间隙锁的组合，即包含索引记录，也包含索引区间。

快照读和当前读

快照读

简单的select操作(不包括 select ... lock in share mode, select ... for update)，读取的是历史数据，不保证是最新的数据。

当前读

比如以下语句：

  • select ... lock in share mode
  • select ... for update
  • insert
  • update
  • delete

在RR级别下，快照读是通过MVCC(多版本控制)和undo log来实现的，当前读是通过加记录锁和间隙锁，即临键锁来实现的。

posted @ 2023-01-04 12:26  DiligentCoder  阅读(72)  评论(0)  编辑  收藏  举报