【数据库】事务？隔离级别？LBCC？MVCC？

2022-10-26 15:18:21 浏览数 (1)

MySQL 事务

之前对事务的了解仅限于知道要么全部执行，要么全部不执行，能背出 ACID 和隔离级别，知其然但不知其所以然，现在觉得非常有必要系统学一下，关于事务，关于 LBCC，关于 MVCC，关于死锁 ……

并发的问题

所谓 事务 是用户定义的一个 数据库操作序列， 这些操作要么全做，要么全不做，是一个不可分割的工作单位，在关系型数据库中，一个事务可以是一条 SQL 语句，一组 SQL 语句或者是整个程序，事务的开始和结束由用户显示控制，如果用户没有显式定义事务，则由 DBMS 按默认规定自动划分事务，如在 MySQL 中默认 autocommit 为 ON 则开启事务自动提交，每条没有显式定义事务的 SQL 语句都会被当作一个单独的事务并自动提交：

代码语言：javascript复制
mysql> show session variables like 'autocommit';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| autocommit    | ON    |
 --------------- ------- 
1 row in set, 1 warning (0.00 sec)

事务有四个特性，即 ACID：

  • A（Atomicity）: 原子性 ，事务是数据库的基本工作单位，事务中包含的诸操作要么都做，要么都不做。
  • C（Consistency）: 一致性，事务的执行结果必须使数据库从一个一致性状态转换到另一个一致性状态，所谓一致性状态是指数据库中因该只包含成功事务执行的结果，如果一个事务在执行过程中被迫中断，但这个未完成的事务对数据库的部分修改已经写入物理数据库，这时数据库就处于一种 "不正确" 的状态，或者称处于 "不一致" 的状态。
  • I （Isolation）: 隔离性，一个事务的执行不能被其他事务所干扰，即一个事务内部操作所使用的数据对其他并发的事务应该是隔离的，所有并发执行的事物之间不能相互干扰。
  • D（Durability）：持久性，一个事务一旦提交，他对数据的操作就应该是持久的，接下来的其他操作或故障不应该对他有任何影响。

只有保证了事务的 ACID 特性，对数据库的操作才能是安全的，因此，不管是 LDCC 还是 MVCC，其核心目的都是保证事务的 ACID 特性。可能破坏事务 ACID 特性的因素包括：

  1. 多个事务并行运行时，不同事物的操作交叉执行。
  2. 事务执行过程中被强制停止。

上面两个因素分别对应事务处理的两类技术：并发控制和数据库恢复技术，事务被强制终止一般由事物内部故障，系统故障等造成，发生类似故障时，一般采用日志文件恢复等方法使数据库恢复到上一个一致性状态，这里着重研究由并发导致的 ACID 被破坏的情况和解决方案。

并发导致的数据不一致包括：丢失修改，脏读，不可重复读，幻读。

丢失修改

如上图，两个并发的事务 T1, T2 同时读表中的某条记录 total 得到 16， 并且对其进行修改，最终造成后提交的事务 T2 的修改结果覆盖了先提交的事务 T1 的修改结果，这种现象叫做丢失修改。

针对丢失修改，还有一种情况，就是如果 T2 在修改 total 之后发生异常进行了回滚，就会导致 total 值重置为 16，这种现象被称为 回滚覆盖，而第一种情况被称为提交覆盖

不可重复读和幻读

事务 T1 读取数据后，T2 对该数据进行了更新操作， 导致 T1 无法再次读到前一次读取的结果，这种现象叫做不可重复读，导致不可重复读的原因包括下面三种情况：

  1. T1 读取某一数据后，T2 对其进行了修改，导致 T1 再次读该数据时，得到与之前不一致的值。
  2. T1 读取某些数据后，T2 删除了其中的某些数据，当 T1 再次读这些数据时，发现之前的一些数据神秘消失了。
  3. T1 读取某些数据后，T2 向其中插入了一些数据，当 T1 再次读这些数据时，发现莫名其妙多了一些数据。

