【MySQL学习】9.锁

1 InnoDB中的锁

在MySQL官方文档中（version 5.7），InnoDB中有以下几种锁：

• Shared and Exclusive Locks：共享（乐观）锁、排他/互斥/独占锁
• Intention Locks：意向锁
• Record Locks：行锁
• Gap Locks：间隙锁
• Next-Key Locks：临键锁
• Insert Intention Locks：插入意向锁
• AUTO-INC Locks：自增锁
• Predicate Locks for Spatial Indexes：空间索引预测锁

总的来说，可以如下分类：

分类

2 解决并发事务问题

并发情况下，一方面，我们要充分利用数据库的并发访问，实现数据能被用户高效的获取；另一方面，又要保证线程或者说用户得到一致性的数据或者安全的修改数据。

而上一章讲过，一个事务进行读取操作，另一个进行改动操作，这种情况下可能发生脏读、不可重复读、幻读的问题。在SQL标准下，规定不同隔离级别下可能发生的问题不一样：

	脏读	幻读	不可重复读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	-	可能	可能
REPEATABLE READ	-	-	可能
SERIALIZABLE	-	-	-

而各个数据库厂商对SQL标准的支持都可能不一样，与 SQL标准不同的一点就是，MySQL 在 REPEATABLE READ 隔离级别实际上就基本解决了幻读问题。

	脏读	幻读	不可重复读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	-	可能	可能
REPEATABLE READ	-	-	—-
SERIALIZABLE	-	-	-

怎么解决脏读、不可重复读、幻读这些问题呢?其实有两种可选的解决方案：

2.1 方案一：读操作MVCC，写操作加锁

事务利用 MVCC 进行的读取操作称之为一致性读，或者一致性无锁读，也称之为快照读。

上一章节详细介绍了MVCC及其所解决的问题：在快照读的情况下，实现读写不冲突，并能尽量避免幻读情况发生。
即：

通过生成一个 ReadView， 然后通过 ReadView 找到符合条件的记录版本(历史版本是由 undo log构建的)， 其实就像是在生成 ReadView 的那个时刻做了一个快照，查询语句只能读到在生成 ReadView 之前已提交事务所做的更改，在生成 ReadView 之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用 MVCC 时，读-写操作并不冲突。

普通的 SELECT 语句在 READ COMMITTED 和 REPEATABLE READ 隔离级别下会使用到 MVCC 读取记录。在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行 SELECT 操作时都会生成一个 ReadView，ReadView 的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象；

Tips: 普通的SELECT 语句是指在非串行化事务隔离级别下，不加锁的select语句。

而在REPEATABLE READ 隔离级别下，一个事务在执行过程中只有第一次执行 SELECT 操作才会生成一个 ReadView，之后的 SELECT 操作都复用这个 ReadView， 这样也就避免了不可重复读和很大程度上避免了幻读的问题。

所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ 隔离级别下都算是一致性读。

2.2 方案二：读写均采用加锁

银行的很多业务查询，特别是跟钱直接相关的ATM查询，基本都是加锁查询的，每一步都是。

脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录， 如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

3 锁定读

MVCC是对应快照读/一致性读，而锁定读是对应加锁情况，指对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题，也称当前读。

哪些情况属于锁定读/当前读？

• select lock in share mode (共享锁)
• select for update (排他锁)
• update (排他锁)
• insert (排他锁)
• delete (排他锁)
• 串行化事务隔离级别

这些都是当前读。

当前读这种实现方式，也可以称之为 LBCC(基于锁的并发控制，Lock-Based Concurrency Control)。

3.1 Shared and Exclusive Locks（共享锁与排他锁）

在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，MySQL 中的锁有好几类：

• 共享锁，Shared Locks，简称 S 锁。在事务要读取一条记录时，需要先获取该记录的 S 锁。
• 独占锁，也常称排他锁，Exclusive Locks，简称 X 锁。在事务要改动一条记录时，需要先获取该记录的 X 锁。

比如MySQL中的两类加锁语句：

