Mysql的事务特性以及隔离级别（整理了部分锁、MVCC的内容）

因为部分是整理<参考资料>中的博文内容，侵删！

四大特性

Atomicity:原子性
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

Consistency:一致性
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

Isolation:隔离性
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

Durability:持久性
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

锁

全局锁

flush tables with read lock
全局读锁，只能select
unlock tables

做数据备份时可能会用到

共享锁与排它锁（读锁、写锁）

• 共享锁（读锁）：其他事务可以读，但不能写。
• 排他锁（写锁） ：其他事务不能读取，也不能写。

行锁

间隙锁(Gap Lock)

当我们在查询语句时，条件为范围查询时，InnoDB不管这个区间是否有数据，都会将其锁住，向这个区间的“间隙”（不存在的行）插入或删除数据都会阻塞。

间隙锁是在普通索引的范围之间，如果开启事务，进行独占后，在找到下一个索引值之前的范围都会被进行锁定，无法进行insert操作。

记录锁(Record Lock)

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

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

Next-Key Lock

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

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

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

表锁

意向锁

在使用 InnoDB 引擎的表里对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁，在使用 InnoDB 引擎的表里对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁

隔离级别

两个更新的事务同时修改一条数据，会造成一前一后把修改后的数据写入硬盘，造成前一条修改失效。这种情况是不被允许的

一个更新事务更新一条数据，另一个事务读取数据，这个读取的事务读取完，可能这个更新事务就更新了，因此读取到了不具备时效性的数据，这个是脏读

一个读取事务读取一条数据时，另一个更新事务修改了这条数据，这时就会出现不可重现的读取。跟上条类似，读取了更新后的数据，可能本来是想读更新前的数据的？

一个读取事务读取时，另一个插入事务（注意此处时插入）插入了一条新数据，这样就可能多读出一条数据，出现幻读。

读未提交

一个事务可以读取另一个事务未提交的数据。
一个数据被改了，但是还没提交，另一个事务来读取，读取的是改后的数据，但是前者抛出异常，回滚了事务，最后发现读取的数据并不是对的数据了。
可以读取未提交的数据（允许脏读），所以允许事务直接读取（不需要加锁） 最新数据即可。

读提交

一个事务开启读取数据，但是中间有另一个事务来修改，然后就会发现读取的数据是改后的数据，但是实际的数据是改了的。一个事务要等另一个事务提交后才能读取数据。
只允许读取已提交的数据，需要避免脏读，所以在每次查询时，获取通过最新的版本快照来判断哪些是已提交的数据从而避免脏读。基于MVCC实现

可重复读

同一事务多次读取同样记录，需要返回结果一致。所以在事务第一次读取数据时，获取当前的版本快照，通过快照的方式保证事务执行期间与事务启动时看到的数据一致，即使其他事务修改了数据也不会影响当前事务，从而实现可重复读。基于MVCC实现

可序列化

最高的隔离级别，强制事务排序，给每行加锁，串行执行，解决幻读问题。容易引发大量的锁竞争与超时现象。

设置数据库隔离级别

set global TRANSACTION ISOLATION level read COMMITTED;

select @@transaction_isolation;

隔离级别产生的问题

脏读

读取了另一个事务未提交的数据，就是不一定是正确的数据

不可重复读

对于某个数据，一个事务范围内的多次查询却返回了不同的数据，这是由于读提交引发的问题，当一个事务读取的时候，另一个事务来改提交，那么这个读事务就会等待，直到另一个事务提交后再读取。

幻读

和不可重复读一样都是读取了另一条已经提交的事务，不同的是不可重复读是针对一条数据，幻读是针对一批数据。

当前读与快照读

当前读（锁定读）

通过 select ... lock in share mode、select ... for update 语句可以对读取的记录进行加锁，其中 select ... lock in share mode 加的是 S 锁（共享锁），select ... for update加的是 X锁（独占锁）。

当前读 读取的是 记录的最新版本，为了防止幻读，在读取时要保证其他并发事务不能插入或删除当前记录，所以会对读取的记录进行加锁（next-key lock[临键锁]）操作，加锁后当其他事务插入或删除记录时就会被阻塞，从而避免幻读现象。

快照读

普通的select 不加锁 不会阻塞其他的事务
快照读基于MVCC（多版本并发控制） 提高并发性能  代价是可能读取到的数据是旧版本的数据，不一定是最新的
在可重复读的隔离级别下 ， 快照读通过快照数据的方式可以保证 事务执行中看到的数据同数据开始时的数据一致（因为此时会加锁阻塞其他的事务）

