Mysql - Innodb 隔离级别的实现 (MVCC、锁机制)

Mysql 数据库

隔离级别与隔离问题

这个部分算是基础知识，稍微提及一下，具体不再介绍

READ_UNCOMMITED 读未提交

最弱的隔离级别，完全没有隔离效果，等同于可以读取到别人事务之中还没有提交的内容，也就是出现所谓的脏数据的场景。

READ_COMMITED 读已提交

查询只查询到别人已经在事务之中提交了的数据改动，也就是解决了脏数据的可能，但是在这种隔离级别下又出现了事务前后同一个查询语句可能会出现不同结果的情况。(其他事务做了修改)

REPEATABLE_READ 可重复读

实际上就是针对读已提交隔离级别的改进，正是因为读已提交的存在，使得事务前后在未做任何改动的情况下，结果却可能是不一样的，而解决了该问题的隔离级别被称为可重复读。

提到可重复读就不得不提及一个数据隔离、一致性问题 --- 幻读。实际上幻读的概念是将DML中的insert单独提了出来说的，因为在具体实现可重复读的时候，对于新增数据对两次数据集的破坏的场景是最难解决的。需要注意的是，会有人提到可重复读本身是无法解决幻读的，但其实各大数据库厂商都多少通过机制来解决幻读问题。

要明确的一点是，可重复读其实从业务场景来看，有两种类型情况的 (涉及到Mysql两种机制引入的处理逻辑)

在事务之中，两次的相同的查询操作期间是没有做任何的改动的。(从事务的角度来看，属于读写冲突 - MVCC 即使是新增数据，也可以解决)
在事务之中，两次相同的查询操作期间自身是做改动操作的。(从事务的角度来看，属于写写冲突 - MVCC + 锁机制)

SERIALIZABLE 序列化

其实就是每一次修改都直接加重量级锁，以持久化到磁盘为结束标记。相当于每次仅仅负责一个事务的工作，其他人排队等待，这样自然也就不会出现单个事务期间可能出现的数据隔离、一致性问题。

前提概念补充：从概念理解上来看，其实等同于：读未提交(脏读) -> 读已提交(不可重复读) -> 可重复读(幻读) -> 持久化。
但实际上，可重复读和幻读并非是完全拆离的，因为要求一个事务之中两次查询是一致其实本身就提到的新增数据会对原先查询结果的破坏这一场景。所以严格意义上来说，幻读本身其实应当属于可重复读的解决范畴。而我们都知道Mysql - Innodb引擎是默认支持可重复读的。下面具体介绍一下是怎么解决可重复读隔离的实现和可重复读下的幻读问题的解决的。

MVCC

MVCC机制其实本质上是通过快照读的方式来解决在同一个事务中，不涉及到改动的两次查询的结果集一致性保证的。他在实现的时候，使用到以下的工具。

MVCC主要概念

隐藏字段

Innodb实际上对所有行数据都加了三个隐藏的字段，分别是：

DB_TRX_ID*(用于记录最后一次修改该行数据的事务ID)*
DB_ROLL_PTR*(指向undoLog之中，也就是指向上一个版本的数据行)*
DB_ROW_ID*(B+树的组件ID，如果有ID该隐藏字段ID等于自定义ID，如果没有则自动生成)*

Undo Log

Innodb实现可重复读的方式其实依赖于自身实现事务原子性的关键日志 undo log，也因此隐藏字段中会指向undo log的上一个版本 (undo log上一个版本又会指向更上一个版本，从而构成所谓的版本链)

ReadView

undo log版本链可读判断基准，其实是记录在某个事务做查询的时候，数据所有的活跃事务的信息，并通过该活跃事务记录来实现事务数据访问的判断基准。

看到这里大概就懂了，其实MVCC解决并发处理的实现方式其实就是通过undo log日志之中，对所有数据的历史版本记录来实现的。但如果我们只从undo log的版本链来看，我们不知道除了最后一次修改的事务ID以外，还有谁可以使用当前版本的数据记录 (如果只用DB_TRX_ID大小判断，无法规避还没提交事务的改动)。因此实际上还需要Readview来协助标记某个事务能否访问到哪一个历史版本的数据。

ReadView

