mysql（八）事务隔离级别及加锁流程详解

MySQL 锁简介

什么是锁

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制

锁的作用

• 在数据库中，除传统计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。
• 如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。
• 锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。

锁的种类

共享排他锁

共享锁

共享锁，又称读锁，简称 S 锁。当事务对数据加上读锁后，其他事务只能对该数据加读锁，不能加写锁。

共享锁加锁方法： select …lock in share mode

排它锁

排他锁，又称为写锁，简称 X 锁，当事务对数据加上排他锁后，其他事务无法对该数据进行查询或者修改

MySQL InnoDB引擎默认 update，delete，insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型。

排他锁加锁方式：select …for update

粒度锁

全局锁

全局锁，从名称上可以理解，全局锁就是对整个 MySQL 数据库实例加锁，加锁期间，对数据库的任何增删改操作都无法执行。

MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)

全库数据备份时，可以使用全局锁，其他情况不要使用

表级锁

表级锁，给当前操作的这张表加锁， MyISAM 与 InnoDB 引擎都支持表级锁定

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

加表锁：lock table read/write

页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁

行级锁

行级锁是 MySQL 粒度最细的锁，发生锁冲突概率最低，但是加锁慢，开销大

MySQL 中只有 InnoDB 引擎支持行锁，其他不支持

MySQL 中，行级锁并不是之间锁记录，而是锁的索引 。

普通的 select 语句是不会对记录加锁的，因为它属于快照读，是通过MVCC（多版本并发控制） 实现的。

MySQL 在执行 update、delete 语句时会自动加上行锁

种类

不同隔离级别下，行级锁的种类是不同的。

在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。

在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（目的是为了避免幻读），所以行级锁的种类主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；

• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；

• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

Record Lock
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;

• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

举个例子，当一个事务执行了下面这条语句：

mysql > begin; 
mysql > select * from t_test where id = 1 for update;

事务会对表中主键 id = 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改和删除了。

​当事务执行 commit 后，事务过程中生成的锁都会被释放。

Gap Lock
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

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

Next-Key Lock
Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新记录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

意向锁

意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存

当有事务A有行锁时，MySQL会自动为该表添加意向锁，事务B如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能。

	意向共享锁(IS)	意向排他锁(IX)
共享锁	兼容	互斥
排他锁	互斥	互斥

间隙临键记录锁

记录锁、间隙锁、临键锁都是排它锁，而记录锁的使用方法跟排它锁介绍一致

记录锁

记录锁是封锁记录，记录锁也叫行锁，例如：

select * from people where id = 1 for update;

间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据

select * from people where id < 10 for update;

