mysql隔离级别深入理解

今天彻底的学习一下数据库中的四种隔离机制，也算个人记录一下学习情况。

先上概念，所谓概念不清，理解混淆。

MySQL的S锁和X锁：

**共享锁（shared lock）：**也叫读锁，读锁是共享的，或者说是互不阻塞的。
**排它锁（exclusive lock）：**也叫写锁，写锁是互斥的，一个写锁会和其他的写锁或者读锁互斥，相互阻塞。

---

事务的四种隔离级别：

**读未提交：**Read Uncommitted，顾名思义，就是一个事务可以读取另一个未提交事务的数据。最低级别，它存在4个常见问题（脏读、不可重复读、幻读、丢失更新）。

读已提交： Read Committed，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。 它解决了脏读问题，存在3个常见问题（不可重复读、幻读、丢失更新）。

可重复读： Repeatable Read，就是在开始读取数据（事务开启）时，不再允许修改操作 。它解决了脏读和不可重复读，还存在2个常见问题（幻读、丢失更新）。

序列化： Serializable，序列化，或串行化。就是将每个事务按一定的顺序去执行，它将隔离问题全部解决，但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

---

数据库常见问题：

更新丢失：最后的更新覆盖了其他事务之前的更新，而事务之间并不知道，发生更新丢失。更新丢失，可以完全避免，应用对访问的数据加锁即可。

脏读：(针对未提交的数据) 一个事务在更新一条记录，未提交前，第二个事务读到了第一个事务更新后的记录，那么第二个事务就读到了脏数据，会产生对第一个未提交数据的依赖。一旦第一个事务回滚，那么第二个事务读到的数据，将是错误的脏数据。

不可重复读：（读取数据本身的对比）一个事务在读取某些数据后的一段时间后，再次读取这个数据，发现其读取出来的数据内容已经发生了改变，就是不可重复读。

幻读：（读取结果集条数的对比）一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。

常见问题举例加强理解：

首先数据库中有这个表，transactiontest表，里面数据有

id	name	balance
1	张三	300
2	李四	500
3	王五	800

更新丢失有两种：回滚丢失和覆盖丢失。

回滚丢失：

时间点	事务A	事务B
T1	begin ； 开启事务
T2	初始金额为1000元	begin ； 开启事务
T3		更新金额为1100元
T4		commit ； 提交
T5	更新金额为1050元，发生错误
T6	rollback
T7	此时金额为1000元，事务B的更新数据丢失。

覆盖丢失：

时间点	事务A	事务B
T1	begin ; 开启事务
T2	初始金额为1000元	begin ; 开启事务
T3		转账汇出100元 ， 金额变为900元
T4		commit ； 提交
T5	转账汇入100元， 金额变为1100元
T6	commit ； 提交
T7	此时金额为1100 元，事务B的金额被覆盖。

所以更新丢失导致的根本原因是因为两个事务之间操作，其中一个事务的更新，另一个事务并不知道。其中第一种回滚更新可以更换隔离级别去控制，第二种覆盖更新可以根据应用级别加锁进行控制，比如乐观锁，通过额外的版本号字段进行更新识别，更新成功版本号+1 ；比如：

时间点	事务A	事务B	版本号
T1	begin ; 开启事务		初始1
T2	初始金额为1000元	begin ; 开启事务
T3		转账汇出100元 ， 金额变为900元	先查询 1 == 1，更新+1 = 2
T4		commit ； 提交
T5	转账汇入100元， 金额变为1100元		修改失败，1 ！= 2
T6	commit ； 提交

脏读：

时间点	事务A	事务B
T1	begin ； 开启事务
T2	初始余额为1000元；	begin； 开启事务
T3	汇出100， 余额为900元 ； -->操作出错	查询余额 此时为900元
T4	rollback ；回滚
T5	余额变为1000元

事务B读取的时候读到了事务A修改的脏数据，900。

不可重复读：

时间点	事务A	事务B
T1	begin ； 开启事务
T2	查询初始金额为1000元	begin ； 开启事务
T3		汇出100元，金额为900元；
T4		commit； 提交
T5	二次查询金额为900元

幻读：

时间点	事务A	事务B
T1	begin ； 开启事务
T2	查询金额超过1000。 有5条记录；	begin；开启事务
T3		其中一个1000的汇出100变为900
T4		commit ； 提交
T5	二次查询 ， 只有4条记录

---

再说下两个概念：快照读和当前读 。

快照读：