接下来在详细介绍一下ReadView的组成部分、Mysql如何使用ReadView、版本判断机制、不同隔离级别下Readview的情况问题。

ReadView组成

m_creator_trx_id：当前readview快照的创建事务ID
m_low_limit_id：创建ReadView的时候事务集之中最小的事务id
m_up_limit_id：创建ReadView的时候还没出现的事务ID
m_ids：创建ReadView的时候所有活跃事务ID集合

Readview可见性判断

实际上决定本次查询应该看到哪一个版本的数据，其实是通过查询事务的ID和readview的四个组成部分做对比来实现的，以下是对比规则：

now_select_trx_id = m_creator_trx_id，如果相同，说明是自己创建的readview，可以查看
now_select_trx_id < m_low_limit_id，说明该readview创建的时候，事务已经结束，可以查看
now_select_trx_id >= m_up_limit_id，说明该事务超出适用范围，数据可能发生修改，不应该给他看
如果 m_low_limit_id <= now_select_trx_id < m_up_limit_id，说明事务刚好属于适用范围，需要查看事务是否在活跃集合之中。如果在那么不可查看(第一步排除了自己)

总结的来说，在版本链之中一直往前找，直到找首个自己能查看的版本 (在此版本所有比自己老并且已经提交的事务的改动都可以看到，且不允许其他仍活跃事务) 可以简单画一个时间轴来看

MVCC_readview事务是否可见判断

不同隔离级别创建ReadView异同

首先明确一点innodb只支持解决事务可见性到可重复读的程度，并没有解决幻读，这里介绍三个可视性程度。

READ_UNCOMMITED

读未提交非常简单，甚至都不需要undo log + readview，直接每次都是找数据最新的记录就好了，但估计没人会有这个隔离级别也因此，MVCC并不在该隔离级别工作。

READ_COMMITED

读已提交也比较简单，在事务之中每一次查询都去构造一个新的readview，然后读这个readview就好了。这样就能保证每次查询到的都是其他已经不活跃事务提交了的修改结果。

REPEATABLE_READ

可重复读问题，其实就是指在一个事务之中做修改之前，两次相同的查询会出现两次不同的结果。实际解决起来也很简单，只记录自己刚开始事务的时候，其他已提交的修改结果，随后不更新。实际操作就是只在事务第一次查询的时候构造一个readview，随后都是时候那个readview去查询对应的数据，通过这个方式来解决可重复读问题。

为什么MVCC快照读能解决事务不做修改的时候，新增、删除操作的改动操作？
首先Select会进行查询，在查询时实际上数据库会做扫描动作，他会查看当前数据行的最后一次提交的事务ID，是否比当前事务的ID要大，并决定是否需要结合 readView的可见性判断机制 到undoLog版本链中找版本。(insert可以依靠数据隐藏字段的create_id直接解决、update则是通过readView+undolog版本链来处理的，而delete则需要再引入一个，也就是delete并不是马上删除，而是先做标记为处理本质上是先修改标记为，后面才会删除，因此其他事务delete的数据也可以依靠数据行 + undolog找到之前的版本)

SERIALIZABLE

该级别只要是事务开启就会直接加锁，对于读加共享锁、对于写就是互斥锁。当然这个级别也跟MVCC没有什么关系了

Mysql中的锁

为什么需要锁？

从上文的介绍可以知道实际上，Innodb其实通过MVCC机制已经满足了基本的可重复读场景的问题，但正如开头隔离级别和隔离问题之中的介绍一样，如果仅仅通过MVCC其实是无法解决新增数据的破坏的，他的解决方案其实就是单纯的隔离开后续的改动的记录，通过找历史的方式来实现两次查询结果一致性。但这也意味着如果我两次查询期间还有修改动作，那就势必要读取到新的数据了，此时MVCC也就失效了。
从数据库查询的角度来看，一旦有改动，就相当于从快照读变回当前读，那么当前读的情况下，也就是有改动的情况下怎么保证可重复读呢？常见的数据库引擎的答案其实就是加锁，加锁之后，其他事务便因为无法获得锁而无法进行操作。本质上跟Java中的锁也是类似的

锁类型

意向锁(Intention Locks)