后两种情况也被常常称为 幻读， 幻读与第一种情况的不同在于：

  1. 幻读往往在读取某一范围的数据时产生。
  2. 幻读是因为其他事务执行了插入或删除语句导致的，但第一种情况一般是执行更新语句导致的。

为了方便，不可重复读一般只指第一种情况，幻读指后两种情况。

脏读

事务 T1 修改了某条记录，T2 读取到了 T1 未提交的这条记录，但 T1 由于某些原因被回滚了，这就导致了 T2 读取到的数据与数据库中的数据不一致，即脏数据。

对比

导致上述四种不一致的原因就是破坏了事务的隔离性，进而导致一致性被破坏，而保证数据隔离性的方法就是使用正确的方式调度并发操作， 但有的时候，为了性能，我们有允许牺牲一部分隔离性，比如对有些数据量很大，少量脏数据对结果影响很小或影响可以接收时，我们可以降低一致性要求以减少系统开销，这就是数据库的隔离级别。

隔离级别

脏读

不可重复读

幻读

读未提交 Read uncommitted

可能

可能

可能

读已提交 Read committed

可能

可能

可重复读 Repeatable read

可能

串行化 Serializable

并发控制技术

隔离级别只是定义了在不同的级别下应该保证哪些一致性，具体实现这些隔离级别的方法有很多，如传统的基于锁的并发控制（LBCC），还有一些无锁并发控制方案，如时间戳（timestamp), 乐观控制法（scheduler），多版本并发控制（MVCC）等，我们主要探索基于锁的并发控制（LBCC）和多版本并发控制（MVCC）

基于锁的并发控制（LBCC）

所谓封锁就是事务在某个数据对象进行操作之前先申请锁，对该对象加锁后，该事务就拥有了一定的对该对象的控制，在该事务释放该锁前，其他事务不能操作此数据对象。

从锁的模式来看，锁可以分为共享锁和排它锁，共享锁又称为读锁（S 锁），排它锁又称为写锁（X锁）。

  • X 锁：若事务 T 对数据对象 A 加上了 X 锁，则只允许 T 读取和修改 A， 其他任何事务不得再对 A加任何类型的锁，直到 T 释放锁，。
  • S 锁：若事务 T 对数据对象 A 加上了 S 锁，则 T 和其他事务都可以可以读 A，同时其他事务可以继续申请 A 的 S 锁，但是直到所有事务都释放 A 的 S 锁为止（所有事务并不包括自己），A 是不允许修改的。这就意味着如果只有一个事务对 A 添加了 S 锁，那他自己是可以修改数据的。
封锁协议
  • 一级封锁协议： 事务 T 在修改数据 R之前必须对其加 X 锁，直到事务提交或回滚才释放锁，一级封锁协议可以防止丢失修改，一级封锁协议只在写数据时加锁，读数据时并不需要获取锁，所以它无法解决脏读，幻读，不可重复读。
  • 二级封锁协议：要求在一级封锁协议的基础上，读数据前必须加 S 锁，读完即可立刻释放 S 锁。因为对数据 A 加 X 锁后，不允许其他事务再申请其他锁，所以事务要想获得 S 锁读，就必须等持有 X 锁的事务写完提交或回滚，这样就可以避免脏读，但由于二级封锁协议允许读完后立刻释放 S 锁，无法保证下一次读时数据不被修改，所以所以它不能保证可重复读（包括幻读）。
  • 三级封锁协议：要求在一级封锁协议的基础上，读数据前必须加 S 锁，直到事务结束才释放。该协议可以解决不可重复读（包括幻读）的问题。
死锁，活锁