单纯的select操作，不包括select … lock in share mode,select … for update ;
Read Committed隔离级别：每次select操作都生成一个快照读 。
Repeatable Read隔离级别：开启事务后第一个select语句才是快照读，而不是一开启事务就是快照读。
快照读比如照片，在那一时刻生成了一个照片，如果你要在进行一些PS操作，也是以这个照片为主，除非你不要这个照片，重新生成一个。

实现方式：undo log 事务日志和多版本并发控制器MVCC（Mutil-Version Concurrency Control）

多版本并发控制器MVCC（Mutil-Version Concurrency Control）：

现代数据库（MySQL，Oracle等）引擎实现中常用的处理读写冲突的手段，目的在于高并发场景下提并发读写的性能。如此一来，不同的事务在并发的过程中，select操作可以不加锁而通过MVCC机制器保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下产生的读写冲突。（加锁效率太慢了，就像隔离级别中的串行化，效率差，不适合当前潮流，然后另一个加粗字后面介绍原因）。
MVCC实现原理：通过保存某个时间节点的快照，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

了解快照图的还需了解两个概念：redo log事务日志和undo log事务日志

redo log事务日志：

前滚日志，通常是物理日志，记录的是页层次的修改，而不是某一行或者某几行的修改。比如redo log中记录的是事务的一次提交的过程，比如事务A，执行了A-1,A-2,A-3,A-4提交，那么记录的是A-4会覆盖前面的所有操作，因为只有A-4提交了。

undo log 事务日志：

回滚日志，通常是逻辑日志。比如update操作成功修改了了5行记录，此时会先在undo log中记录，再进行提交或者回滚，这个是一行一行的去进行记录。

注意：

redo log 和 undo log都是innodb自带的一个日志记录，只记录innodb层的表的修改，和DB中二进制日志有区别。二进制日志是在存储引擎之上产生的，即无论是什么类型的数据库都会产生二进制日志进行记录，但是不一定有redo log和undo log。另外undo log并不是redo log的逆向过程哦，担心有些想法和我一样的人还以为是逆向过程，☺。

说完日志说说版本链：（要想理解这些东西概念可不能少）

在innodb中，必须有一个聚簇索引（也就是索引中同时存储行信息），聚簇索引包含三个隐藏值：DATA_TRX_ID （记录最近更新这条行记录的事务ID，大小为 6 个字节），DATA_ROLL_PTR（回滚指针，指向上一个操作的事务ID，大小为 7 个字节）和没有主键或者唯一索引情况下额外创建的DATA_ROW_ID（行标识，存储行信息，大小为 6 字节）。另外，每一个行的头信息（record head）中会有一个bit（deleted_flag）来标识该行是否被删除，来对应delete操作，因为在mysql中delete也算一种更新操作。

这个图片中代表一个版本链，这个图片别人画的，挺好的。事务 A 对值 x 进行更新之后，该行即产生一个新版本和旧版本。假设之前插入该行的事务 ID 为 100 ，事务 A 的 ID 为 200 ，该行的隐藏主键为 1 。绿色的代表DATA_TRX_ID，黄色的代表DATA_ROLL_PTR，红色的代表DATA_ROW_ID。所以说版本链的记录是回滚操作必要的，每一次update，insert，delete操作都会先把当前行信息在undo log中记录再进行提交或者回滚。保证在数据库不明原因宕机出现的意外情况。

事务A的执行过程：（假设有主键的情况哦，没有主键有unique索引或者普通索引加锁情况都不同）

1. 对改行DATA_ROW_ID加排他所，即写锁 。
2. 将这行记录包括DATA_TRX_ID，DATA_ROLL_PTR先拷贝到undo log中。
3. 修改该行的值这时产生一个新版本，更新 DATA_TRX_ID 为修改记录的事务 ID ，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR 找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE ， Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁。
4. 记录 redo log ，包括 undo log 中的修改。

那么insert，delete操作会怎么做呢？insert它会在DATA_TRX_ID中存储为当前插入记录事务ID；delete则会在DATA_TRX_ID中存储删除记录的事务ID，并且去更改record head中的delete_flag。

再来说说ReadView（可读视图）吧！ 上面说了要解释为什么MVCC机制保证读取的记录值要和当前隔离级别有关。

ReadView（可读视图）：

ReadView有4个重要属性：

1. rw_trx_ids：表示生成的ReadView中活跃的事务ID集合。
2. up_limit_id：表示生成的ReadView中活跃事务中的最小事务ID 。
3. low_limit_id：表示生成的ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1
4. create_trx_id：表示生成该ReadView的事务ID 。