所谓的意向锁，其实分为意向读锁和意向写锁，但他们的目的是一样的。就是表明表之中存在读/写锁，方便其他加锁做判断提高性能。如果不存在意向锁，那么在实际加锁时，(特别是原先有行锁，要加表锁的时候)为了确保不存在行锁往往需要逐行扫描来保证当前加锁范围之中不存在其他的加锁，有了意向锁便可以直接判断。
但意向锁在历史的发展上其实已经很少有人留意，一方面上实际开发时，除非业务有特殊的高一致性的要求，否则基本不会手动加锁，而且Mysql其实还发展出了MDL锁来处理。除了Mysql Innodb引擎在处理事务的时候会自己加意向锁以外，一般开发已经不会尝试加意向锁了
MDL锁(metadata lock) 又称元数据锁

其实就是指代共享读锁、排他写锁
1. 共享锁：实际上就是禁止一定范围的数据的修改操作，但是允许对其的查询操作
2. 互斥锁：实际上是禁止其他的连接或者说线程的任何操作，其他人不管是想干嘛都好都只能等着
关于Metadata Lock详细信息可以参考官网 MySQL :: MySQL 8.4 Reference Manual :: 10.11.4 Metadata Locking
记录锁(Record Locks)
1. 表锁：简单的来说就是直接将整个表锁住，不允许其他人做修改或者查询操作。在进行加锁的时候，只要涉及到事务加锁的时候，只要无法通过索引来优化将其优化为行锁，那必然就会是表锁。
2. 行锁：实际上行锁是只有在使用的时候，刚好能使用索引进行优化的时候才会出现的情况，Innodb会利用索引*(不管是不是唯一索引都可以)*来尝试将锁的范围进行压缩，压缩到单条记录的时候就是行锁。如果压缩到目前不存在的数据，那么就涉及到间隙锁。
间隙锁(Gap Locks)

在REPEATABLE_READ隔离级别的情况下使用的一种锁，其实就是锁定某两条数据值之间的范围，防止有其他人在自己查询的时候做DML或者DDL导致自己查询的数据范围出现变动导致结果不一致。 本质上其实是Mysql尝试解决幻读的一种方式
前提条件是，一定要有索引，否则直接就是全局表锁，更是重量级自然也就不需要间隙锁了。
```
//如果我们有一个表，表中有以下id记录：1, 4, 7, 10
//那么间隙就是：(负无穷,1), (1,4), (4,7), (7,10), (10,正无穷)
// 如果执行以下SQL
SELECT * FROM users WHERE id > 4 AND id < 7 FOR UPDATE;
//那么最终锁定的范围会是
(4,7)
```
Next-Key Locks

在REPEATABLE_READ隔离级别的情况下使用的一种锁，非常简单，就是间隙锁的升级版，相比于间隙锁Next-Keys其实就是锁定的范围从区域变为区域加上数据的本身，这也导致了有人说他是左开右闭的。从实际行动来看，其实约等于先加间隙锁，然后再加行锁 本质上其实也是Mysql尝试解决幻读的一种方式
```
//如果我们有一个表，表中有以下id记录：1, 4, 7, 10
//那么间隙就是：(负无穷,1), (1,4), (4,7), (7,10), (10,正无穷)
// 如果执行以下SQL
SELECT * FROM users WHERE id > 4 FOR UPDATE;
//那么最终锁定的范围会是
(4,7], (7,10], (10,正无穷)
```
Mysql - Innodb 所有的加锁都是由Next-Key Lock来尝试加锁，再根据实际情况做退化处理。
实际退化流程遵守以下规则：
1. 判断是否为聚簇索引或者是唯一索引的等值判断，判断是否存在该值，如果存在直接降级为行锁，如果不存在则变为间隙锁，锁住前后值的间隙 n个行锁 / 区间锁
2. 判断是否为聚簇索引或者唯一索引的范围判断，根据范围直接通过next-key lock加锁。n个next-key左开右闭区间
3. 判断是否为非唯一索引的等值判断，最后的一个next-Key lock区间的最大值如果没有等值，那么就会从左开右闭变为左开右开，也就是退化为间隙锁。n个next-key左开右闭区间 + 1个区间锁 / 1个next-key左开右闭
4. 判断是否为非唯一索引的范围判断，其实是首先通过对非唯一索引的范围锁定，保证间隙不会有新的数据插入，又因为值可以重复也需要避免原先符合条件的值被改为不符合条件了，所以也需要在主键上加锁。n个next-key左开右闭区间 + 主键id锁定 (非唯一索引范围加锁 + 原先值的行锁)
可以简单理解为：唯一性（索引类型）+ 查询类型（等值/范围）+ 记录是否存在 = 决定锁类型
自增锁(AUTO_INC)

