MySQL锁 —— 记录锁、间隙锁、临键锁、自增锁

港控/mmm° 2022-12-10 12:27 309阅读 0赞

> MySQL的事务隔离级别有：未提交读、已提交读、可重复读、可串行化，除了串行化，在不同的隔离级别下，都会存在不同的并发问题，MySQL默认隔离级别是可重复读，在这种隔离级别下，存在幻读的问题，MySQL是如何解决幻读问题的呢？

# 一、MySQL数据库锁介绍 #

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问，在MySQL数据库中，MyISAM引擎只支持表锁，而InnoDB引擎既支持表锁，也支持行锁。MySQL中的锁分为如下几种：

## 1.1、行锁 ##

InnoDB存储引擎实现了如下两种标准的行级锁：

共享锁（行锁）：Shared Locks

排它锁（行锁）：Exclusive Locks

S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

## **1.2、意向锁（Intention Lock）** ##

InnoDB存储引擎支持一种额外的锁方式，称之为意向锁，意向锁分为：

意向共享锁（表锁）：Intention Shared  Locks

意向排它锁（表锁）：Intention Exclusive Locks

## 1.3、行锁算法 ##

记录锁：Record Locks

间隙锁：Gap Locks

临键锁：Next-key Locks

## 1.4、自增锁 ##

自增锁：AUTO-INC Locking

# 二、InnoDB中的锁的基本类型 #

我们先新建一张表，新增一部分测试数据

CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `user`(`id`, `name`, `age`) VALUES (1, '张三', 19);
    INSERT INTO `user`(`id`, `name`, `age`) VALUES (5, '李四', 23);
    INSERT INTO `user`(`id`, `name`, `age`) VALUES (9, '王五', 29);
    INSERT INTO `user`(`id`, `name`, `age`) VALUES (12, '赵六', 16);

## 2.1、共享锁 ##

共享锁又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，不同的事务都能访问到数据，但是只能读不能修改。

加锁：

SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;

释放锁：

COMMIT/ROLLBACK;

<table> 
 <tbody> 
 <tr> 
 <th>Transaction A</th> 
 <th>Transaction B</th> 
 </tr> 
 <tr> 
 <td> BEGIN; SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> BEGIN; SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;&nbsp;&nbsp; &nbsp; </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> SELECT * FROM user WHERE id = 1 FOR UPDATE; (Blocking) </td> 
 </tr> 
 </tbody> 
</table>

## 2.2、排他锁 ##

排他锁: 又称为写锁或者互斥锁，简称X锁，排他锁不能与其他锁并存，事务A获取了一行数据的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有事务A可以对改行数据行进行读取和修改操作。

自动加锁：

DELETE / UPDATE / INSERT  默认加上X锁；

手动加锁：

SELECT * FROM user WHERE id = 1 FOR UPDATE;

释放锁

COMMIT/ROLLBACK;

<table> 
 <tbody> 
 <tr> 
 <th>Transaction A</th> 
 <th>Transaction B</th> 
 </tr> 
 <tr> 
 <td> BEGIN; SELECT * FROM user WHERE id = 1 FOR UPDATE;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> BEGIN; SELECT * FROM user WHERE id = 1 FOR UPDATE; &nbsp;(Blocking) </td> 
 </tr> 
 <tr> 
 <td>COMMIT;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> SELECT * FROM user WHERE id = 1 FOR UPDATE; </td> 
 </tr> 
 </tbody> 
</table>

> SELECT…FOR UPDATE，SELECT…LOCK IN SHAREMODE必须在一个事务中，当事务提交了，锁也就释放了。MySQL默认事务自动提交，因此在使用上述两句SELECT锁定语句时，务必加上BEGIN，START TRANSACTION或者修改MySQL事务修改成手动提交事务SET AUTOCOMMIT=0。

## 2.3、意向锁 ##

InnoDB存储引擎支持多粒度（granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度（fine granularity）上进行加锁，

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，意向锁分为意向共享锁和意向排他锁。

意向共享锁（Intention Shared Lock，简称IS锁）：事务想要获得一张表中某几行的共享锁，表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（Intention Exclusive Lock，简称IX锁）：事务想要获得一张表中某几行的排他锁，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性下表所示

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

## 2.4、记录锁(Record Lock) ##

记录锁Record Lock是去锁住索引记录，所以记录锁也叫行锁，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。例如：

