【MySQL高阶】27.事务(2)-锁

5. 隔离性实现原理

5.1 事务的隔离性

MySQL服务可以同时被多个客户端访问，每个客户端执行的DML语句以事务为基本单位，那么不同的客户端在对同一张表中的同一条数据进行修改的时候就可能出现相互影响的情况，为了保证不同的事务之间在执行的过程中不受影响，那么事务之间就需要要相互隔离，这种特性就是隔离性。

5.2 事务的隔离级别

事务具有隔离性，那么如何实现事务之间的隔离？隔离到什么程度？如何保证数据安全的同时也要兼顾性能？这都是要思考的问题。

如果学习过多线程技术，都知道在并发执行的过程中，多个线程对同一个共享变量进行修改时，在不加限制的情况下会出现线程安全问题，我们解决线程安全问题时，一般的做法是通过对修改操作进行加锁；同理，多个事务在对同一个表中的同一条数据进行修改时，如果要实现事务间的隔离也可以通过锁来完成。

在MySQL中常见的锁包括：读锁，写锁，行锁，间隙锁，Next-Key锁等。

不同的锁策略联合多版本并发控制可以实现事务间不同程度的隔离，称为事务的隔离级别。

不同的隔离级别在性能和安全方面做了取舍，有的隔离级别注重并发性，有的注重安全性，有的则是并发和安全适中。

在MySQL的InnoDB引擎中事务的隔离级别有四种，分别是：

1. READ UNCOMMITTED，读未提交【性能高】【最不安全】
2. READ COMMITTED，读已提交【性能第二高】【第三安全】
3. REPEATABLE READ，可重复读(默认)【性能第三高】【第二安全】
4. SERIALIZABLE，串行化【性能第四高】【最安全】

5.3 锁

实现事务隔离级别的过程中用到了锁，所谓锁就是在事务A修改某些数据时，对这些数据加一把锁，防止其他事务同时对这些数据执行修改操作。

当事务A完成修改操作后，释放当前持有的锁，以便其他事务再次上锁执行对应的操作。

不同存储引擎中的锁功能并不相同，这里我们重点介绍InnoDB存储引擎中的锁。

5.3.1 锁信息

锁的信息包括锁的请求(申请)，锁的持有以及阻塞状态等等，都保存在performance_schema库的data_locks表中，可以通过以下方式查看：

mysql> SELECT * FROM performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB # 持有或请求锁的存储引擎 ENGINE_LOCK_ID: 139664434886512:1059:139664350547912 # 存储引擎持有或请求的锁的ID ENGINE_TRANSACTION_ID: 2569 # 请求锁的事务对应的存储引擎内部ID THREAD_ID: 46 # 创建锁的会话的线程ID EVENT_ID: 12 # 请求锁的事件ID OBJECT_SCHEMA: test_db # 锁定表所在的数据库 OBJECT_NAME: account # 被锁定表的名称 PARTITION_NAME: NULL # 锁定分区的名称; 没有使用表分区时为NULL SUBPARTITION_NAME: NULL # 锁定子分区的名称; 没有使用子分区时为NULL INDEX_NAME: NULL # 锁定索引的名称; 没有使用索引时为NULL OBJECT_INSTANCE_BEGIN: 139664350547912 # 锁在内存中的地址 LOCK_TYPE: TABLE # 锁的类型 LOCK_MODE: IX # 锁的模式，表示如何请求锁 LOCK_STATUS: GRANTED # 锁请求的状态; GRANTED(持有)，WAITING(等待) LOCK_DATA: NULL # 与锁相关的数据(如果有) *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139664434886512:2:4:1:139664350544872 ENGINE_TRANSACTION_ID: 2569 THREAD_ID: 46 EVENT_ID: 12 OBJECT_SCHEMA: test_db OBJECT_NAME: account PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: GEN_CLUST_INDEX OBJECT_INSTANCE_BEGIN: 139664350544872 LOCK_TYPE: RECORD # 锁的类型 LOCK_MODE: X # 锁的模式，表示如何请求锁 LOCK_STATUS: GRANTED # 锁请求的状态; LOCK_DATA: supremum pseudo-record