死锁和活锁是使用 LBCC 解决一致性问题时必须考虑的问题：

  • 活锁：如果 T1 封锁了 R，T2 请求 R 的锁，这时 T2 应该等待，然后 T3 也请求 R 并等待，这时 T1 释放了 R 的锁，但该锁被 T3 获得，在这过程中， T4 也请求 R， T3 释放锁后锁又被 T4 获得…… 这导致 T2 一直无法获得锁，这种某个事务陷入饥饿的状态现象叫做活锁，避免活锁的简单策略是先来先服务。
  • 死锁：如果 T1 封锁了 R1, T2 封锁了 R2, 然后 T1 又来申请 R2, T2 又来申请 R1, 这会造成 T1 T2 相互等待永远无法结束的局面，形成死锁。
预防死锁

死锁出现的原因是两个事务都已经封锁了一些数据对象，然后都去请求已经被对方锁定的数据对象，预防死锁就是要想办法破坏死锁产生的条件，通常使用一次封锁法和顺序封锁法解决：

  • 一次封锁法：一个事务必须一次性将所有要使用的数据全部加锁，否则就不能继续执行。但由于数据库中的数据并不是一成不变的，我们往往很难准确知道要用哪写数据，为了实现一次封锁，就不得不扩大封锁范围，将可能的数据全部加锁，这会造成系统并发度降低，影响性能。
  • 顺序封锁法：预先对数据对象规定一个封锁顺序，所有事物按这个顺序实施封锁，但这样同样存在问题：
    • 数据库中的数据对象很多，要维护这么多对象的封锁顺序并不容易。
    • 谁应该先被封锁，谁应该后被封锁，关于这个顺序很难得到一个最优解。
解除死锁

上面个两种预防死锁的办法在现实中可行性很低，所以DBS通常采用诊断并解除死锁的办法解决死锁问题。

诊断死锁：

  1. 超时法：如果一个事务的等待时间超过特定期限，就认为其发生了死锁。
  2. 等待图法：将事务和其等待的数据对象以有向图的形式组织，检测图中是否有回路，发现回路即为死锁。

解除死锁：

  • 发现死锁后，我们往往会选择一个处理死锁代价最小的事务，将其撤销以释放他持有的所有锁，当然，对撤销事务的数据修改操作必须加以修复。
可串行化调度和两段锁协议

可串行化调度：

  • 如果多个事务并发执行的结果和按某一次序串行执行这些事务的结果是一样的，就说这种调度是可串行化调度，他是并发事务正确调度的准则。

两段锁协议（2PL）：

  • 两段锁协议用来保证调度是可串行化的
  • 两段锁协议是指所有事务必须分两个阶段对数据项进行加锁和解锁操作。
  • 在对一个数据项进行读写操作前，必须申请并获得该数据项的封锁。（拓展阶段）
  • 释放完一个封锁后，事务不再去申请或获取任何其他锁。（收缩阶段）
封锁粒度

加锁时，你可以选择对一个属性值，关系，索引项，整个索引甚至整个数据库加锁，加锁对象的大小叫做锁的粒度，一般来说，粒度越大，并发度越小，系统开销也越小，封锁粒度越小，并发度越高，系统开销也就越大。一个 DBS 应该尽可能兼顾并发度和系统开销，这样显然不能只支持某一粒度的封锁，这种提供多种封锁粒度供不同事务选择的封锁方法叫做多粒度封锁。

多粒度封锁可以由多粒度树描述，如下：

多粒度树的根节点是整个数据库，表示最大的数据粒度，叶子节点表示最小的封锁粒度，多粒度封锁协议 允许粒度树的每一个节点可以被单独加锁，某一个节点加锁意味着该节点的所有子节点也被加同样的锁，对该节点来说，这个锁属于显式加锁，对于其子节点来说，属于隐式加锁，他们的效果是一样的。

显式和隐式加锁看起来是理所当然的，但这会导致一个问题，在对某一数据项加锁时，我们必须保证当前要加的这把锁与其显示假的锁不冲突，同时还要保证与其隐式假的锁不冲突，为此，在加锁前，我们必须要：

  1. 检查数据项有无显示加锁，保证不与其冲突。
  2. 检查数据项的所有父节点，保证不与其隐式加锁冲突。
  3. 检查数据项的所有子节点，保证加锁后由于本次加锁获得隐式锁的数据项不与其原来的锁冲突。