SELECT * FROM user WHERE id = 1 FOR UPDATE;

user表存在一条id为1的数据，执行完上述SQL，会在 id=1 的这条记录上加上记录锁，其他事务再对 id=1 这一行数据进行更新或删除时，会被阻塞。

<table> 
 <tbody> 
 <tr> 
 <th>Transaction A</th> 
 <th>Transaction B</th> 
 </tr> 
 <tr> 
 <td> BEGIN; SELECT * FROM user WHERE id = 1 FOR UPDATE;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> BEGIN; UPDATE user SET age = 21 WHERE id = 1; &nbsp;(Blocking) </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> UPDATE user SET age = 21 WHERE id = 5; Affected rows: 1 </td> 
 </tr> 
 <tr> 
 <td>COMMIT;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> UPDATE user SET age = 21 WHERE id = 1; Affected rows: 1 ROLLBACK; </td> 
 </tr> 
 </tbody> 
</table>

## 2.5、间隙锁(Gap Lock)（重点） ##

间隙锁，锁定一个范围，但不包含记录本身，间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

![202009261052065.png][]

Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内，避免幻读（Phantom Problem）问题的产生。我们执行一SQL语句：SELECT \* FROM user WHERE id > 5 AND id < 9  FOR UPDATE，此时间隙锁Gap Lock锁定（5，9）这个范围，我们在Transaction B事务里插入索引值为7的记录时，执行会被阻塞，Transaction A中的用户再次执行同样查询时返回相同的记录，Phantom Problem问题就可以避免，我们在Transaction B事务里插入索引值为11的记录时，因为11不在锁定的范围，执行新增操作成功。

<table> 
 <tbody> 
 <tr> 
 <th>Transaction A</th> 
 <th>Transaction B</th> 
 </tr> 
 <tr> 
 <td> BEGIN; SELECT * FROM user WHERE id &gt; 5 AND id &lt; 9 &nbsp;FOR UPDATE;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> BEGIN; INSERT INTO user VALUES (7, '钱七',26); &nbsp;(Blocking) </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td>INSERT INTO user VALUES (11, '钱七',26); Affected rows: 1 </td> 
 </tr> 
 <tr> 
 <td>COMMIT;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> ROLLBACK; </td> 
 </tr> 
 </tbody> 
</table>

产生间隙锁的条件（REPEATABLE READ事务隔离级别下；）：

使用普通索引锁定；使用多列唯一索引；使用唯一索引锁定多行记录。

用户可以通过以下两种方式来显式地关闭Gap Lock：

*  将事务的隔离级别设置为READ COMMITTED
 *  将参数innodb\_locks\_unsafe\_for\_binlog设置为1

SELECT * FROM user WHERE id = 1 FOR UPDATE;

上面这条语句，就只会产生记录锁，不会产生间隙锁。

## 2.6、临键锁（Next-key Lock）（重点） ##

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法（Next-Key Lock=Gap Lock+Record Lock），InnoDB对于行的查询采用的就是这种锁定算法。Next-Key Lock主要是为了解决幻读（hantom Problem）的问题，Next-Key Lock锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。