即所有在 [1，10）区间内的记录行都会被锁住，所有id 为 1、2、3、4、5、6、7、8、9 的数据行的插入会被阻塞

加锁的流程

由于InnoDB引擎才支持行级锁，以下内容都是基于InnoDB引擎介绍。

锁的内存结构

对一条记录加锁本质上是内存中创建的一个锁结构跟这条记录相关联。
所以锁本质上就是内存中的一种数据结构。

那么我们在操作一个事务的时候，如果对应多条记录，是不是要针对多条记录生成多个内存的锁结构呢？比如我们执行select * from people for update的时候，people 表中如果存在1万条数，那么难道要生成1万个内存的锁结构吗？那当然不会是这样的。其实，如果符合以下几个条件，那么这些记录的锁就可以放到一个内存中的锁结构里了，条件如下所示：

• 加锁操作时在同一个事务中
• 需要被加锁的记录在同一个页中
• 需要加锁的类型是一致的
• 锁的等待状态是一致的

那么这么多次的锁结构，它到底是怎么组成的呢？
主要是由6部分组成的。分别为：锁所在的事务信息、索引信息、表锁或行锁信息、type_mode、其他信息、与heap_no对应的比特位。

• 锁所在的事务信息
  一个锁结构对应一个事务，那么这里就存储着锁对应的事务信息。它其实只是一个指针，可以通过它获取到内存中关于该事务的更多信息，比如：事务id是多少。
• 索引信息
  对于行级锁来说，这里记录的就是加锁的记录属于哪个索引。
• 表锁/行锁信息
  (1)、对于表锁，主要是来记录对哪张表进行的加锁操作以及其他的信息。
  (2)、对于行锁，内容包括3部分：
  Space ID：记录所在的表空间ID。
  Page Number：记录所在的页号。
  n_bits：一条记录对应一个bit
• type_mode
  它是由32个bit组成的，分别为：lock_mode、lock_type、lock_wait和rec_lock_type，如下图所示：
  

加锁的基本流程

• 一开始是没有锁结构与记录进行关联的，即：上图第一个图例所示。
• 当一个事务T1想对这条记录进行改动时，会看看内存中有没有与这条记录关联的锁结构，如果没有，就会在内存中生成一个锁结构与这条记录相关联，即：上图第二个图例所示。我们把该场景称之为获取锁成功或者加锁成功。
• 此时又来了另一个事务T2要访问这条记录，发现这条记录已经有一个锁结构与之关联了，那么T2也会生成一个锁结构与这条记录关联，不过锁结构中的is_waiting属性值为true，表示需要等待。即：上图第三个图例所示。我们把该场景称之为获取锁失败/加锁失败。
• 事务T1提交之后，就会把它生成的锁结构释放掉，然后检测一下还有没有与该记录关联的锁结构。结果发现了事务T2还在等待获取锁，所以把事务T2对应的锁结构的is_waiting属性设置为false，然后把该事务对应的线程唤醒，让T2继续执行。

根据主键加锁

对应sql语句，其中id字段是自增主键：

update people set age = 10 where id = 49;

说明：

1. 基于主键（聚簇索引）进行等值查询时，如果对应的值存在，则只需添加标准记录锁Record Lock。如果对应的值不存在，则需要在查询id所在的索引间隙添加间隙锁Gap Lock。
2. 基于主键（聚簇索引）进行范围查询时，采用采用Next Key Lock添加行锁。

根据二级索引加锁

对应sql语句，其中name字段上有普通索引：

update people set age = 10 where name = 'Tom';

说明：

1. 基于辅助索引进行查询时，会先在辅助索引上加锁，然后在聚簇索引上加锁。
2. 基于辅助索引进行查询时，聚簇索引上加锁算法采用Record Lock，即只锁记录不锁间隙。

根据非索引字段查询加锁

对应sql语句，其中age字段上没有索引：

update people set name = 'Tom' where age = 10;

说明：

1. 查询不走索引时，会在聚簇索引上加锁，加锁算法采用Next Key Lock，并且会锁定全表范围。

注意是通过Next Key Lock锁定的全表范围，而不是通过表级锁直接锁表。

加锁规律

• InnoDB中默认采用Next Key Lock加锁，Next Key Lock加锁范围前开后闭。
• 行锁都是加在索引上，如果通过聚集索引查询则在聚集索引上加锁，通过辅助索引查询则需要同时在辅助索引和聚集索引上加锁，不走索引则在聚集索引上加锁。
• 查找过程中访问到的索引才会加锁。注意是访问到的索引而不是满足查询条件的索引。
• 基于主键和唯一索引进行等值查询，Next Key Lock会退化为行锁Record Lock。
• 索引上的等值查询，没有满足条件的记录时，Next-key lock退化为间隙锁，加锁范围是查询值所在的间隙。
• 通过辅助索引查询并加锁时，需要进行回表查询然后在聚集索引上采用行锁Record Lock加锁。
• 范围查询采用Next Key Lock加锁。

锁信息查看

查看锁的sql语句

-- 查看当前所有事务
select * from information_schema.innodb_trx;
-- 查看加锁信息（MySQL5.X）
select * from information_schema.innodb_locks;
-- 查看锁等待（MySQL5.X）
select * from information_schema.innodb_lock_waits;
--查看加锁信息（MySQL8.0）
SELECT * FROM performance_schema.data_locks;
--查看锁等待（MySQL8.0）
SELECT * FROM performance_schema.data_lock_waits;
-- 查看表锁
show open tables where In_use>0;
-- 查看最近一次死锁信息
show engine innodb status;

这里主要介绍通过查询performance_schema.data_locks表，查看事务中加锁的情况。

DROP TABLE if EXISTS people;

CREATE TABLE `people` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `account` varchar(30) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '账号',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_account` (`account`) USING BTREE,
  KEY `ik_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `people` (`id`,`account`,`name`, `age`, `email`) VALUES (3, '000003', '老万', 12, '101@qq.com');
INSERT INTO `people` (`id`,`account`, `name`, `age`, `email`) VALUES (10, '000010', '老张', 15, '101@qq.com');
INSERT INTO `people` (`id`,`account`, `name`, `age`, `email`) VALUES (20, '000020', '老王', 15, '101@qq.com');
INSERT INTO `people` (`id`,`account`, `name`, `age`, `email`) VALUES (30, '000030', '老王', 30, '101@qq.com');

开启mysql命令行窗口，开启事务执行加锁

查看加锁情况：

select * from performance_schema.data_locks\G

• ENGINE 表使用的存储引擎，这里是InnoDB
• ENGINE_TRANSACTION_ID 事务ID
• OBJECT_SCHEMA 加锁的表空间,这里的表空间是test
• OBJECT_NAME 加锁的表名，这里是user
• INDEX_NAME加锁的索引名称，表级锁为null，行级锁为加锁的索引名称。这里PRIMARY表示是主键索引上添加锁。
• LOCK_TYPE 锁类型：TABLE对应表级锁，RECORD对应行级锁。
• LOCK_MODE 加锁模式，对应具体锁的类型，比如：IX 意向排他锁，X，GAP 排他间隙锁。
• LOCK_STATUS 锁的状态，GRANTED 已获取，WAITING 等待中
• LOCK_DATA 加锁的数据，这里的10表示，在主键索引值为10的记录上加锁。由于加的是间隙锁GAP，这里锁定的是3~10这个间隙。如果值为supremum pseudo-record，表示高于索引中的任何值，锁定正无穷的范围。

lock_mode说明
这里需要重点对 LOCK_MODE 加锁模式进行说明：

LOCK_MODE值	锁类型
IX	意向排他锁
IS	意向共享锁
AUTO_INC	自增主键锁
X	排他临键锁(Next Key) ,既锁记录，也锁间隙
S	共享临键锁(Next Key) ,既锁记录，也锁间隙
X,REC_NOT_GAP	排他标准记录锁(Record）,只锁记录，不锁间隙
S,REC_NOT_GAP	共享标准记录锁(Record) ,只锁记录，不锁间隙
S,GAP	共享间隙锁(GAP),只锁间隙
X,GAP	排他间隙锁(GAP) ,只锁间隙
INSERT_INTENTION	插入意向锁

数据库事务

可串行化（Serializable）（最高级别）

可串行化完全符合普通程序员对数据竞争加锁的理解，如果不考虑性能优化的话，对事务所有读、写的数据全都加上读锁、写锁和范围锁即可做到可串行化（“即可”是简化理解，实际还是很复杂的，要分成 Expanding 和 Shrinking 两阶段去处理读锁、写锁与数据间的关系，称为Two-Phase Lock，2PL）。但数据库不考虑性能肯定是不行的，并发控制理论（Concurrency Control）决定了隔离程度与并发能力是相互抵触的，隔离程度越高，并发访问时的吞吐量就越低。现代数据库一定会提供除可串行化以外的其他隔离级别供用户使用，让用户调节隔离级别的选项，根本目的是让用户可以调节数据库的加锁方式，取得隔离性与吞吐量之间的平衡。

可重复读（Repeatable Read）

可串行化的下一个隔离级别是可重复读（Repeatable Read），可重复读对事务所涉及的数据加读锁和写锁，且一直持有至事务结束，但不再加范围锁。可重复读比可串行化弱化的地方在于幻读问题（Phantom Reads），它是指在事务执行过程中，两个完全相同的范围查询得到了不同的结果集

SELECT count(1) FROM books WHERE price < 100					/* 时间顺序：1，事务： T1 */
INSERT INTO books(name,price) VALUES ('深入理解Java虚拟机',90)	/* 时间顺序：2，事务： T2 */
SELECT count(1) FROM books WHERE price < 100					/* 时间顺序：3，事务： T1 */

根据前面对范围锁、读锁和写锁的定义可知，假如这条 查询SQL 语句在同一个事务中重复执行了两次，且这两次执行之间恰好有另外一个事务在数据库插入了一本小于 100 元的书籍，这是会被允许的，那这两次相同的查询就会得到不一样的结果，原因是可重复读没有范围锁来禁止在该范围内插入新的数据，这是一个事务受到其他事务影响，隔离性被破坏的表现。

读已提交（Read Committed）

可重复读的下一个隔离级别是读已提交（Read Committed），读已提交对事务涉及的数据加的写锁会一直持续到事务结束，但加的读锁在查询操作完成后就马上会释放。读已提交比可重复读弱化的地方在于不可重复读问题（Non-Repeatable Reads），它是指在事务执行过程中，对同一行数据的两次查询得到了不同的结果

SELECT * FROM books WHERE id = 1;   						/* 时间顺序：1，事务： T1 */
UPDATE books SET price = 110 WHERE id = 1; COMMIT;			/* 时间顺序：2，事务： T2 */
SELECT * FROM books WHERE id = 1; COMMIT;   				/* 时间顺序：3，事务： T1 */

如果隔离级别是读已提交，这两次重复执行的查询结果就会不一样，原因是读已提交的隔离级别缺乏贯穿整个事务周期的读锁，无法禁止读取过的数据发生变化，此时事务 T2 中的更新语句可以马上提交成功，这也是一个事务受到其他事务影响，隔离性被破坏的表现。假如隔离级别是可重复读的话，由于数据已被事务 T1 施加了读锁且读取后不会马上释放，所以事务 T2 无法获取到写锁，更新就会被阻塞，直至事务 T1 被提交或回滚后才能提交。

读未提交（Read Uncommitted）

读已提交的下一个级别是读未提交（Read Uncommitted），读未提交对事务涉及的数据只加写锁，会一直持续到事务结束，但完全不加读锁。读未提交比读已提交弱化的地方在于脏读问题（Dirty Reads），它是指在事务执行过程中，一个事务读取到了另一个事务未提交的数据

参考

https://icyfenix.cn/architect-perspective/general-architecture/transaction/local.html
https://blog.csdn.net/qq_43842093/article/details/131737316
https://blog.csdn.net/weixin_50205273/article/details/127818382