其实就是用来解决表格的主键ID生成时候事务加锁机制，毕竟插入行为也必然涉及到并发插入可能会出现的duplicate key的可能。Mysql-Innodb就是通过加该锁实现的隔离。

以上所有的锁，其实都在Mysql官方文档之中有所介绍：MySQL :: MySQL 8.4 Reference Manual :: 17.7.1 InnoDB Locking

业务层面的锁认识

如果从业务层面来看锁的话，其实本身就跟Innodb的技术角度来看关系不强了，其实简单的来说就是从程序开发的角度来避免数据库表锁、行锁的使用。

乐观锁：也就是标记为 + CAS自旋加锁的处理，原理其实等同于 synchronized 轻量级锁的处理。如果不想看 syncrhonized 其实可以直接看搜索 ABA和CAS。非常简单的逻辑这里就不再赘述了。
悲观锁：逻辑上其实也非常简单，简单来说就是针对涉及的数据本身直接做加锁处理即可。

不可重复读下的幻读问题解决

Select穿插DML的可重复读

MVCC不支持两次查询间存在DML操作的根本原因其实是，实际上当两次的查询之间如果存在数据操作动作 (DML语句的执行)，会导致ReadView变更。为了实际能查看上一个修改语句的实际效果 (ACID) ，快照读会失效，并变为当前读。ReadView的更新不可避免的会加载到其他事务提交的结果，从而导致第二次查询不可能跟第一次查询是一样的。
从而就需要 Next-Key 出场了，也非常简单，在第一次的select的时候，就直接使用 select ... for update / select ... lock in share mode 也就是通过添加互斥锁和共享锁的方式来实现的。

可重复读的幻读解决核实

在上文介绍锁类型的时候，其实就提到，Innodb其实针对不同的索引类型和不使用索引的时候加锁的方式都有所不同，所以下面的示例将会以唯一索引的角度和非唯一索引的角度展开，至于不使用索引就不介绍了，毕竟是直接加表锁实现的。

对主键(唯一索引)for update效果对比

首先准备两个Session，这里用的是DBeaver提供的直接通过SQL编辑器，并配置数据库为手动提交事务的方式来实现事务控制。(SessionA B都进行开启手动提交-可重复读)。

在 lf_pla_user表之中存在以下的数据

id	uuid
10034	10013
10035	10014
10036	10015

SeesionA之中准备的SQL如下：

BEGIN

	SELECT * FROM lf_pla_user lpu WHERE ID > 10034

	UPDATE lf_pla_user SET uuid = 'HELP' WHERE ID > 10034

	SELECT * FROM LF_PLA_USER LPU WHERE ID > 10034

END

SessionB之中准备的SQL如下：

BEGIN
	INSERT INTO lf_pla_user (id, uuid, password) VALUES (10037, 'bushi,gemen');
END

显然的，SessionA之中是先查询 ID > 10034 的数据，然后将他们的uuid字段改为HELP，并在事务结束之间查询检查是否改动成功；而SessionB就是单纯的插入一条新的数据。
也当然，我们要做的就是。

不使用For Update加锁会怎么样？

SessionA查询，成功也确实是ID > 10034，目前只有两条
SeesionB开始事务，执行插入操作，回报插入成功 (此时不要提交SessionB事务)

SessionA尝试执行 UPDATE 操作 (其他DML也可以)，显示尝试执行，并最终超时报错，提示发生死锁建议重试

超时之前的加锁信息

超时之后的信息

我们可以配合 show processlists + show profile for query N + performance_schema (data_locks 、 events_statement_current、threads) + show open tables where in_use > 0 等手段查看具体的死锁信息

这里为了简单介绍，只使用 performance_schema 来查看死锁情况