根据前面新增的user表数据，那么该索引可能被Next-Key Locking的区间为：（-∞，1\]、(1，5 \] 、(5，9 \] 、(9，12 \] 、(12，+∞），如下图所示：

![2020092610500632.png][]

当查询的索引含有唯一属性时，也就是查询的列是主键索引或唯一索引时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，InnoDB存储引擎依然使用Next-Key Lock进行锁定。

InnoDB存储引擎采用Next-Key Locking的算法避免Phantom Problem。

我们执行一条SQL语句：SELECT \* FROM user WHERE id > 7 AND id < 11  FOR UPDATE，锁住的不是9这单个值，而是对(5，9 \] 、(9，12 \] 这2个区间加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免Phantom Problem。

<table> 
 <tbody> 
 <tr> 
 <th>Transaction A</th> 
 <th>Transaction B</th> 
 </tr> 
 <tr> 
 <td> BEGIN; SELECT * FROM user WHERE id &gt; 7 AND id &lt; 11 &nbsp;FOR UPDATE;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> BEGIN; INSERT INTO user VALUES (10, '钱七',26); &nbsp;(Blocking) </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td>INSERT INTO user VALUES (8, '钱七',26); &nbsp;(Blocking)</td> 
 </tr> 
 <tr> 
 <td>COMMIT;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> INSERT INTO user VALUES (8, '钱七',26); Affected rows: 1 ROLLBACK; </td> 
 </tr> 
 </tbody> 
</table>

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。而在事务隔离级别READ COMMITTED下，其仅采用Record Lock，因此在上述的示例中，会话A需要将事务的隔离级别设置为READ COMMITTED。

除了next-keylocking，还有previous-key locking技术，previous-key locking技术，那么锁定的区间为：（-∞，1）、\[1，5）、\[5，9）、\[9，12）、\[12，+∞），如下图所示：

![20200926112316625.png][]

注意：如果对非索引列进行锁定，则锁定的是整张表

<table> 
 <tbody> 
 <tr> 
 <th>Transaction A</th> 
 <th>Transaction B</th> 
 </tr> 
 <tr> 
 <td> BEGIN; SELECT * FROM user WHERE age &gt; 20 FOR UPDATE;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> BEGIN; INSERT INTO user VALUES (8, '钱七',16); &nbsp;(Blocking) </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>COMMIT;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td></td> 
 <td> ROLLBACK; </td> 
 </tr> 
 </tbody> 
</table>

## 2.7、自增锁 ##

自增长在数据库中是非常常见的一种属性，我们在新建一张表的时候，通常会对主键配置自增属性，比如：id int(11) NOT NULL AUTO\_INCREMENT。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

SELECT MAX(ai_col) FROM `user` FOR UPDATE;

> 但是执行完报错，没找到原因

事务提交和没提交，自增长计数器都会加1

<table> 
 <tbody> 
 <tr> 
 <th>Transaction</th> 
 </tr> 
 <tr> 
 <td> mysql&gt; BEGIN; </td> 
 </tr> 
 <tr> 
 <td>mysql&gt; SELECT max(id) FROM user; +-----------+ | max(id)&nbsp; | +-----------+ | &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp;| +-----------+ 1 row in set (0.00 sec)</td> 
 </tr> 
 <tr> 
 <td>mysql&gt; INSERT INTO user (name,age) VALUES ('钱七',21);</td> 
 </tr> 
 <tr> 
 <td>mysql&gt; SELECT max(id) FROM user; +-----------+ | max(id)&nbsp; | +-----------+ | &nbsp; &nbsp; &nbsp;13&nbsp; &nbsp; &nbsp;| +-----------+ 1 row in set (0.00 sec)</td> 
 </tr> 
 <tr> 
 <td>mysql&gt; ROLLBACK;</td> 
 </tr> 
 <tr> 
 <td>INSERT INTO user (name,age) VALUES ('钱七',21);</td> 
 </tr> 
 <tr> 
 <td>mysql&gt; SELECT max(id) FROM user; +-----------+ | max(id)&nbsp; | +-----------+ | &nbsp; &nbsp; &nbsp;14&nbsp; &nbsp; &nbsp;| +-----------+ 1 row in set (0.00 sec)</td> 
 </tr> 
 </tbody> 
</table>

我们可以看到，之前新增的数据id=13，但是ROLLBACK了，事务提交之后，再新增一条，id变成了14，而不是13，新增数据自增长计数器每次都会增加，与事务没有关系。

MySQL官方锁介绍

[https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html][https_dev.mysql.com_doc_refman_5.7_en_innodb-locking.html]

*  [Shared and Exclusive Locks][]
 *  [Intention Locks][]
 *  [Record Locks][]
 *  [Gap Locks][]
 *  [Next-Key Locks][]
 *  [Insert Intention Locks][]
 *  [AUTO-INC Locks][]
 *  [Predicate Locks for Spatial Indexes][]

[202009261052065.png]: /images/20221123/dfc429829aef44d9917adb09d63d14aa.png
[2020092610500632.png]: /images/20221123/d58ba31359cd471caf2a015f79486660.png
[20200926112316625.png]: /images/20221123/d440617cc32c4d808ed4e2f5c554b5b2.png
[https_dev.mysql.com_doc_refman_5.7_en_innodb-locking.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
[Shared and Exclusive Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-shared-exclusive-locks
[Intention Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-intention-locks
[Record Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
[Gap Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks
[Next-Key Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks
[Insert Intention Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
[AUTO-INC Locks]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-auto-inc-locks
[Predicate Locks for Spatial Indexes]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-predicate-locks