1. 事务隔离级别（隔离性实现底层）实现原理 = 锁 + MVCC(mvcc处理这两种级别：REPEATABLE-READ和READ-COMMITTED)
  2. SERIALIZABLE需要依赖间隙锁，即解决幻读问题
  3. 原子性，一致性，持久性：undo log（回滚日志） + redo log(重做日志)

一、表级锁&行级锁

  • 表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低。
  • 行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。
  • 显示加锁：select ... lock in share mode强制获取共享锁，select ... for update获取排它锁

二、排他锁和共享锁

  • 排它锁（Exclusive），又称为X锁，写锁。
  • 共享锁（Shared），又称为S锁，读锁。

X和S锁之间有以下的关系： SS可以兼容的，XS、SX、XX之间是互斥的

  • 一个事务对数据对象 O 加了 S 锁，可以对 O 进行读取操作但不能进行更新操作。加锁期间其它事 务能对O 加 S 锁但不能加 X 锁。
  • 一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任 何锁。

示例：注意这里的隔离级别是REPEATABLE-READ,在SERIALIZABLE这种隔离级别中情况并不同。后面会看到

PixPin_2024-11-25_17-27-05

从图中我们可以看出，InnoDB是支持行锁的。其中，第五步失败的原因是被右边第四步的共享锁阻塞了，因为第五步申请的是排他锁，排他锁和共享锁是互斥的，所以第五步会失败。如下面，共享锁是兼容的，所有第五步会成功，第四步会失败。

PixPin_2024-11-25_18-43-28

示例二

PixPin_2024-11-25_17-50-20

​ 从图中可以看出，我们只是修改了where的过滤字段，使用name作为过滤字段，但是使用以后，发现行锁好像不起作用了？我们查询zhangsan和lisi是不同的行数据，发现是失败的。这是因为，Innodb的行锁是加在索引项上面的,是给索引加锁，而不是单纯的在行数据上面加锁，如果过滤条件没有索引的话，InnoDB使用的是表锁而不是行锁!,只有在使用索引作为过滤条件时才会使用行锁,这里的name是没有索引的，所有这里使用的是表锁。如下图所示，给name字段添加了辅助索引，便能够使用行锁了

PixPin_2024-11-25_18-00-17

由于InnoDB的行锁实现是针对索引（主键索引）字段添加的锁，不是针对行记录加的锁，因此虽然访问的是 InnoDB引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行。

PixPin_2024-11-25_18-06-13

即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率更高，此 时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引。

下面使用SERIALIZABLE隔离级别，在SERIALIZABLE隔离级别中，查询操作会自动加上共享锁，写操作会自动加上排他锁

PixPin_2024-11-25_18-20-53

从上面可以看到，我们使用SERIALIZABLE的隔离级别，发现共享锁和共享锁时是兼容的，所以第一步和第二步可以得到结果，由于共享锁和排他锁不兼容，所以第四，第五和第六步失败了(这个解释错了，看下面的解释)。其中，第三和第四步，虽然使用不同的索引，但实际上，InnoDB的所有锁最终是加在主键索引上面的，我们使用name作为索引，他会发生回表在主索引树上查找数据，最终使用的是主键作为索引，所以第三和第四步不会成功,他们都被右边共享锁阻塞住了。再看第五步，刚开始我是很懵逼的，为什么两边都无法执行写操作。后面才知道共享锁是可以叠加的，在开始的两次select操作，两边的会话都加上了共享锁。所以左边的第三和第四步被右边会话的共享锁阻塞，而右边的第五步被左边的共享锁阻塞，所有两边都无法执行写操作。

PixPin_2024-11-25_18-29-59

从这里我们可以知道，第二步能成功是因为在同一个会话中且只有一个共享锁进行加锁，所有第二部的写操作是能够成功的，而第三步被第二步的排他锁阻塞。

  1. InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着只有通过索引条件检索据，InnoDB才使用行级锁，否则InnoDB将使用表锁。
  2. 由于InnoDB的行锁实现是针对索引（主键索引）字段添加的锁，不是针对行记录加的锁，因此虽然访问的是 InnoDB引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行。
  3. 即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引。

三、间隙锁(gap lock)