• 锁定读下的Select语句：

  • SELECT ... LOCK IN SHARE MODE，这是S锁；

    如果当前事务执行了该语句，那么它会为读取到的记录加S 锁，这样允许别的事务继续获取这些记录的S 锁（比方说别的事务也使用SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），但是不能获取这些记录的X 锁（比方说使用SELECT ... FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。
    如果别的事务想要获取这些记录的X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。

  • SELECT ... FOR UPDATE：这是X锁。

    比如语句SELECT ... FOR UPDATE，如果当前事务执行了该语句，那么它会为读取到的记录加X 锁，这样既不允许别的事务获取这些记录的S锁（比方说别的事务使用SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的X 锁（比如说使用SELECT ... FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。
    如果别的事务想要获取这些记录的S 锁或者X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X 锁释放掉。

• 写操作语句：
  • Delete语句：对一条记录做 DELETE 操作的过程其实是先在 B+树中定位到这条记录的位置， 然后获取一下这条记录的 X 锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在 B+树中位置的过程看成是一个获取 X 锁的锁定读。
  • Insert语句： 一般情况下，新插入一条记录的操作并不加锁，InnoDB 通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。

    一个事务对新插入的记录可以不显式的加锁，但是别的事务在对这条记录加S 锁或者X 锁时，会去检查索引记录中的trx_id 隐藏列，然后进行各种判断，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。但是由于事务id的存在，相当于加了一个隐式锁。
    这样的话，隐式锁就起到了延迟生成锁的用处。这个过程，我们无法干预，是由引擎自动处理的，对我们是完全透明的，我们知道下就行了。

  • Update语句：分为三种情况。
    • 未修改该记录的键值且被更新的列未发生存储空间的变化，先定位 B+树位置，再获取下该记录的 X 锁，最后在原位置修改；
    • 未修改该记录的键值且至少有一列的存储空间发生变化，则先定位 B+树的位置，再获取该记录的 X 锁，将该记录删除，再插入一条新的记录（insert 的隐式锁）；
    • 修改了该记录的键值，相当于先 delete，再 insert，加锁则按照 delete 和 insert 的规则进行。

4 锁的粒度

我们前边提到的锁都是针对记录的，也可以被称之为行级锁或者行锁，对一 条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细；

其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。给表加的锁也可以分为共享锁(S 锁)和排他锁(X 锁)。

4.1 表锁与行锁的比较

• 锁定粒度：表锁 > 行锁
• 加锁效率：表锁 > 行锁
• 冲突概率：表锁 > 行锁
• 并发性能：表锁 < 行锁

给表加S锁：

• 别的事务可以继续获得该表的S锁；
• 别的事务可以继续获得该表中的某些记录的S锁；
• 别的事务不可以继续获得该表的X锁；
• 别的事务不可以继续获得该表中的某些记录的X锁。

给表加X锁：

• 别的事务不可以继续获得该表的S锁；
• 别的事务不可以继续获得该表中的某些记录的S锁；
• 别的事务不可以继续获得该表的X锁；
• 别的事务不可以继续获得该表中的某些记录的X锁。

4.2 表锁之意向锁（Intention Locks）

• 意向共享锁，Intention Shared Lock，简称 IS 锁。当事务准备在某条记录上加 S 锁时，需要先在表级别加一个 IS 锁。
• 意向独占锁，Intention Exclusive Lock，简称 IX 锁。当事务准备在某条记录上加 X 锁时，需要先在表级别加一个 IX 锁。

IS锁、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的 S 锁 和 X 锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

就是说其实 IS 锁和 IX 锁是兼容的，IX 锁和 IX 锁是兼容的。 我们画个表来看一下表级别的各种锁的兼容性：

兼容性	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容		不兼容
S	不兼容	不兼容
IS	不兼容

锁的组合性：

兼容性	X	IX	S	IS
表锁	有	有	有	有
行锁	有		有

5 MySQL 中的行锁和表锁

对于 MyISAM、MEMORY、MERGE 这些存储引擎来说，它们只支持表级锁， 而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。

因为使用 MyISAM、MEMORY、MERGE 这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎实际上最好用在只读，或者大部分都是读操作，或者单用户的情景下。 另外，在 MyISAM 存储引擎中有一个称之为 Concurrent Inserts 的特性，支持在对 MyISAM 表读取时同时插入记录，这样可 以提升一些插入速度。关于更多 Concurrent Inserts 的细节，详情可以参考文档。

接下来，重点讲讲在InnoDB存储引擎下的一些锁。

5.1 InnoDB 中的表级锁

5.1.1 表级别的 S 锁、X 锁

在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB 存储引擎是不会为这个表添加表级别的 S 锁或者 X 锁的。

