【MySQL】事务与锁（四）：事务并发控制方案（LBCC）

客官°小女子只卖身不卖艺 2022-11-21 11:27 205阅读 0赞

> 前言：[【MySQL】事务与锁（三）：事务并发控制方案（MVCC）][MySQL_MVCC]

在上一篇我们介绍了 MVCC 多版本并发控制，它可以保证多事务并发时读的一致性，但是还需要一种手段来保证并发写时的线程安全 --LBCC（Lock Based Concurrency Control，基于锁的并发控制）。

[官网][Link 1] 把锁分成了8 类。我们一般把前面的两个行级别的锁（Shared and ExclusiveLocks），和两个表级别的锁（Intention Locks）称为锁的基本模式。后面三个RecordLocks、GapLocks、Next-KeyLocks，我们把它们叫做锁的算法，也就是分别在什么情况下锁定什么范围。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]

## 1.锁的粒度 ##

InnoDB 里面既有行级别的锁，又有表级别的锁，我们先来分析一下这两种锁定粒度的一些差异。表锁，顾名思义，是锁住一张表；行锁就是锁住表里面的一行数据。锁定粒度，表锁肯定是大于行锁的。

**问题一**：那么加锁效率，表锁应该是大于行锁还是小于行锁呢？为什么？

大于。表锁只需要直接锁住这张表就行了，而行锁，还需要在表里面去检索这一行数据，所以表锁的加锁效率更高。

**问题二**：冲突的概率？表锁的冲突概率比行锁大，还是小？

大于。因为当我们锁住一张表的时候，其他任何一个事务都不能操作这张表。但是我们锁住了表里面的一行数据的时候，其他的事务还可以来操作表里面的其他没有被锁定的行，所以表锁的冲突概率更大。表锁的冲突概率更大，所以并发性能更低，这里并发性能就是小于。

**问题三**：InnoDB里面我们知道它既支持表锁又支持行锁， 另一个常用的存储引擎MyISAM支持什么粒度的锁？InnoDB 已经支持行锁了，那么它也可以通过把表里面的每一行都锁住来实现表锁，为什么还要提供表锁呢？

要搞清楚这个问题，我们就要来了解一下 InnoDB 里面的基本的锁的模式（lockmode），这里面有两个行锁和两个表锁。

## 2.锁的基本模式 ##

> 注：说在前面，下面所有事务相关操作前必须关闭自动提交，`set autocommit=off`。然后再`show variables like 'autocommit'`查看自动提交是否是off。

### 2.1 共享锁/排他锁（行级别读/写锁） ###

第一个行级别的锁就是我们在官网看到的 Shared Locks （共享锁），我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁，注意不要在加上了读锁以后去写数据，不然的话可能会出现死锁的情况。而且多个事务可以共享一把读锁。

*  加共享锁：查询指定记录时加上`LOCK IN SHARE MODE` 比如 `select * from student where id=1 lock in share mode`;
 *  释放共享锁：只要事务结束，锁就会自动释放，包括提交事务和结束事务

下面我们也来验证一下，看看共享锁是不是可以重复获取：

第二个行级别的锁叫做 Exclusive Locks（排它锁），它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

*  加排他锁（两种方式）：
    
     *  自动加锁：我们在操作数据的时候，包括增删改，都会**默认加上一个排它锁**
     *  手工加锁：我们用一个`FOR UPDATE`给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用
 *  释放排他锁：跟共享锁是一样的，也是事务结束自动释放

这个是两个行锁，接下来就是两个表锁。

### 2.2 意向共享锁/意向排他锁（表级别读/写锁） ###

意向锁（Intention Locks）是什么呢？我们好像从来没有听过，也从来没有使用过，其实他们是由数据库自己维护的。也就是说，当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。反过来说：

*  如果一张表上面至少有一个**意向共享锁**，说明有其他的事务给其中的某些数据行加上了共享锁。
 *  如果一张表上面至少有一个**意向排他锁**，说明有其他的事务给其中的某些数据行加上了排他锁。

那么这两个表级别的锁存在的意义是什么呢？

我们想一下，如果说没有意向锁的话，当我们准备给一张表加上表锁的时候，我们首先要做什么？是不是必须先要去判断有没其他的事务锁定了其中了某些行？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低？

但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁，如果有，就直接返回失败。如果没有，就可以加锁成功。所以 InnoDB 里面的表锁，我们可以把它理解成一个**标志**。就像火车上厕所有没有人使用的灯，是用来提高加锁的效率的。

另外，我们有了表级别的锁，在InnoDB里面就可以支持更多粒度的锁。

*  加表锁：`LOCK TABLE 表名 READ/WRITE;`，read和write可以理解成意向共享锁和意向排他锁
 *  释放表锁：`UNLOCK TABLES;`

以上就是MySQL里面的4种基本的锁的模式，或者叫做锁的类型。