1、范围查询

在串行化隔离级别下会使用到间隙锁

当我们用范围条件而不是相等条件检索数据（使用next-key lock,next-key lock = record lock + gap lock ），并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁(这些锁称为行锁（record lock）)；对于键值在条件范围内但并不存在的记录，叫做间隙（GAP) ，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(gap lock)。举例来说， 假如 user 表中只有 101 条记录， 其 userid 的值分别是 1,2,...,100,101， 下面的 SQL：

select * from user where userid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁(行锁)，也会对userid 大 于 101（但是这些记录并不存在）的"间隙"加锁（间隙锁），防止其它事务在表的末尾增加数据。

InnoDB使用间隙锁的目的，为了防止幻读，以满足串行化隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 userid 大于 100 的任何记录，那么本事务如果再次执行上述语句， 就会发生幻读。

示例：

PixPin_2024-11-26_14-21-28

在串行化（SERIALIZABLE）隔离级别下，查询操作都会加上共享锁，写操作都会添加排他锁。在这里，我们使用select * from user where id = 3进行范围查询，这时，InnoDB会为添加间隙锁，即大于id>3的数据都会被上锁，即使没有这个数据。

间隙锁的锁的范围是左开右闭的，所以上面的加锁范围如下：(3,4],(4,5],(5,无穷大],加上锁以后，他会对该大于该id后面的写操作都会被间隙锁阻塞。所以上面的第五步会失败。

行级共享读锁 (S Lock)：在你进行 SELECT 时，InnoDB 会为每一行数据加上共享读锁，防止其他事务修改这些行。

间隙锁 (Gap Lock)：如果你的查询是带范围的（如 SELECT * FROM user WHERE id > 3;），InnoDB 可能会在符合条件的行之间的“间隙”上加锁，阻止其他事务在这些间隙插入数据，防止幻读。

同样的道理，在 SERIALIZABLE 隔离级别下，如果执行 SELECT * FROM user;，在事务未提交之前，其他事务对 user 表的数据进行 写操作（如 INSERT、UPDATE、DELETE）是会被间隙锁阻塞的。所以下面的第五步会失败。

PixPin_2024-11-26_14-30-40

存在的问题：我们这里的id是主键，这个是不会重复的,那么使用辅助索引的情况是怎么？辅助索引是允许重复的。

例如使用age作为辅助索引，如果age相等，那么就会比较主键大小，主键按升序排序。这就意味着，如果在不指定id的情况下，一般后面插入的数据排在后边，这就会出现下面这种情况。

例如：我们使用select * from user where age > 16;锁住大于16区间的数据

PixPin_2024-11-26_17-50-09

我们会加锁的区域有：(16,无穷大]

  • 我们首先插入数据的的age = 15，不在加锁区域，能够插入

  • 但是age = 16无法插入，按理说16不应该在这个加锁区间才是。但是就是失败了

    PixPin_2024-11-26_17-48-23

    这是因为虽然新插入的数据age是16,但是他是后插入的，且没有指定id,所以在插入数据排序时，他会排在原本数据jianzhe行的右边，即图中（16，5）的后边，但是他的右边已经被锁住了，所以无法插入成功,所以对于间隙的范围要了解清楚

2、等值查询

在串行化下，如果使用主键或者唯一键作为等值查询的条件，是不会产生幻读现象的，这是因为插入操作会执行失败，删除和更新操作会被共享锁阻塞,所以需要考虑的是辅助索引作为等值查询条件的情况。

在使用辅助索引作为插入条件时，数据要么插入到该数据的左边，要么插入到该数据的右边，所以，要避免幻读的发生，InnoDB就会在该值的左右两边的间隙加上间隙锁，例如：我们插入一条age = 18的数据，insert into user(name,age,sex) values('aaa',18,'M'),假设表数据如下，他就会在（15，18]和（18，19]之间加上间隙锁，防止有插入age等于18的 数据。但是这时，如果我们插入age等于15, 16，17,19的数据也会失败（age = 15如果不指定id的情况下会失败，但是在age = 15且id < 23的时候会成功，19同理）

PixPin_2024-11-26_18-08-58 PixPin_2024-11-26_18-15-40