这样一来，每次加锁我们不得不遍历整个粒度树，这种效率是非常低下的，为此，我们引入了一种新锁：意向锁

意向锁用于提升加锁效率，无法手动创建，它的含义是如果对一个节点加意向锁，则说明该节点的下层节点正在被加锁，对任意节点加锁时，必须先对它的上层节点加意向锁。有三种常用的意向锁，他们分别是：意向共享锁（IS 锁），意向排它锁（IX锁），共享意向排它锁（SIX锁）：

  • IS 锁：表示其子节点准备加 S 锁
  • IX 锁：表示其子节点准备加 X 锁
  • SIX 锁：如果对一个数据对象加 SIX 锁，表示对他加 S 锁，在家 IX 锁，例如对某表加 SIX 锁，表示该事务要通过 S 锁读整个表，同时还要更新个别元组（IX锁）。

根据上面的描述，我们可以得出以下的锁强度偏序关系图和数据锁的相容矩阵：

锁强度表示一个锁对其他锁的排斥程度

X

SIX

IX

S

IS

-

X

F

F

F

F

F

T

SIX

F

T

T

F

T

T

IX

F

T

T

F

F

T

S

F

F

F

T

T

T

IS

F

T

T

T

T

T

-

T

T

T

T

T

T

规律：对于意向锁来说使用强度更高的锁来替换强度低的锁是安全的。

意向锁如何提高加锁效率？

如果某一时刻，数据库中数据对象的锁持有情况如下图粒度树所示，对表 T1 加了 S 锁，其父节点对应加了 IS 锁，T1 的子节点也隐式获得了 S 锁（当然所有节点也隐式获得了 IS 锁，所有隐式锁都未画出）

如果现在我们希望更新记录 R1，根据封锁协议，就必须对 R1 添加 X 锁，对其父节点加 IX 锁，这时只需要检查 T1 和 DB 的锁是否与之不相容，T1 持有 S 锁，与 X 锁不相容，调度器会阻止加锁。

相反，如果我们只是想对 R1 加 S 锁进行读操作，就需要先对父节点加 IS 锁，T1 持有 S 锁，与 IS 锁相容，再检查 R1 持有的锁是否与 S 锁相容，相容，允许加锁。

加入意向锁后，我们不需要再去遍历所有子节点便可以判断能否未数据项加锁，可以提高系统并发度，减少加锁解锁开销。

多版本并发控制（MVCC）

通过 LBCC， 我们可以解决所有的并发不一致问题，那为什么还会有其他并发控制方案呢？归根结底还是基于性能的考虑， LBCC 只是实现了允许并发读，但对于并发读写，写写操作只能串行执行，在读写都很频繁的场景下，并发性能将大大降低，因此，人们才提出各种无锁并发控制方案，MVCC 就属于其中一种。

MVCC 的大概思路是每一个事务都有一个唯一的ID，当某一个事务要修改某行数据时，先将这一行原来的数据做一个快照保存下来，当有其他并发事务也要操作这个事务时，可以操作之前的版本，这样，最新的版本只被写事务维持，不会干扰到读事务，以此实现隔离，MVCC 并没有一个统一的标准，不同 DBS 的实现也不尽相同，下面以 MySQL InnoDB 引擎为例说一下 MVCC 的一个具体流程。

InnoDB 中的 MVCC

既然是 MVCC， 那最重要的就是旧版本的数据要存在，在 MySQL InnoDB 中，这些数据会以 回滚段 （rollback segment）的形式保存在表空间中，更具体来说，他们会被保存在 undo log 中。

其次，InnoDB 会在所有表中加两个隐藏列 DB_TRX_ID 和 DB_ROLL_PTR，前者占 6 字节，表示插入或更新该行的最后一个事务的事务标识符；后者占 7 个字节，称为回滚指针，指向回滚段的 undo log 记录。