RC隔离级别（读提交）

关闭自动提交

方便进行后继的测试，自动提交会把单条的sql语句看成一个事务，sql执行完事务完成，自动提交或者回滚。

mysql> set @@autocommit=0;  //设置自动提交关闭
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

RC + 唯一索引

CREATE TABLE `user_info_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `status` varchar(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570072 DEFAULT CHARSET=utf8mb3;

UNIQUE KEY 唯一索引

先把隔离级别设置为RC，因为user_name为唯一索引，我们使用user_name为条件去执行select......for update语句，然后开启另外一个事务去更新数据同一条数据，发现被阻塞了。如下图：

select * from user_info_tab where user_name = '杰伦' for update;
for update 的作用和目的: select for update 是为了在查询时,对这条数据进行加锁,避免其他用户以该表进行插入,修改或删除等操作,造成表的不一致性. 

类似使用
select * from t for update 会等待行锁释放之后，返回查询结果。
select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录

综上 ， select for update 加的是行锁

比较合适使用wait 5 等待一定的时间，防止无限等待占用资源

查看使用的锁
SELECT * FROM performance_schema.data_locks\G;

​ X锁会先锁name索引的字段，然后为了其他数据能够快速识别到这行锁了，还需要锁它的主键，因为所有的数据查询时都会走到主键的索引。

RC 隔离级别 + 主键

select * from user_info_tab where id ='1570070' for update;然后开启另外一个事务去更新数据同一条数据，发现被阻塞了

更新其他行的话， 是能够正常执行的

条件是主键的话，锁的也是行，而不会去锁表

begin;
select * from user_info_tab where id ='1570070' for update;
SELECT * FROM performance_schema.data_locks\G;

查看加了什么锁

上面的用唯一索引的值作为条件，会先锁唯一索引，然后锁主键，这次直接锁主键

RC隔离级别 + 普通索引

alter table user_info_tab add index idx_city (city);
加上个普通索引

执行语句：select * from user_info_tab where city ='广州' for update;然后开启另外一个事务去更新同一条数据，发现被阻塞了。
如果事务二更新的是其他行的记录，还是可以顺利执行的。
一共加了三把锁，分别是：IX意向排他锁（表锁）、两把X排他锁（行锁，分别对应普通索引的X锁，对应主键的X锁）。

没有命中数据库表的记录，又加什么锁呢？
只加了一把锁，即IX意向排他锁（表锁，不影响插入）。

RC 隔离级别 + 无索引

事务一 先执行select......for update，然后事务二先更新别的行，发现可以顺利执行，如果执行for update的同一行，还是会阻塞等待。
一共加了两把锁，分别是：IX意向排他锁（表锁）、一把X排他锁（行锁，对应主键的X锁）。

若age列上没有索引，MySQL会走聚簇(主键)索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

RR 隔离级别（可重复度）

RR隔离级别比RC隔离级别，主要差异还是有间隙锁这个概念。
mysql> set global transaction isolation level repeatable read; 

RR + 唯一索引

RR数据库隔离级别，事务一先执行select......for update，然后事务一先更新别的行，发现可以顺利执行，如果执行更新for update的那一行，还是会阻塞超时。

不管是RC隔离级别还是RR隔离级别，select......for update，查询条件是唯一索引，命中数据库表记录时，一共会加三把锁：一把IX意向排他锁 （表锁，不影响插入），一把对应主键的X排他锁（行锁），一把对应唯一索引的X排他锁 （行锁）。

RR + 主键索引

跟RC隔离级别一样，会加两把锁：一把IX意向排他锁（表锁，不影响插入），一把对应主键的X排他锁（行锁，影响对应主键那一行的插入）。

RR + 普通索引

如果select......for update的查询条件是普通索引的话，除了会加X锁，IX锁，还会加Gap 锁。
Gap锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁。

在RR隔离级别下，如果select......for update的查询条件是普通索引的话，命中查询记录的话，除了会加两把X锁（行锁），IX锁（表锁，不影响插入），还会加Gap 锁（间隙锁，会影响插入）。

RR + 无索引

查询where 无索引的字段

然后查看加的锁

select OBJECT_NAME,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

一共五把锁，这个IX锁（表级别，意向排他锁），我们可以理解，跟前面几个例子的一样。后面三把行锁，就是把每一行的数据记录，都加了X排他锁（行锁，锁的对象对应于主键Id），我们也可以理解。 但是这个第二行，是一把怎么样的X锁呢？

什么是supremum pseudo-record
相当于比索引中所有值都大，但却不存在索引中，相当于最后一行之后的间隙锁。我理解就是如果查询条件有索引的话，类似于一个（索引最大值，+无穷）的虚拟间隙锁。
但是因为我们的查询字段age并没有索引，锁为X锁，lock_data值为supremum pseudo-record，它表示：全表行锁，要走聚簇索引进行全部扫描。

其实这样就是，直接锁表了。

MVCC

在实现 MySQL 的读提交隔离级别中，会使用 Read View ＋ 记录的版本链 以对比的方式来确保避免读取到未提交的数据即避免脏读现象。
在实现 MySQL 的可重复读隔离级别中，同样使用 Read View ＋ 记录的版本链 的方式来确保 事务执行过程中看到的数据与启动事务时看到的数据一致，从而避免不可重复读现象。
可重复读隔离级别中的幻读是靠快照读（基于 MVCC 实现的）的方式来避免幻读现象。

MVCC实现

主要依靠记录的隐藏字段、undo log 、Read View实现

记录的隐藏字段

聚簇索引的记录中除了包含记录的数据字段之外，还包含：

DB_ROW_ID：隐含的自增ID，如果数据表中没有找到符合条件的主键，会自动以DB_ROW_ID作为聚簇索引的索引键。
DB_TRX_ID：最后一次插入或修改（更新或删除）该记录的事务的事务ID。
DB_ROLL_PTR：回滚指针，指向该记录的undo log（上一个版本），如果是第一个版本，则该位置为空。

这里我们只需关注 DB_TRX_ID 和 DB_ROLL_PTR 两个隐藏字段即可，其中的 DB_ROLL_PTR 回滚指针利用 undo log。

undo log

当一个事务更改时，undo log会记录当前字段的内容，然后放到undo log中，尝试修改
事务回滚，则当前事务回到7
事务提交，则生成当前事务8，回滚指针指向事务7

redo log

数据库的操作是在内存中完成的，刷盘操作并不是实时的，完成操作会产生多条redo log，然后事务提交，会根据redo log来刷盘，把多条更改数据的内容从不同时间段变成一个时间段，从而变成了顺序读写的I/O，而不是随机I/O。

Read View

Read View 用于记录和维护系统某一时刻的当前活跃事务的ID，可以看成是一张快照，主要用来做可见性判断，通过结合记录的版本链，将 Read View 的字段作为条件判断当前事务对记录版本链上的哪个版本的记录可见。通过 Read View 与 记录的版本链 对比，来控制并发事务访问同一个记录时的行为，从而实现 MVCC。


字段有：
m_creator_trx_id：创建该 Read View 的事务的事务 ID。
m_ids：创建该 Read View 时，当前数据库中活跃事务（启动但未提交）的事务 ID 列表。
m_up_limit_id：活跃事务 ID 列表 m_ids 中最小的事务 ID。
m_low_limit_id：目前出现过的最大的事务 ID + 1，即下一个将被分配的事务 ID。

然后判断，如果小于最小活跃事务的id，则已提交，则说明对当前事务可见
如果大于等于最大事务+1，则说明是开启Read View之后开启的事务，肯定是不可见的
然后判断是否在事务未提交的列表中，如果在，则不可见，不在，则说明已提交，可见。

Read View创建的时机

在不同的事务隔离级别下，生成 Read View 的时机是不同的。

在 读提交（RC） 事务隔离级别下，每次执行 select 语句都会生成一个新的 Read View。

这也是为什么读提交不能避免不可重复读现象的原因，因为如果在当前事务执行期间，多次读取同一个数据的过程中，有其他事务对数据进行修改并提交事务时，新的 Read View 对修改的数据是可见的，所以会导致前后两次读取的数据不一致。


在 可重复读（RR） 事务隔离级别下，每次启动事务后，第一次 select 数据前生成一个 Read View。

这也是为什么可重复读可以避免不可重复读现象和幻读的原因，因为只会在事务第一次读取记录时生成一个 Read View，整个事务执行期间都是用这个 Read View，这样可以保证事务执行期间读取的数据始终与事务启动时读取的数据一致，并且记录数量也是一致的。

参考资料

https://juejin.cn/post/7187206201363398717

https://juejin.cn/post/6844904183922065421

https://juejin.cn/post/7214665617311088700