四、意向共享锁和意向排他锁

意向锁是存储引擎自己加的，并不需要我们手动添加。

  • 意向共享锁（IS锁）：事务计划给记录加行共享锁，事务在给一行记录加共享锁前，必须先取得该表的 IS 锁。
  • 意向排他锁（IX锁）：事务计划给记录加行排他锁，事务在给一行记录加排他锁前，必须先取得该表的 IX 锁。
XIXSIS
XConflictConflictConflictConflict
IXConflict兼容Conflict兼容
SConflictConflict兼容兼容
ISConflict兼容兼容兼容
  1. 意向锁是由InnoDB存储引擎获取行锁之前自己获取的
  2. 意向锁之间都是兼容的，不会产生冲突
  3. 意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）
  4. 意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某 行。

意向锁存在的目的：针对表锁

如果一个事务需要修改很多的数据，涉及多张表，那么如果使用行锁时很容易导致死锁且效率较低，这时使用表锁的效率反而更高，要获取表的共享锁或排他锁，就需要确保两件事：

  1. 这张表没有被其他事务获取过X锁
  2. 这张表里面的数据没有被其他事务获取过行锁x锁

其中，最主要的是第二条，如果表的数据量很大，如有1千万条数据，那么要判断有没有行锁x锁，这个效率是很低的，所以意向锁的目的就是解决这个查找效率问题。

如果意向排他锁已经被获取了，那就说明表中有数据被其他事务添加了行锁x锁，所以当要获取表的X锁时，不需要检查表中的哪些行已经加上了行（X或S）锁，只需要快速检查IX和IS锁即可。

五、MVCC

已提交读和可重复读的底层原理：MVCC(多版本并发控制)，并发读取方式：快照读

InnoDB的两种读取操作：

  1. 锁定读（SX），需要加锁
  2. 非锁定读，即不需要加锁（MVCC提供的快照读，底层为undo log回滚日志）,如select，其中，已提交读和可重复读都依赖于非锁定读，即快照读

MVCC提供的读操作：

  1. 快照读（snapshot read）：读的是记录的可见版本，不用加锁。如select，已提交读和可重复读都依赖于非锁定读，即快照读
  2. 当前读（current read）：读取的是记录的最新版本，并且当前读返回的记录。如insert，delete，update，select...lock in share mode/for update

六、undo log 回滚日志

  • 事务发生错误时回滚日志 rollback

  • 提供MVCC的非锁定读（快照读）

    PixPin_2024-11-28_15-38-36

    如图所示，undo log使用链表将每一次修改的数据的操作串在一起，这些数据存放于缓存中。通过将历史数据串起来实现回滚的效果。

    • DB_TRX_ID是InnoDB为每一个事务提供的唯一的ID值
    • DB_ROLL_PTR是上一个数据存放的地址，如果发生错误，会根据这个地址回滚数据
    • DB_ROW_ID是InnoDB默认提供的索引，如果建表时没有创建索引，InnoDB就会提供这么一个索引

  • 已提交读和可重复读

    1. 已提交读：每次执行select语句的时候都重新生成一次快照 （Read View），但是只有事务已经成功commit以后才能 放入快照中，如果事务还未commit，则不会放入快照中。解决了脏读问题。但是存在不可重复读和幻读的现象，即最新查询的数据内容与前面的的不一致。换句话说：其他事务更新后而且已经提交的数据，可以实时反馈给当前事务的select结果当中。

    2. 可重复读：同一个事务开始的时候生成一个当前事务全局性的快照（Read View），只在事务开始时生成一次快照。这样既能解决脏读，也解决不可重复读的问题。但还是存在幻读的问题。 例如：事务A开启时生成了一个快照，此时事务B插入了一个数据并成功提交，此时如果事务A查询时并不会查询到数据，这是因为数据会从快照中读取，但是，如果事务A对新插入数据进行update操作是会成功的，再一次查询时，会查询到了新的数据，发生了幻读现象。原因是一个事务内所有的操作对于事务自己是可见的，事务进行select操作时不仅会查询快照，还会根据自己的事务ID（DB_TRX_ID）查询修改过的数据（查询时不仅使用快照，还会检查事务自身产生的修改）。这里事务进行update操作是在最新数据中修改的(将新数据的事务ID变为A的事务ID)，而不是在快照中修改的。所以update操作能成功，从而能查询到修改后的数据。

      快照内容读取原则：

      1. 版本未提交无法读取，无法生成快照
      2. 版本已提交，但是在快照创建后提交的，无法读取
      3. 版本已提交，但是在快照创建前提交的，可以读取
      4. 当前事务内自己的更新，可以读到，通过查询当前事务ID号的操作