另外，在对某个表执行一些诸如 ALTER TABLE、DROP TABLE 这类的 DDL 语句 时，其他事务对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE 的语句会发生阻塞；

同理，某个事务中对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server 层使用一种称之为元数据锁(Metadata Locks，简称 MDL) 来实现的，一般情况下也不会使用 InnoDB 存储引擎自己提供的表级别的 S 锁和 X锁。

其实这个 InnoDB 存储引擎提供的表级 S 锁或者 X 锁是相当鸡肋，只会在一些特殊情况下，比方说崩溃恢复过程中用到。不过我们还是可以手动获取一下的， 比方说在系统变量 autocommit=0，innodb_table_locks = 1 时，手动获取 InnoDB 存储引擎提供的表 t 的 S 锁或者 X 锁可以这么写:

• LOCK TABLES t READ：InnoDB 存储引擎会对表 t 加表级别的 S 锁。
• LOCK TABLES t WRITE：InnoDB 存储引擎会对表 t 加表级别的 X 锁。

不过请尽量避免在使用 InnoDB 存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。

5.1.2 表级别的 IS 锁、IX 锁

IS 锁和 IX 锁的使命：只是为了后续在加表级别的 S 锁和 X 锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。
我们并不能手动添加意向锁，只能由 InnoDB 存储引擎自行添加。

当我们在对使用 InnoDB 存储引擎的表的某些记录加 S 锁之前，那就需要先在表级别加一个 IS 锁，当我们在对使用 InnoDB 存储引擎的表的某些记录加 X 锁之前，那就需要先在表级别上加一个 IX 锁。

5.1.3 表级别的 AUTO-INC 锁

在使用 MySQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值。

系统实现这种自动给 AUTO_INCREMENT 修饰的列递增赋值的原理主要是两个：

• 采用 AUTO-INC 锁，也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。

  如果我们的插入语句在执行前不可以确定具体要插入多少条记录(无法预计即将插入记录的数量)，比方说使用 INSERT ... SELECT、REPLACE ... SELECT 或者LOAD DATA这种插入语句，一般是使用 AUTO-INC 锁为 AUTO_INCREMENT 修饰的列生成对应的值。

• 采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时，获取一下这个轻量级锁，然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。

  如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比方说我们上边举的关于表 t 的例子中，在语句执行前就可以确定要插入 2 条记录，那么 一般采用轻量级锁的方式对 AUTO_INCREMENT 修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。

InnoDB 提供了一个称之为 innodb_autoinc_lock_mode 的系统变量来控制到底使用上述两种方式中的哪种来为 AUTO_INCREMENT 修饰的列进行赋值：

• 当 innodb_autoinc_lock_mode = 0 时，一律采用 AUTO-INC 锁；
• 当 innodb_autoinc_lock_mode = 2 时，一律采用轻量级锁。这种方式可能会造成不同事务中的插入语句为 AUTO_INCREMENT 修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。
• 当 innodb_autoinc_lock_mode = 1 时，两种方式混着来(也就是在插入记录数量确定时采用轻量级锁，不确定时使用 AUTO-INC 锁)。

MySQL5.7.X 中innodb_autoinc_lock_mode默认值为 1。

5.2 InnoDB 中的行级锁

行锁，顾名思义就是在记录上加的锁。但是要注意，这个记录指的是索引上的索引项。

• 只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁；
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁；
• 只有执行计划真正使用了索引，才能使用行锁；
• 当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

5.2.1 Record Locks，记录锁

对索引记录项进行加锁。官方的类型名称为: LOCK_REC_NOT_GAP。

以上一章【MySQL学习】8.事务原理与MVCC 中的表teacher为例，现插入一条对字段name的索引：

INDEX `idx_name`(`name`);

目前拥有以下数据：

number	name	domain
1	Jack	源码系列
3	Mark	并发编程
9	James	Redis
15	King	JVM
21	Dafei	MySQL

现对name=9的记录加一个记录锁：

SELECT name FROM teacher t WHERE name = 'James' FOR UPDATE;

示意图如下：

记录锁情况,

则其他事务对teacher表中的name='James的项进行select、insert、delete都将会被阻塞。

记录锁是有 S 锁和 X 锁之分：
当一个事务获取了一条记录的 S 型记录锁后， 其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；
当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2.2 Gap Locks，间隙锁