InnoDB 插入的隐藏列还有一个 DB_ROW_ID，会随着新行的插入会单调递增，如果使用了默认自增ID的聚簇索引，索引中就会包含这个列。

每当我们插入或更新一行数据（删除被认为是更新的一种），InnoDB 会为这个事务分配一个唯一单调递增的事务ID，这个 ID会记录在这一行的 DB_TRX_ID 中，表示这一行数据的最新版本。

如下图，设有一张表 t, 包含两个字段 id 和 name, 它的初始状态如下：

表示最后插入或修改这条记录的事务 ID 是 100，现在如果有一个新的事务要修改这条记录，设其 ID 为 200，则现在表的状态就会变成下面这样：

灰色行表示历史版本，被记录在 undo log 中，从最新版本的回滚指针可以找到这条记录的历史版本，这条链表被叫做版本链，

除此之外，当一个事务第一次执行读操作时，会为该事务生成一个一致性视图 read-view, 这个数据结构包含此刻所有活跃着的（未提交的）写事务的事务ID列表和此时DBS分配出去的最大事务号，有了 read-view 就可以判断出哪写事务是已经提交了的，哪写事务是未提交的，具体判断逻辑如下：

从活跃事务列表中找到最小的事务ID，记为 min_t_id, 读取到某一版本的事务ID记为 row_t_id, 系统分配的最大事务ID 记为 max_t_id

  1. 如果 row_t_id < min_t_id, 说明这一版本的事务一定被提交了，这一版本可见
  2. 如果 row_t_id > max_t_id, 说明这一版本的事务还没有开始，一般不存在这种情况，不可见
  3. 如果 row_t_id >= min_t_id && row_t_id <= max_t_id, 还需要分两种情况讨论：
    1. 如果 row_t_id 在活跃事务列表中，则说明该版本是由未提交的事务创建的，不可见，但对于自身事务是可见的。
    2. 如果 row_t_id 不在活跃事务列表中，说明创建该版本的事务已提交，该版本是可见的。

有了版本链和 read-view，一个事务就可以根据 read-view 顺着版本链依照上面的规则一直往下直到找到一个可见的版本，

以上面的例子为例，如果事务的并发时序图如下：

当事务二更新操作执行后，版本链变为：

事务三第一次执行查询语句时，生成的 read-view 为：

代码语言：javascript复制
|    active list    |  max   |
 ------------------- -------- 
|     200, 300      |   300  |
 ------------------- -------- 
          read  view

根据 read-view 得到 max_t_id = 300, min_t_id = 200, 从最新版本开始遍历，

  1. row_t_id = 300, 满足条件3，同时 row_t_id 在 active list 中，说明这一版本未提交，不可见，根据回滚指针检查下一个版本。
  2. row_t_id = 200，同样满足条件三，在 active list 中，不可见，看下一个版本。
  3. row_t_id = 100, 满足条件一，可见，则这个读事务会使用这一版本的信息。

如下图，如果在第五时刻，事务 2 提交，第六时刻事务三再次读取：

版本链并没有发生改变，但如果在 RC 的隔离界别下，事务三的第二条查询语句会重新生成 read-view, 这时活跃事务只有 200，最大事务为 300，按上面的规则，row_t_id = 300 时，满足条件三但不在活跃事务列表中，所以这条记录是可见的，这就会读出 wangwu, 导致不可重复读。

但如果使用 InnoDB 默认的 RR 隔离界别，read-view 只会在事务执行第一条查询语句时生成，后续所有查询语句使用同一个 read-view, 由此避免不可重复读。

一些其他问题

InnoDB 中的锁

在上面我们介绍了 LBCC，这里再简单介绍一下 InnoDB 中几个具体的锁，他们以锁粒度划分。

记录锁