七、redo log重做日志

redo log是为了保证了数据的持久性和崩溃恢复而设计的,记录的是物理变更信息，即页级的操作，在事务begin开始时就开始记录，不管事务是否提交都会记录下来，在异常发生时，如数据持久化过程中发生掉电，InnoDB会使用redo log恢复到掉电前的时刻，保证数据的完整性。其中，undo log也会写入在redo log中。

八、redo log与undo log的区别

  • Redo Log（重做日志）：

    • redo log记录的是物理变更信息（如写入、更新、删除等），即页级的操作而不记录原始的SQL语句。它可以确保即使系统崩溃，数据库也能根据这些日志进行恢复。它是为了保证事务的持久性（Durability）和崩溃恢复而设计的。
    • 由于它记录的是数据页级别的修改，因此我们有时称它为物理日志。

  • Undo Log（回滚日志）：

    • undo log记录的是逻辑操作(记录了对数据的反向操作)，即记录的是事务对数据的撤销操作（例如，原始数据的值），用于在事务回滚时撤销之前的操作。
    • 例如，在一个UPDATE语句执行时，undo log会记录更新前的数据值。这样如果事务回滚时，InnoDB可以使用undo log来恢复到操作之前的状态。
    • undo log并不是用来恢复数据的物理状态，而是恢复事务的逻辑操作。因此我们把它称为逻辑日志。

小结

  • Redo Log是物理日志，记录数据页的修改，保证持久性和崩溃恢复。它用于记录操作的物理变更（例如写入数据页），并在数据库恢复时重新应用这些变更。
  • Undo Log是逻辑日志，记录撤销操作，确保事务的原子性和一致性。它用于回滚事务时恢复数据的原始状态。
  • 在事务提交时，redo log会先写入磁盘，保证数据的一致性和持久性。然后，事务状态标记为commit，而数据页的刷写通常是异步进行的。

九、InnoDB的Buffer

PixPin_2024-12-03_14-55-55 PixPin_2024-12-03_15-51-52

InnoDB 在处理 SQL 语句时，涉及两个重要的内存结构：InnoDB Logger Buffer 和 InnoDB Buffer Pool Cache。这两个内存结构的作用、分配和使用是为了优化事务处理、数据的缓存和磁盘 I/O 操作。

  1. InnoDB Buffer Pool Cache（缓冲池）

    Buffer Pool 是 InnoDB 存储引擎中的关键内存组件，主要用于存储表数据页、索引页、undo log 页和其他数据。它的作用是提高磁盘 I/O 性能，减少对磁盘的直接访问，将经常使用的数据保存在内存中。Buffer Pool 的大小对数据库性能有重要影响。

Buffer Pool 的使用场景

  • 数据和索引的缓存：在处理 SQL 查询时，数据页和索引页会被加载到 Buffer Pool 中。当执行查询（例如 SELECT）时，InnoDB 会首先检查 Buffer Pool 是否已经缓存了所需的数据和索引。如果数据已经在内存中，就可以直接访问，避免了磁盘 I/O 操作。
  • 事务中的数据修改：当执行更新、删除或插入等写操作时，修改的数据页会被加载到 Buffer Pool 中。这些修改的数据页不会立刻写入磁盘，而是保存在内存中，直到合适的时机（例如 Buffer Pool 满了或者定期刷新）才会被写入磁盘。

  1. InnoDB Logger Buffer（日志缓冲区）

    Logger Buffer 是用来存储事务的 redo log 信息的内存缓冲区。每当一个事务对数据做出修改时，InnoDB 会将修改的操作记录在 redo log 中，这些日志是物理日志，用于恢复数据库的一致性。