• 锁类型

  锁类型依赖于存储引擎，在InnoDB存储引擎中按照锁的粒度分为，行级锁RECORD和表级锁TABLE：

  • 行级锁也叫行锁，是对表中的某些具体的数据行进行加锁；
  • 表级锁也叫表锁，是对整个数据表进行加锁。

  在之前版本的BDB存储引擎中还支持页级锁，锁定的是一个数据页，MySQL8中没有页级锁

• 锁模式

  锁模式，用来描述如何请求(申请)锁，分为共享锁(S)、独占锁(X)、意向共享锁(IS)、意向独占锁(IX)、记录锁、间隙锁、Next-Key锁、AUTO-INC锁、空间索引的谓词锁等

5.3.2 共享锁和独占锁 - Shared and Exclusive Locks

InnoDB实现了标准的行级锁，分为两种分别是共享锁(S锁)和独占锁(X锁)，独占锁也称为排他锁。

• 共享锁(S锁)：允许持有该锁的事务读取表中的一行记录，同时允许其他事务在锁定行上加另一个共享锁并读取被锁定的对象，但不能对其进行写操作；

  类似于贴了张成绩单，所有人都可以看见。

• 独占锁(X锁)：允许持有该锁的事务对数据行进行更新或删除，同时不论其他事务对锁定行进行读取或修改都不允许对锁定行进行加锁；

  类似于要修改成绩单，把原来的成绩单拿下去修改，修改的时候别人看不了。

# 对查询结果集中的每行数据都加共享锁 select * from account where id < 2 FOR SHARE; # MYSQL8.0(推荐) select * from account where id < 2 LOCK IN SHARE MODE; # 8.0以及之前版本 # 对查询结果集中的每行数据都加排他锁 select * from account where id = 1 FOR UPDATE; # 可以使用以下SQL在监视器中查看锁信息 SHOW ENGINE INNODB STATUS\G

• 如果事务T1持有R行上的共享锁(S)，那么事务T2请求R行上的锁时会有如下处理:
  • T2请求S锁会立即被授予，此时T1和T2都对R行持有S锁；
  • T2请求X锁不能立即被授予，阻塞到T1释放持有的锁
• 如果事务T1持有R行上的独占锁(X)，那么T2请求R行上的任意类型锁都不能立即被授予，事务T2必须等待事务T1释放R行上的锁。

读锁是共享锁的一种实现，写锁是排他锁的一种实现。

5.3.3 意向锁 - Intention Locks

• InnoDB支持多粒度锁，允许行锁和表锁共存；
• InnoDB使用意向锁实现多粒度级别的锁，意向锁是表级别的锁，它并不是真正意义上的加锁，而只是在data_locks中记录事务以后要对表中的哪一行加哪种类型的锁(共享锁或排他锁)，意向锁分为两种:
  • 意向共享锁(IS)：表示事务打算对表中的单个行设置共享锁。
  • 意向排他锁(IX)：表示事务打算对表中的单个行设置排他锁。
• 在获取意向锁时有如下协议：
  • 在事务获得表中某一行的共享锁(S)之前，它必须首先获得该表上的IS锁或更强的锁。
  • 在事务获得表中某一行的排他锁(X)之前，它必须首先获得该表上的IX锁。
• 意向锁可以提高加锁的性能，在真正加锁之前不需要遍历表中的行是否加锁，只需要查看一下表中的意向锁即可；
• 在请求锁的过程中，如果将要请求的锁与现有锁兼容，则将锁授予请求的事务，如果与现有锁冲突，则不会授予；事务将阻塞等待，直到冲突的锁被释放；意向锁与行级锁的兼容性如下表：

• 除了全表锁定请求之外，意向锁不会阻止任何锁请求；意向锁的主要目的是表示事务正在锁定某行或者正在意图锁定某行。

5.3.4 索引记录锁 - Record Locks

• 索引记录锁或称为精准行锁，顾名思意是指索引记录上的锁，如下SQL锁住的是指定的一行：

# 防止任何其他事务插入、更新或删除值为1的行，id为索引列 SELECT * FROM account WHERE id = 1 For UPDATE;