Record Locks，也叫行锁，加在索引记录上的锁。例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE, 防止任何其他事务插入、更新或删除 t.c1 值为10的行, 行锁加在索引上而不是记录上，因为innodb一定会有一个聚簇索引，因此最终的行锁都会落到聚簇索引上。

间隙锁

gap Locks, 是对索引记录之间间隙加的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如：

代码语言：javascript复制
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE

这样可以防止其他事务插入 c1 值在 10 - 20 之间的数据。

这里的间隙可以是两个索引值之间的间隙，也可以是从无穷到单个索引值之间的间隙

使用唯一索引检索唯一行时不会使用间隙锁，但是如果检索条件仅包含多列唯一索引的一些列时，仍然会使用间隙锁锁定，例如一个简单的 SELECT 语句：

代码语言：javascript复制
SELECT * FROM child WHERE id = 100;

如果 id 使用唯一索引，则该语句只会加记录锁。

但如果 id 未建立索引或建立了非唯一索引，那么从负无穷到100的间隙将会被间隙锁锁定。

还有一个需要值得注意的问题，在一个间隙上，不同的事务可以持有相互冲突的锁，这是因为如果某条记录被从索引中清除，那我们必须合并由不同事务保存在记录上的间隙锁。所以说间隙锁的唯一目的是为了防止记录被插入间隙， 一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定。共享和专用间隙锁之间没有区别。 它们彼此不冲突，并且执行相同的功能。

间隙锁定可以显式禁用。 如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_unsafe_for_binlog系统变量（现已弃用），则会发生这种情况。 在这种情况下，将禁用间隙锁定来进行搜索和索引扫描，并且间隙锁定仅用于外键约束检查和重复键检查。

使用 READ COMMITTED 隔离级别或启用innodb_locks_unsafe_for_binlog还有其他效果。MySQL 在计算完 WHERE 条件后，将释放不匹配行的记录锁。 对于 UPDATE 语句，InnoDB 进行“半一致”读取，以便将最新的提交版本返回给 MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。

间隙锁总结
  1. 进行范围查询或使用非唯一索引作为检索条件时会使用间隙锁。
  2. 间隙锁只用于阻止别的事务插入间隙，他不阻止别的间隙锁锁定相同内容，在一个间隙上，不同的事务可以持有相互冲突的锁。
  3. 间隙锁只在 RR 隔离界别下起作用，可以手动关闭。
临键锁

临键锁是间隙锁和记录锁的结合，临键锁使得一个会话如果在某个索引记录上建立了共享或排它锁，其他会话不能在该索引记录前面的间隙插入数据。

假设某个表的索引包含值10，11，13，20，则其临键锁可能包含以下间隔：

其实只需要记住临键锁锁定的是一个左开右闭的区间即可

临键锁总结
  1. 临键锁是行锁和间隙锁的组合。
  2. 临键锁锁定的是一个左开右闭的区间。
  3. InnoDB RR 隔离级别下，临键锁用来解决幻读。
插入意向锁

insert intention lock， 是在插入新的记录之前通过 INSERT 操作设置的一种间隙锁，该锁以这样一种方式发出插入意图的信号，即如果多个事务要插入的数据在同一间隙内但不是相同的位置，那这些事务就不需要相互等待。比如假设有值为4和7的索引记录。尝试分别插入值5和6的单独事务在获得插入行上的独占锁之前，每个事务都使用插入意图锁锁定4和7之间的间隙，但不会互相阻塞，因为行不冲突。

自增锁

AUTO-INC锁是一种特殊的表级锁，如果一个表中有 AUTO_INCREMENT列，则要插入该表的事务在插入之前会先获取该锁，该锁是表级锁，但不是事务级锁，插入语句执行完后就会立刻释放，不会等待事务提交才释放。自增锁也具有不同的模式，可以使用 innodb_autoinc_lock_mode 选项（0， 1， 2）控制自增锁递增算法，以谋求效率和安全性的要求.