SELECT  LOCK_TABLE.THREAD_ID, PROCESSLIST_ID, SQL_TEXT, MYSQL_ERRNO, MESSAGE_TEXT, LOCK_TABLE.OBJECT_SCHEMA, LOCK_TABLE.OBJECT_NAME,LOCK_TYPE, LOCK_MODE, LOCK_DATA 
FROM PERFORMANCE_SCHEMA.DATA_LOCKS AS LOCK_TABLE
	 INNER JOIN PERFORMANCE_SCHEMA.events_statements_current AS EVENT_TABLE
	 		 ON EVENT_TABLE.THREAD_ID = LOCK_TABLE.THREAD_ID
	 INNER JOIN PERFORMANCE_SCHEMA.THREADS AS THREAD_TABLE
	 		 ON THREAD_TABLE.THREAD_ID = EVENT_TABLE.THREAD_ID

可以看到，实际上我们的表格已经加了互斥锁，SessionB事务的插入操作对整个表添加了 IX 互斥锁，这使得SessionA的DML操作失败了 (MYSQL_ERRNO不为0，以及 MESSAGE_TEXT)

上图之中显示存在以下加锁

SessionA	SessionB
TABLE级别 IX (意向排他锁) - 失败 (SessionB已经锁定)	TABLE级别 IX (意向排他锁) - 成功
RECORD级别(行锁 10035) X (排他锁) - 失败 (SessionB已经锁定)
RECORD级别(行锁 10036) X (排他锁) - 失败 (SessionB已经锁定)
RECORD级别(行锁 10037) X,REC_NOT_GAP (排他锁 + ID提示没有间隙锁) - 失败 (SessionB已经锁定)

补充介绍：需要注意的是SessionB为了实现Insert实际上添加的全表的互斥锁，IX (意向排他锁)。而SessionA为了实现范围的Update操作，首先尝试针对全表的 IX 意向排他锁，并且由于我们Update是根据聚簇索引来更新的，因此针对具体数据做的是行锁的排他锁 X，而针对10037的更新，由于是边界ID，因此又在 X 后追加了一个REC_NOT_GAP表明没有使用间隙锁。

意向排他锁：表明当前事务想要在该表之中使用一些行级排他锁

提交SessionB事务，可以看到我们新增数据已经可以其他Session中查出来
执行SessionA中DML操作，可以发现原先回报加锁超时的情况已经不在可以，并且要修改的两条第一次Select的数据可以在自己事务提交前查询到未提交的修改结果。
提交事务，最终结果

改SessionB为自动提交会怎么样？

上面的结果很明显，就是先开始SELECT的事务在其他事务做插入操作但没有提交时做DML会被加锁阻塞，这实际上就是因为我们的DML其实会被Mysql自动做加锁处理，从而阻塞了SessionA的修改操作，导致事务执行失败。那如果SessionB改为手动提交或者插入成功后直接提交呢？

SessionB直接插入到表之中

再次查询，由于MVCC的作用，我们的结果还是一致的

但一旦我们DML，实际改动行数就变成3了，也就出现了幻读导致处理数据行数不符合预期的问题了。MVCC的ReadVoew由于DML的改动变为不可见了，相当于强制变为了当前读

先SeesionA的DML，再SeesionB的DML

结果是一样会出现加锁超时的报错，只不过这一次会变成后尝试加锁的SessionB提示加锁失败

提示加锁失败前查询锁表SQL (提示加锁失败后，最后一行的记录会从 events_statement_current 删除)

上图之中显示存在以下加锁

SessionA	SessionB
TABLE级别 IX (意向排他锁) - 成功	TABLE级别 IX (意向排他锁) - 失败 (SessionA已经锁定)
RECORD级别(行锁 10035) X (排他锁) - 成功	RECORD级别(supremum pseudo-record) X,INSERT_INTENTION (排他锁, 插入意向锁) - 失败 (SessionA已经锁定)
RECORD级别(行锁 10036) X (排他锁) - 成功
RECORD级别(supremum pseudo-record) X (排他锁) - 成功

从这个结果就可以看出来，其实加锁都是一样的，只不过等同于先DML的一方先做加锁罢了。

使用For Update会怎么样

其实就是手动的让SessionA来先加锁，从而保证SessionA的事务能够成功执行

一样的，在SessionA执行