Logger Buffer 的使用场景

  • 每次发生数据修改时，InnoDB 会将修改操作记录到 Logger Buffer，并等待最终的写入操作。写入日志的操作通常会被延迟，以减少磁盘 I/O 操作。

  • 在事务提交时，Logger Buffer 会被刷新到磁盘中，确保 redo log 文件的持久性。

    Buffer Pool 和 Logger Buffer 的区别与配合

  • Buffer Pool 主要负责缓存和存储数据库的数据页和索引页。它帮助减少磁盘 I/O，提高查询和写操作的性能。
  • Logger Buffer 主要负责缓存 redo log 记录，确保事务的持久性。在事务提交时，Logger Buffer 中的内容会被刷新到磁盘中的 redo log 文件。

配合使用的流程：

  1. SQL 语句执行时：当执行 SELECT 查询时，InnoDB 会先检查所需的数据页是否在 Buffer Pool 中；如果不在内存中，则会从磁盘加载。如果是修改数据的语句（例如 UPDATE），数据会先在 Buffer Pool 中进行修改。
  2. 事务开始时：当事务开始时，所有对数据的修改都会先记录到 Logger Buffer，然后在提交时将这些日志写入磁盘。
  3. 事务提交时：当事务提交时，Logger Buffer 中的日志会被刷新到磁盘中的 redo log 文件，这样可以确保在系统崩溃时，已经提交的事务能够通过 redo log 恢复。与此同时，修改的数据页也会从 Buffer Pool 刷写到磁盘中，确保数据的持久性。

十、事务提交过程的详细说明

  1. 事务的prepare阶段：

    • 当事务执行COMMIT时，InnoDB首先会在内存中记录该事务的redo log。此时，事务的状态变为PREPARE。
    • 在PREPARE阶段，InnoDB已经将redo log（包括更新的数据页信息）写入到日志缓冲区，但尚未将修改的实际数据写入磁盘。
    • 这时，事务的操作还没有完全持久化到磁盘，InnoDB通过redo log记录了关于数据变更的物理操作（即页级的操作信息），这些信息将用于崩溃恢复时的回滚或重做操作。

  2. 事务的commit阶段：

    • 当事务的redo log成功地刷新到磁盘后，InnoDB会标记该事务为COMMIT状态。
    • 在此时，修改的数据页（实际上是缓冲池中的数据）并没有立即刷新到磁盘。数据在InnoDB的Buffer Pool中仍然存在，InnoDB会异步地将这些数据写入磁盘。这个过程通过flush操作完成，但它是延迟的，具体时机由innodb_flush_log_at_trx_commit等配置项控制。

十一、 崩溃恢复过程

MySQL重启后，InnoDB会执行崩溃恢复。这个过程分为以下几个阶段

  • Redo Log重做（Recovery Phase）：InnoDB会通过读取redo log，恢复所有已提交事务的操作。因为redo log记录的是物理变更，它确保所有已提交的事务能够重做（即应用）到磁盘上的数据中。换句话说，尽管数据页没有及时刷写到磁盘，只要事务已经提交并且redo log被持久化，重启后InnoDB会通过redo log将修改恢复到数据页。

  • Undo Log回滚（Rollback Phase）：InnoDB会回滚所有尚未提交的事务（即PREPARE状态的事务）。这些未提交事务的操作在崩溃前已经被记录到redo log，但是由于它们没有提交，InnoDB会通过undo log撤销这些事务的操作，确保数据库恢复到一致性状态。

    即崩溃在COMMIT之前：InnoDB会通过redo log回滚事务的变更，确保未提交的事务不会影响最终的数据一致性。

    崩溃在COMMIT之后：InnoDB会通过redo log重做已提交事务的变更，恢复数据库数据，即使数据页未写入磁盘。

    undo log 和 redo log 在崩溃恢复和回滚操作中是配合使用的，但它们的作用和使用场景有所不同。简要来说，undo log 用于回滚未提交事务的操作，而 redo log 用于重做已提交事务的操作。它们在恢复过程中各司其职。

现在我们知道，InnoDB存储引擎事务操作的实际上是redo log日志，并不是真正的数据，如今可以根据redo log恢复，所以说MySQL最重要的就是日志。