在默认状态下，该选项设置为 1，在该模式下：

  • “批量插入”使用特殊的AUTO-INC表级锁并将其保持到语句结束。 这适用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句。 一次只能执行一条持有AUTO-INC锁的语句。 如果批量插入操作的源表与目标表不同，则在对源表中选择的第一行进行共享锁之后，将对目标表执行AUTO-INC锁。 如果批量插入操作的源和目标是同一表，则在对所有选定行进行共享锁之后，将获取AUTO-INC锁。
  • “简单插入”（预先知道要插入的行数）通过在互斥量（轻型锁）的控制下获得所需数量的自动增量值来避免表级AUTO-INC锁定 仅在分配过程的整个过程中才保留，直到语句完成为止。 除非另一个事务持有AUTO-INC锁，否则不使用表级AUTO-INC锁。 如果另一个事务持有AUTO-INC锁，则“简单插入”将等待AUTO-INC锁，就好像它是“批量插入”一样。
  • 混合模式插入”，如果用户为多行“简单插入”中的某些行 (但不是所有行) 的AUTO_INCREMENT列提供显式值，InnoDB分配的自动增量值会多于要插入的行数。但是，自动分配的所有值都是由最近执行的上一条语句生成的自动增量值连续生成的，因此“多余的”号码就会丢失。

其他模式可以参考 官方文档

关于幻读

网上对幻读的定义各种各样，有人把幻读囊括在不可重复中（比如我们的教材），有人说对某一范围的数据执行删除或插入会导致幻读，有人说只有插入导致的才叫幻读，实际上在 SQL 92 标准里明确定义了什么是幻读：

代码语言：javascript复制
 P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

当一个事务 T1 读到满足某些条件的行集合后，事务 T2 向表中插入了满足这些条件的一行或多行数据，如果 T1 使用相同的条件重复读取，它将得到不同的结果，这叫幻读，而对于删除的情况，92 标准也明确说了这属于不可重复读。

代码语言：javascript复制
P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs  a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
P2(“不可重复读取”)：SQL-Transaction T1读取一行。然后，SQL-Transaction T2修改或删除该行并执行提交。如果T1随后尝试重新读取该行，它可能会收到修改后的值或发现该行已被删除。

在 InnoDB 的文档中，也可以看见：

Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted.

所以对于 MVCC 解决不可重复读中删除情况的问题和修改其实是一样的。

还有一个问题是 InnoDB 的 RR 隔离级别有没有解决幻读的问题，这在文档里面也清晰的写了：解决了，解决方式就是上面的临键锁。

关于丢失修改

上面说到并发导致的问题时提到了两类丢失修改的问题：提交覆盖和回滚覆盖，但似乎在平时说到并发问题时大家只提脏读幻读不可重复读，在 SQL 92 标准里也没有发现对所谓丢失修改问题的描述，事实上，这两种情况都是在读的结果上进行了修改，对于回滚覆盖，InnoDB 的任何隔离级别下他都不会发生，因为回滚到的是针对数据库的上一个已提交的版本，而不是针对该事务的，而对于提交覆盖，我们完全可以在应用程序层面使用诸如 CAS 等技术手段避免这类问题，网上也有使用悲观锁或乐观锁避免的方案，可以参考：事务的隔离级别以及Mysql事务的使用

关于 InnoDB 中的死锁

在 LBCC 那一节也简单说了一下死锁和活锁，这里再详细了解一下 MySQL InnoDB 的死锁问题。先看一个官方文档给出的死锁的例子：

代码语言：javascript复制
CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);

事务一先对数据加 S 锁执行读操作(未提交)：

代码语言：javascript复制
START TRANSACTION;
SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

然后再开启一个事务二尝试删除该记录：

代码语言：javascript复制
START TRANSACTION;
DELETE FROM t WHERE i = 1;

由于删除会对数据加 X 锁，这与事务一加的 S 锁并不相容，所以事务二会等待事务一释放 S 锁，像下面这样：