SELECT * FROM lf_pla_user lpu WHERE ID > 10034 FOR UPDATE

然后在SessionB执行

INSERT INTO lf_pla_user (id, uuid, unit_name, password) VALUES (10037, 10016, 'bushi', 'gemen');

在超时前查看锁表信息，便可以看到以下的加锁信息

超时后查看锁表信息，便可以查看到

SessionA	SessionB
TABLE级别 IX (意向排他锁) - 成功	TABLE级别 IX (意向排他锁) - 失败 (SessionA已经锁定)
RECORD级别(行锁 10035) X (排他锁) - 成功	RECORD级别(supremum pseudo-record) X,INSERT_INTENTION (排他锁, 插入意向锁) - 失败 (SessionA已经锁定)
RECORD级别(行锁 10036) X (排他锁) - 成功
RECORD级别(supremum pseudo-record) X (排他锁) - 成功

从结果来看，其实等同于先执行SessionA的DML，然后再执行SessionB的。

对非唯一索引for update的异同

还是上面的例子，只不过这次我们执行的SQL修改的字段从ID变为了另外一个字段UUID，并且是非唯一索引

数据本身是没有变化还是上面的数据，但是SQL需要做一些改变

SessionA

	SELECT * FROM lf_pla_user lpu WHERE UUID > 10013 /* FOR UPDATE */

	UPDATE lf_pla_user SET uuid = 114514 WHERE UUID > 10013

	SELECT * FROM LF_PLA_USER LPU WHERE UUID > 10013

SessionB

	INSERT INTO lf_pla_user (id, uuid, unit_name, password) VALUES (10037, 10016, 'bushi', 'gemen');

不使用For Update加锁会怎么样？

首先我们先来测试一下，不加 FOR UPDATE 的情况，也就是SessionA和SessionB都在手动提交的情况下，显然SessionA查询，然后SessionB DML再到SessionA DML看看加锁情况。

同样的其实也分超时前和超时后，以下是超时之前的加锁信息

超时后的加锁信息如下

SessionA	SessionB
TABLE级别 IX (意向排他锁) - 失败	TABLE级别 IX (意向排他锁) - 成功
RECORD级别(UUID 10014 - ID 10035) X (排他锁) - 失败
RECORD级别(UUID 10015 - ID 10036) X (排他锁) - 失败
RECORD级别(ID 10035) X (行锁排他锁无间隙) - 失败
RECORD级别(ID 10036) X (行锁排他锁无间隙) - 失败
RECORD级别(UUID 10016 - ID 10037) X (排他锁) - 失败
RECORD级别(UUID 10016 - ID 10037) X (行锁排他锁无间隙) - 失败

结果其实也跟唯一索引也是一样的，只要不加for update，那么就是先做DML的一方只要不提交事务就会阻塞其他事务的执行。

那SessionB改为自动提交呢？

这里就很简单了，不再过多介绍了，直接上截图，结果也是幻读破坏了预期的修改范围。两次的SELECT在MVCC的支持下都是一样的，但是UPDATE就是三个。

如果完全按照上面的流程我们可以先把他们执行顺序调转继续证明DML的顺序不同的效果，但是我认为到这里应该不需要继续验证这个内容了。我们可以直接来介绍一下加For Update的效果。

使用For Update会怎么样？

本质上非唯一索引的For Update跟唯一索引的没啥区别就是保证了SessionA的加锁，使得SessionA事务能够阻塞其他事务的动作，从而保证了隔离性和数据的一致性。

同样的，先SessionA

SELECT * FROM lf_pla_user lpu WHERE UUID > 10013 FOR UPDATE

再SessionB

INSERT INTO lf_pla_user (id, uuid, unit_name, password) VALUES (10037, 10016, 'bushi', 'gemen')

在未超时时可以查看到以下加锁信息

超时后可以查看到以下信息

SessionA	SessionB
TABLE级别 IX (意向排他锁) - 成功	TABLE级别 IX (意向排他锁) - 失败
RECORD级别正无穷 X(排他锁) - 成功	RECORD级别正无穷 X(排他锁) - 失败
RECORD级别(UUID 10014 - ID 10035) X (排他锁) - 成功
RECORD级别(UUID 10015 - ID 10036) X (排他锁) - 成功
RECORD级别(ID 10035) X (行锁排他锁无间隙) - 失败
RECORD级别(ID 10036) X (行锁排他锁无间隙) - 失败