> 到这里我们要思考两个问题：
> 
>  *  **问题一**：锁的作用是什么？它跟Java里面的锁是一样的，是为了解决资源竞争的问题，Java里面的资源是对象，数据库的资源就是数据表或者数据行。所以锁是用来解决事务对数据的并发访问的问题的。
>  *  **问题二**：锁到底锁住了什么呢？当一个事务锁住了一行数据的时候，其他的事务不能操作这一行数据，那它到底是锁住了这一行数据，还是锁住了这一个字段，还是锁住了别的什么东西呢？锁的其实是索引，详情见 [行锁到底锁住的是什么？][Link 2]

## 3.行锁的算法 ##

行锁算法有三种，它们的区别就在于加锁的范围不同（因为我们用主键索引加锁，这里的划分标准就是主键索引的值）

> 假如现在我们有一张表 t，这张表有一个主键索引。然后我们插入了4行数据，主键值分别是1、4、7、10。

*  Record（记录）：这些数据库里面存在的主键值，我们把它叫做 Record，记录，那么t就有4个Record
 *  Gap（间隙）：根据主键，这些存在的Record隔开的数据不存在的区间，我们把它叫做 Gap，间隙，它是一个左开右开的区间
 *  Next-key（临键区间）：间隙（Gap）连同它左边的记录（Record），我们把它叫做临键的区间，它是一个**左开右闭**的区间

如下图，是表 t 对应的三个范围：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]

> 这里有一个问题，表 t 的主键索引是整型的，可以排序，所以才有这种区间。如果我的主键索引不是整形，是字符怎么办呢？字符可以排序吗？ 答：用ASCII码来排序。

下面我们就来看一下在不同的范围下，行锁是怎么表现的（以上图表 t 为基础）。

### 3.1 记录锁 ###

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10 。这个我们在前面实验已经看过了。我们使用不同的 key 去加锁，不会冲突，它只锁住这个 record。

### 3.2 间隙锁 ###

第二种情况，当我们查询的记录不存在，没有命中任何一个record，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

举个例子，where id>4 and id <7，where id = 6。

<table> 
 <thead> 
  <tr> 
   <th align="left">Transaction1</th> 
   <th align="left">Transaction2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">Begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">SELECT * FROM t where id&gt;4 and id&lt;7 FOR UPDATE;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">Begin;</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">INSERT INTO<code>t</code>(<code>id</code>,<code>name</code>) VALUES(5,‘5’); // BLOCKED<br>INSERT INTO<code>t</code>(<code>id</code>,<code>name</code>) VALUES(6,‘6’); // BLOCKED<br>SELECT * FROM t where id=6 FOR UPDATE; // OK</td> 
  </tr> 
  <tr> 
   <td align="left">SELECT * FROM t where id&gt;20 FOR UPDATE;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">INSERT INTO<code>t</code>(<code>id</code>,<code>name</code>) VALUES(11,‘11’); // BLOCKED</td> 
  </tr> 
 </tbody> 
</table>

重复一遍，当查询的记录不存在的时候，使用间隙锁。注意，间隙锁主要是阻塞插入insert，而没有阻塞select。相同的间隙锁之间不冲突。

> 注：Gap Lock 只在隔离级别 RR 中存在。如果要关闭间隙锁，就是把事务隔离级别设置成 RC，并且把 innodb\_locks\_unsafe\_for\_binlog设置为ON。这种情况下除了外键约束和唯一性检查会加间隙锁，其他情况都不会用间隙锁。

### 3.3 临键锁 ###

第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。

临键所两种退化的情况：

*  记录锁：唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。
 *  间隙锁：没有匹配到任何记录的时候，退化成间隙锁。

比如我们查询区间(5, 9)， 它包含了记录不存在的区间，也包含了一个Record 7。

<table> 
 <thead> 
  <tr> 
   <th align="left">Transaction1</th> 
   <th align="left">Transaction2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">Begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">SELECT * FROM t where id&gt;5 and id&lt;9 FOR UPDATE;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">Begin;</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">SELECT * FROM t where id=4 FOR UPDATE; // OK<br>INSERTINTO<code>t</code>(<code>id</code>,<code>name</code>) VALUES(6,‘6’); // BLOCKED<br>INSERTINTO<code>t</code>(<code>id</code>,<code>name</code>) VALUES(8,‘8’); // BLOCKED<br>SELECT * FROM t where id=10 FOR UPDATE; // BLOCKED</td> 
  </tr> 
 </tbody> 
</table>

临键锁与间隙锁不同的是，它除了锁住原本的临键区间，还会锁住最后一个key的下一个左开右闭的区间：

select * from t where id>5 and id<=7 for update; -- 锁住(4,7]和(7,10] 
    select * from t where id>8 and id<=10 for update; -- 锁住 (7,10]和(10,+∞)

为什么要锁住下一个左开右闭的区间？——为了解决幻读的问题。所以，我们看下MySQL InnoDB里面事务隔离级别的实现。为什么 InnoDB 的RR 级别能够解决幻读的问题，就是用临键锁实现的。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]

[MySQL_MVCC]: https://yzhyaa.blog.csdn.net/article/details/109438837
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/b57b1f55da6e41b2962b04f196ec6db1.png
[Link 2]: https://blog.csdn.net/weixin_43935927/article/details/109438506
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/a71821d057064174b1539d3cd3230c9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/da5936b8be3b456aadcedbfb654616db.png