我们说 MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB 提出了一种称之为 Gap Locks 的锁（LOCK_GAP），我们也可以简称为 gap 锁。

默认开启，可通过以下两种方式关闭：

• 将事务隔离级别调整为READ COMMITTED；
• 或者将innodb_locks_unsafe_for_binlog置为1。

  5.2.2.1 间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁（开区间）。

比如：对表teacher插入一条如下的记录(事务id：108210）：

update teacher set domain ='Spring' where name='James';

其会对([‘Jack’,1], [‘James’,9])之间, ([‘James’,9], [‘King’,15])之间进行上锁。
ps: 本机MySQL版本：v5.7.17

间隙锁情况,

现另一事务开启以下插入操作(事务id：108212）：

begin;
insert into teacher value(22,'Jahes','docker');

结果爆出以下错误：

1205 - Lock wait timeout exceeded; try restarting transaction, Time: 51.093000s

通过以下命令可以查看两条事务对表加了什么锁：

mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 108212:1309:4:4
lock_trx_id: 108212
lock_mode: X,GAP
lock_type: RECORD
lock_table: `student`.`teacher`
lock_index: idx_name
lock_space: 1309
lock_page: 4
lock_rec: 4
lock_data: 'James', 9
*************************** 2. row ***************************
lock_id: 108210:1309:4:4
lock_trx_id: 108210
lock_mode: X
lock_type: RECORD
lock_table: `student`.`teacher`
lock_index: idx_name
lock_space: 1309
lock_page: 4
lock_rec: 4
lock_data: 'James', 9
2 rows in set, 1 warning (0.00 sec)

可以看出：

• 事务id=108210是更新操作，通过字段lock_type: RECORD可以得出其对记录name=James所加的锁类型为行锁(注意，这里不是记录锁的意思)；而通过字段lock_mode: X得出这个行锁还是next-key lock。
• 事务id=108212是插入操作，lock_type: RECORD得出是对记录name=James加的是行锁，lock_mode: X,GAP得出该行锁为间隙锁。

Tips:

• 如果LOCK_MODE为X，说明是 next-key 锁；
• 如果LOCK_MODE为X, REC_NOT_GAP，说明是记录锁；
• 如果LOCK_MODE为X, GAP，说明是间隙锁。

5.2.2.2 两个事务的间隙锁之间是相互兼容的，不会产生冲突

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻读记录而提出的。

5.2.3 Next-Key Locks

官方名LOCK_ORDINARY，一个记录锁和一个gap 锁的合体(左开右闭区间)。

默认情况下，InnoDB 以 REPEATABLE READ 隔离级别运行。在这种情况下， InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

5.2.4 Insert Intention Locks，插入意向锁

插入意向锁是一种锁的的等待队列，让等锁的事务在内存中进行排队等待，当持有锁的事务完成后，处于等待状态的事务就可以获得锁继续事务了。

并不是意向锁，它属于行级锁，是一种特殊的间隙锁。

举例：
事务A执行：

BEGIN;
SELECT name from teacher where name BETWEEN 'Jack' and 'James' for UPDATE;

事务B执行：

BEGIN;
insert into teacher value (24, 'Jae2', 'Go');

结果：

1205 - Lock wait timeout exceeded; try restarting transaction, Time: 51.134000s

通过以下命令输出innodb监控可以查看到插入意向锁的信息：

show engine innodb status;

结果：

insert into teacher value (24, 'Jae2', 'Go')
------- TRX HAS BEEN WAITING 24 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1309 page no 4 n bits 80 index idx_name of table `student`.`teacher` trx id 108264 lock_mode X locks gap（间隙锁） before rec insert intention（插入意向） waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 4a616d6573; asc James;;
 1: len 4; hex 80000009; asc     ;;

与间隙锁的区别：

• 是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。
• 尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。

生成时机：每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，那 Insert 语句会被阻塞，并生成一个插入意向锁。

5.2.5 隐式锁

无法干预，是由引擎自动处理的。

5.2.6 兼容矩阵

锁模式兼容矩阵（横向是已持有锁，纵向是正在请求的锁）：

兼容性	Gap	Insert Intention	Record	Next-Key
Gap	兼容	冲突	兼容	兼容
Insert Intention	兼容	兼容	兼容	兼容
Record	兼容	兼容	冲突	冲突
Next-Key	兼容	兼容	冲突	冲突