显然的，与唯一索引一样，都是保证了SessionA也就是的加锁，并阻塞SessionB事务，从而保证了SessionA能够顺利执行。

两种索引的加锁不同

针对唯一索引进行DML的时候 (不管是自动DML锁还是手动加锁)，其实就是next-keys然后根据实际情况，判断是否存在数据可以退化为行锁，如果期间存在间隙则退化为间隙锁 / 保持next-keys。

针对非唯一索引进行DML的时候 (不管是自动DML锁还是手动加锁)，其实就是利用next-keys先锁定非唯一索引的间隙，保证不存在新的数据插入，另外在对原先的存在的值加聚簇索引的行锁，从而避免修改问题

范围查询加锁示例补充

为了防一手有人钻牛角尖，这里补充一个唯一索引范围加锁的时候，ID不连续时的加锁情况。我先补充了多条类似的数据

现在在 lf_pla_user表之中存在以下的数据

id	uuid
10034	10013
10035	10014
10036	10015
10040	10020
10041	10021
10045	10022

唯一索引加锁

此时，我们在SessionA，执行以下SQL

SELECT * FROM lf_pla_user lpu WHERE ID > 10034 FOR UPDATE

然后在SessionB执行以下SQL，其实就是往 (10036, 10040) 区间中尝试加锁

INSERT INTO lf_pla_user (id, uuid, unit_name, password) VALUES (10037, 10016, 'bushi', 'gemen');

得到以下加锁信息，超时前

超时后

在实际超时加锁期间，其实是尝试往 10040 之前的行锁 + 间隙 + 意向插入锁尝试加锁处理，当然实际也加锁失败了

非唯一索引加锁

将SessionA的SQL改为

SELECT * FROM lf_pla_user lpu WHERE UUID > 10013 FOR UPDATE

得到以下加锁信息，超时前

超时后

显然的，其实是往UUID 10020 ID 10040尝试加了next-key做加锁处理，但是失败了

总结

所以更加简单的办法就是记住一点，MVCC的ReadView只能解决读写冲突，对于写写冲突，只能直接加锁。加锁后，由两个冲突事务之间一方先执行完毕，再轮到另一个事务执行，直接就避免了不可重复读的问题。这个加锁的处理，其实是next-key锁间隙锁区间开闭锁，只影响一部分的行，避免了表锁的出现。需要注意的是如果想要使用间隙锁，我们必须要在事务的第一次查询的时候就是用 select ... for update / select ... lock in share mode，否则，其他事务一样有可能会导致幻读

如果觉得文章对你有用，请随意赞赏

Mysql 数据库 Innodb

Mysql - Innodb 隔离级别的实现 (MVCC、锁机制)

https://www.leticiafeng.com/archives/3ddacf63-de47-4dfd-8d50-14f3f790adc3

作者

LeticiaFENG

发布于

2024-11-27

更新于

2024-11-28

许可协议

CC BY 4.0

Mysql - Innodb 隔离级别的实现 (MVCC、锁机制)

隔离级别与隔离问题

READ_UNCOMMITED 读未提交

READ_COMMITED 读已提交

REPEATABLE_READ 可重复读

SERIALIZABLE 序列化

MVCC

MVCC主要概念

隐藏字段

Undo Log

ReadView

ReadView

ReadView组成

Readview可见性判断

不同隔离级别创建ReadView异同

READ_UNCOMMITED

READ_COMMITED

REPEATABLE_READ

SERIALIZABLE

Mysql中的锁

为什么需要锁？

锁类型

不可重复读下的幻读问题解决

Select穿插DML的可重复读

可重复读的幻读解决核实

对主键(唯一索引)for update效果对比

不使用For Update加锁会怎么样？

先SeesionA的DML，再SeesionB的DML

使用For Update会怎么样

对非唯一索引for update的异同

不使用For Update加锁会怎么样？

使用For Update会怎么样？

两种索引的加锁不同

范围查询加锁示例补充

唯一索引加锁

非唯一索引加锁

总结

作者

发布于

更新于

许可协议

评论