• 索引记录锁总是锁定索引行，在表没有定义索引的情况下，InnoDB创建一个隐藏的聚集索引，并使用该索引进行记录锁定，当使用索引进行查找时，锁定的只是满足条件的行，如图所示：

5.3.5 间隙锁 - Gap Locks

• 间隙锁锁定的是索引记录之间的间隙，或者第一个索引记录之前，再或者最后一个索引记录之后的间隙。如图所示位置，根据不同的查询条件都可能会加间隙锁：

• 例如有如下SQL，锁定的是ID (10, 20)之间的间隙，注意不包括10和20的行，目的是防止其他事务将ID值为15的列插入到列account表中(无论是否已经存在要插入的数据列)，因为指定范围值之间的间隙被锁定了；

SELECT * FROM account WHERE id BETWEEN 10 and 20 For UPDATE;

• 间隙可以跨越单个或多个索引值；

• 对于使用唯一索引查询到的唯一行，不使用间隙锁，如下语句，id列有唯一的索引，只对id值为100的行使用索引记录锁：

# 只使用Record Locks SELECT * FROM account WHERE id = 100;

• 如果id没有被索引，或者是一个非唯一的索引，以上语句将锁定对应记录前面的间隙；
• 不同事务的间隙锁可以共存，一个事务的间隙锁不会阻止另一个事务在相同的间隙上使用间隙锁；共享间隙锁和独占间隙锁之间没有区别。
• 当事务隔离级别设置为READ COMMITTED时间隙锁会被禁用，对于搜索和索引扫描不再使用间隙锁定。

5.3.6 临键锁 - Next-Key Locks

• Next-key锁是索引记录锁和索引记录之前间隙上间隙锁的组合，如图所示；

• InnoDB搜索或扫描一个表的索引时，执行行级锁策略，具体方式是：在扫描过程中遇到的索引记录上设置共享锁或排他锁，因此，行级锁策略实际上应用的是索引记录锁。索引记录上的nextkey锁也会影响该索引记录之前的"间隙"，也就是说，next-key锁是索引记录锁加上索引记录前面的间隙锁。如果一个会话对索引中的一条记录R具有共享锁或排他锁，则另一个会话不能在索引记录R之前的空白中插入新的索引记录行。
• 假设索引包含值10、11、13和20，这些索引可能的next-key锁覆盖以下区间，其中圆括号表示不包含区间端点，方括号表示包含端点:

(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)

• 默认情况下，REPEATABLE READ事务隔离级别开启next-key锁并进行搜索和索引扫描，可以防止幻象行，从而解决幻读问题，后面我们再分析。

5.3.7 插入意向锁 - Insert Intention Locks

• 插入意向锁是一个特殊的间隙锁，在向索引记录之前的间隙进行insert操作插入数据时使用，如果多个事务向相同索引间隙中不同位置插入记录，则不需要彼此等待。

  假设已经存在值为10和20的索引记录，两个事务分别尝试插入索引值为15和16的行，在获得插入行上的排他锁之前，每个事务都用插入意向锁锁住10到20之间的间隙，但不会相互阻塞，因为他们所操作的行并不冲突；

• 下面的示例演示一个事务在获得插入记录的排他锁之前，使用了插入意向锁：

会话A开启事务A	会话B开启事务B

mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s) MySQL thread id 10, OS thread handle 17568, query id 83 localhost ::1 root update INSERT INTO child (id) VALUES (101) ------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 25 page no 4 n bits 72 index PRIMARY of table `test_db`.`child` trx id 6441 lock_mode X locks gap before rec insert intention waiting #插入意向锁等待中 Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 6; hex 000000001923; asc #;; 2: len 7; hex 8200000154011d; asc T ;;

5.3.8 AUTO-INC Locks

AUTO-INC锁也叫自增锁是一个表级锁，服务于配置了AUTO_INCREMENT自增列的表。

在插入数据时会在表上加自增锁，并生成自增值，同时阻塞其他的事务操作，以保证值的唯一性。

需要注意的是，当一个事务执行新增操作已生成自增值，但是事务回滚了，申请到的主键值不会回退，这意味着在表中会出现自增值不连续的情况。其他的相关内容这里我们不做深入讨论。