注意：事务ID的分配是递增的。

举个活跃事务列表例子：createt-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;
那么当前这个事务列表是：[3,11] , rw_trx_ids = [11,9,6,5,3] ; up_limit_id=3; low_limit_id=11;
假设当前这个事务去执行一个update操作，比如 update x set a=20 where id=3 ;
这个过程能否成功呢？ 需要判断的：

1. 进行update首先会把这行去加锁，（假设这个id是主键），那么会把这个主键索引处加X锁。
2. 查询数据，这个地方不是快照读哦，是当前读。如果查到的数据中,
   • 事务ID < up_limit_id ：这个好办，你比我最小的还小，说明我这个ReadView创建时，你早就被commit 提交了，数据当然是安全的，可以访问。
   • 事务ID >= low_limit_id：这个情况说明你的事务ID都比我最大的都大了，我最大的都还没commit 提交，对于我来说，我只能认为你没有commit ，所以数据访问不到，select失败
   • up_limit_id < 事务ID <= low_limit_id：两者之间，则要看看访问的数据行的事务ID是否在列表rw_trx_ids 中有记录，有则访问不到（你在我这里有记录说明你还没有提交，没有提交数据可能会回滚），没有则可以访问 。
3. 在undo log中记录，并且把回滚指针正确指向上一个版本，修改值。
4. redo log中记录，包过undo log的一些修改 。

RC隔离级别：

同一事务的每一个select操作都会创建一个ReadView 。

RR隔离级别：

同一事务并不是开启时就创建ReadView，而是执行第一个select进行快照读创建一个ReadView，后续所有的select都是复用这个ReadView。并且对于update，insert，delete来说，会将当前事务结束时这段时间内所有的update，insert，delete的操作都会加入到这个ReadView中，此时mysql就会维持一个列表。（这个很好理解，因为RR级别是可重复读，那么第一次select产生了一个快照读，当前事务执行期间可见性不会发生变化） 。

---

当前读：

select … lock in share mode（共享读锁）
select … for update --> 这个SQL语句是为这个查询上一个排他锁，我在查询，请不要修改数据
update , insert ,delete
当前读，读取的是最新的版本，并且读取的时候会加锁，加锁就是为了保证同时刻其他事务无法访问这个数据，防止修改。这个很容易理解，比如一个表，你对id=2的进行update操作，此时另一个事务进行delete操作，如果update不加锁，delete就可以成功执行，造成数据的不安全性。

实现方式：next-key锁（行记录锁+Gap间隙锁）

next-key locks 其实是一个组合record locks + Gap locks：

在innodb中运用了next-key locks 来保证了RR隔离级别下的幻读情况的发生 。

比如： SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

那么这个语句中where条件式10到20，那么10到20的范围全部锁住。为什么这么做，如果不加锁，那么我在你查询的过程中，向里面insert 一个数据 17 那么你查询出来的数据条数就会+1 ，形成幻读。所以保证读一致性，必须加锁。并且Gap locks可以共存，同一个范围类可以有多个间隙锁。

下面抛出mysql 8.0官方说明：

record lock（行记录锁）：

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 15.6.2.1, “Clustered and Secondary Indexes”.

Gap locks（间隙锁）：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be empty.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

英文不好，谷歌复制翻译去。

以上就是所有的db事务隔离机制所出现的知识点，若有问题，一定要指正。

===========20220331补充

测试上述RR级别update可以修改到对应数据测试，以及相关快照读可见性测试
测试版本mysql：8.x
开启两个cmd命令，分别进入mysql

当前存在一个t表，数据很简单，如下：

现在假设左边是A，右边是B；
A将执行以下操作

start transaction;    //开启事务
select * from t;    //开始一个快照读
insert into t(age) values(35);    //插入一条语句

B将执行以下操作

start transaction;    //开启事务
select * from t;    //开始一个快照读

总结：这里证明了，RR级别可以解决了脏读和不可重复读；

接下来测试版本链的存在以及活跃事务：
A将执行操作

start transaction;    //开启事务
select * from t;    //开始一个快照读
update t set age=27 where age=35;  //将上面插入的35更新为27
commit;  //提交

B将执行操作

start transaction;    //开启事务
select * from t;    //开始一个快照读
update t set age=55 where age=27 ;  //修改A提交的数据，重点观察这个是否生效，如果生效即可证明

补充完毕，有问题请点正