而等待一段时间后，会提示获取锁超时并终止事务：

代码语言：javascript复制
DELETE FROM t WHERE i = 1
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 51.473s

这样并不存在死锁的问题，但如果在事务二等待的期间，事务一执行了对这条记录的删除操作：

代码语言：javascript复制
DELETE FROM t WHERE i = 1;

就会发生死锁：

代码语言：javascript复制
DELETE FROM t WHERE i = 1
> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 时间: 12.216s

原因是事务一如果想要删除就必须要一个 X 锁，但 X 锁已经被事务二请求了，不能授予事务一，且由于事务二事先请求 X 锁，事务一持有的 S 锁也不能升级为 X 锁，这就会导致死锁，出现这个问题时，InnoDB 会回滚一个小事务（事务的大小由插入、更新或删除的行数决定）并抛出上面的错误来解除死锁。

在高并发系统上，当多个线程等待相同的锁时，死锁检测会导致速度变慢。 所以有时当发生死锁时，禁用死锁检测而依靠innodb_lock_wait_timeout 设置进行事务回滚可能会更有效。 可以使用 innodb_deadlock_detect 配置选项禁用死锁检测。

InnoDB使用自动行级锁定。即使在仅插入或删除单行的事务中，也可能会遇到死锁。这是因为这些操作并不是真正的“原子”操作；它们会自动对插入或删除的行的（可能是多个）索引记录设置锁定。 您可以使用以下技术来处理死锁并减少发生死锁的可能性： 使用 SHOW ENGINE INNODB STATUS命令以确定最近死锁的原因。这可以帮助您调整应用程序以避免死锁。 如果频繁出现死锁警告引，请通过启用innodb_print_all_deadlocks 配置选项来收集更广泛的调试信息 。有关每个死锁的信息，而不仅仅是最新的死锁，都记录在MySQL error log 中。完成调试后，请禁用此选项。 如果事务由于死锁而失败，在任何时候，请重试一遍，死锁并不可怕。 请保持插入或更新事务足够小，避免锁被一个事务长时间占用，以此减少冲突概率。 进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务而使交互式 mysql会话保持打开状态。 如果您使用锁定读取（SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED。 在事务中修改处于多个表或同一表中的不同行集时，每次都要以一致的顺序去执行这些操作。这样事务会形成定义明确的队列而不会导致死锁。例如，将数据库操作组织到应用程序内的函数中，而不是在不同位置编码多个类似的INSERT，UPDATE和DELETE语句序列。 对表中的数据建立合适索引，这样您的查询将会使用更少的索引记录，同样也会使用更少的锁。使用EXPLAIN SELECT以确定MySQL认为哪些索引最适合您的查询。 如果可以，请尽量少的使用锁，以允许 SELECT从一个旧的快照返回数据，不要添加条款FOR UPDATE或LOCK IN SHARE MODE给它。在READ COMMITTED这里使用隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。 如果没有其他办法，可以使用表级锁序列化事务。对事务表（例如InnoDB表）使用LOCK TABLES的正确方法是：SET autocommit = 0（not START TRANSACTION）后跟来开始事务，直到明确提交事务后才对LOCK TABLES调用 UNLOCK TABLES。例如，如果您需要写表 t1和从表中读取数据 t2，则可以执行以下操作：

代码语言：javascript复制
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。 序列化事务的另一种方法是创建一个仅包含一行的辅助“信号量”表。 在访问其他表之前，让每个事务更新该行。 这样，所有事务都以串行方式发生。 注意，在这种情况下，InnoDB即时死锁检测算法也适用，因为序列化锁是行级锁。 对于MySQL表级锁，必须使用超时方法来解决死锁。

参考

MySQL 官方文档 innodb-multi-versioning

美团技术文章 - Innodb中的事务隔离级别和锁的关系

解决死锁之路 - 学习事务与隔离级别

auto innodb mysql row 事务

0 人点赞