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

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

一、MySQL数据库锁介绍

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

1.1、行锁

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

共享锁（行锁）：Shared Locks

排它锁（行锁）：Exclusive Locks

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

	X	S
X	不兼容	不兼容
S	不兼容	兼容

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;

Transaction A	Transaction B
BEGIN; SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;
	BEGIN; SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;
	SELECT * FROM user WHERE id = 1 FOR UPDATE; (Blocking)

2.2、排他锁

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

自动加锁：

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

手动加锁：

SELECT * FROM user WHERE id = 1 FOR UPDATE;

释放锁 

COMMIT/ROLLBACK;

Transaction A	Transaction B
BEGIN; SELECT * FROM user WHERE id = 1 FOR UPDATE;
	BEGIN; SELECT * FROM user WHERE id = 1 FOR UPDATE;  (Blocking)
COMMIT;
	SELECT * FROM user WHERE id = 1 FOR UPDATE;

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存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性下表所示

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

	X	IX	S	IS
X	互斥	互斥	互斥	互斥
IX	互斥	兼容	互斥	兼容
S	互斥	互斥	兼容	兼容
IS	互斥	兼容	兼容	兼容

2.4、记录锁(Record Lock)

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

SELECT * FROM user WHERE id = 1 FOR UPDATE;

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

Transaction A	Transaction B
BEGIN; SELECT * FROM user WHERE id = 1 FOR UPDATE;
	BEGIN; UPDATE user SET age = 21 WHERE id = 1;  (Blocking)
	UPDATE user SET age = 21 WHERE id = 5; Affected rows: 1
COMMIT;
	UPDATE user SET age = 21 WHERE id = 1; Affected rows: 1 ROLLBACK;

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

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

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不在锁定的范围，执行新增操作成功。

Transaction A	Transaction B
BEGIN; SELECT * FROM user WHERE id > 5 AND id < 9  FOR UPDATE;
	BEGIN; INSERT INTO user VALUES (7, '钱七',26);  (Blocking)
	INSERT INTO user VALUES (11, '钱七',26); Affected rows: 1
COMMIT;
	ROLLBACK;

产生间隙锁的条件（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，+∞），如下图所示：

当查询的索引含有唯一属性时，也就是查询的列是主键索引或唯一索引时，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。

Transaction A	Transaction B
BEGIN; SELECT * FROM user WHERE id > 7 AND id < 11  FOR UPDATE;
	BEGIN; INSERT INTO user VALUES (10, '钱七',26);  (Blocking)
	INSERT INTO user VALUES (8, '钱七',26);  (Blocking)
COMMIT;
	INSERT INTO user VALUES (8, '钱七',26); Affected rows: 1 ROLLBACK;

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，+∞），如下图所示：

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

Transaction A	Transaction B
BEGIN; SELECT * FROM user WHERE age > 20 FOR UPDATE;
	BEGIN; INSERT INTO user VALUES (8, '钱七',16);  (Blocking)
COMMIT;
	ROLLBACK;

2.7、自增锁

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

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

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

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

Transaction
mysql> BEGIN;
mysql> SELECT max(id) FROM user; +-----------+ | max(id)  | +-----------+ |      12     | +-----------+ 1 row in set (0.00 sec)
mysql> INSERT INTO user (name,age) VALUES ('钱七',21);
mysql> SELECT max(id) FROM user; +-----------+ | max(id)  | +-----------+ |      13     | +-----------+ 1 row in set (0.00 sec)
mysql> ROLLBACK;
INSERT INTO user (name,age) VALUES ('钱七',21);
mysql> SELECT max(id) FROM user; +-----------+ | max(id)  | +-----------+ |      14     | +-----------+ 1 row in set (0.00 sec)

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

MySQL官方锁介绍

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