Davids原理探究：MySQL-InnoDB-MVCC 多版本控制

野性酷女 2023-02-18 11:50 20阅读 0赞

### 文章目录 ###

*   *  MySQL-InnoDB-MVCC 多版本控制
     *   *  概述
         *  特性
         *  事务快照的创建过程：
         *  undo log
         *  InnoDB行数据隐藏字段
         *   *  SQL示例
         *  当前读/快照读
         *   *  快照读SQL示例
             *  当前读SQL示例

## MySQL-InnoDB-MVCC 多版本控制 ##

关注可以查看更多粉丝专享blog~

### 概述 ###

MVCC指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

> 1.  MySQL的大多数事务型存储引擎实现的其实都不是简单的行级锁。基于提升并发性能的考虑, 它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL, 包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同, 因为MVCC没有一个统一的实现标准。
> 2.  可以认为MVCC是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
> 3.  MVCC的实现方式有多种, 典型的有乐观(optimistic)并发控制 和 悲观(pessimistic)并发控制。
> 4.  MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

### 特性 ###

1.  MVCC是被MySQL中 事务型存储引擎InnoDB 所支持的;
2.  应对高并发事务, MVCC比单纯的加锁更高效;
3.  MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
4.  MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;
5.  各数据库中MVCC实现并不统一
6.  InnoDB的MVCC是通过在每行记录后面保存**两个隐藏的列**来实现的

### 事务快照的创建过程： ###

1.  查看当前所有的未提交并活跃的事务，存储在数组中
2.  选取未提交并活跃的事务中最小的XID，记录在快照的xmin中
3.  选取所有已提交事务中最大的XID，+1后记录在xmax中

read view的快照生成时机，正是因为生成时机的不同，造成了RC和RR级别的不同可见性，RC无法防止不可重复度，RR可以做到不可重复度。

1.  InnoDB中（默认RR级别），事务在begin/start transaction之后的**第一条SELECT语句**读操作后，创建一个快照（read view），并将当前系统中其他活跃事务信息记录下来。
2.  InnoDB中，RC级别事务中每一条SELECT语句都会创建快照（read view）。

### undo log ###

1.  当我们对记录做变更操作时就会产生undo log，Undo记录默认储存在系统表空间中，从MySQL5.6开始开辟了独立的undo表空间。
2.  undo记录中储存的是老版本数据，当一个比较早的事务要读取数据的时候就需要随着undo链寻找满足其可见性的数据，当undo链很长的时候，这个过程是比较耗时的。
3.  INSERT/UPDATE/DELETE，其中INSERT操作在事务提交前只对当前事务可见，产生的undo日志在事务提交之后直接删除；UPDATE/DELETE则需要维护多版本信息，所以这两个操作产生的undo log被归成一类，即update\_undo
4.  insert undo log和update undo log：
    
    1.  insert undo log：事务在insert时产生的undo log，只在回滚时需要，并且事务提交之后可以立即删除。
    2.  update undo log：事务在update/delete时产生的undo log，在回滚/一致性读时需要，当数据库使用的快照中不涉及该日志，才会被purge线程清理掉。

### InnoDB行数据隐藏字段 ###

1.  6字节DB\_TRX\_ID（事务ID）：用于记录本行数据最后一次进行修改操作（INSERT/UPDATE）的事务ID。（DELETE在InnoDB看来也是一次UPDATE操作，不会真正的删除数据）
2.  7字节DB\_ROW\_PTR（回滚指针）：指向undo log record记录，储存着重建改行记录之前的内容。我之前还在想，多线程并发场景下这个undo log record是怎么记录之前的内容的，多份？后来想想InnoDB更新是有排它锁的，只有一个线程能操作。愚蠢-\_-!!!
3.  6字节ROW\_DB\_ID：包含一个随新行插入而单调递增的行ID，当由InnoDB自动产生聚簇索引的时候使用，但是如果表中设置了唯一主键，那么就不会生成该字段了，因为唯一主键已经可以用来生成聚簇索引了。

#### SQL示例 ####

// 测试InnoDB RR级别测试，并发修改同一条数据，先修改的rollback，后修改的commit，先修改的能否正确回滚
    // 事务A
    BEGIN;
    START TRANSACTION;
    select * from `user` where id = 1;
    UPDATE `user` SET Field3 = 3 where id = 1;
    select * from `user` where id = 1;
    SELECT SLEEP(5);// 休眠5s让事务B开始执行
    ROLLBACK;
    // 事务A信息
    [SQL]BEGIN;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    START TRANSACTION;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    select * from `user` where id = 1;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    UPDATE `user` SET Field3 = 3 where id = 1;
    受影响的行: 1
    时间: 0.001s
    
    [SQL]
    select * from `user` where id = 1;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    SELECT SLEEP(5);
    受影响的行: 0
    时间: 5.000s
    
    [SQL]
    ROLLBACK;
    受影响的行: 0
    时间: 0.002s
    
    // 事务B
    BEGIN;
    START TRANSACTION;
    select * from `user` where id = 1;
    UPDATE `user` SET Field2 = 2 where id = 1;
    select * from `user` where id = 1;
    COMMIT;
    // 事务B信息
    [SQL]BEGIN;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    START TRANSACTION;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    select * from `user` where id = 1;
    受影响的行: 0
    时间: 0.000s
    
    // 此时事务A执行UPDATE的事务并未commit/rollback所以排它锁阻塞
    [SQL]
    UPDATE `user` SET Field2 = 2 where id = 1;
    受影响的行: 0
    时间: 3.016s
    
    [SQL]
    select * from `user` where id = 1;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    COMMIT;
    受影响的行: 0
    时间: 0.002s

在上述过程中undo log状态如图所示  
执行事务A的时候：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Rhdmlkc18_size_16_color_FFFFFF_t_70]  
执行事务B的时候：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Rhdmlkc18_size_16_color_FFFFFF_t_70 1]  
行数据的DB\_TRX\_ID和RB\_ROLL\_PER是最新的事务B数据，此时回滚指针指向事务B快照读的undo log的信息，事务B的快照读undo log信息的回滚指针指向事务A的undo log信息，此时形成了类似于链表形式的结构，不管有多少事务同时操作一行数据，那么任一事务需要rollback的时候都可以找到当时修改之前的数据并进行回滚操作。具体最终数据是多少，取决于最后commit的事务修改的数据。

### 当前读/快照读 ###

MySQL的InnoDB默认是RR隔离级别，是通过“行排它锁 + MVCC”一起实现的，不仅可以保证可重复读，还可以防止**部分幻读**。

1.  事务B在事务A执行过程中，插入一条数据并提交，事务A再次查询，虽然通过快照读获取了undo log里面的旧记录（防止了幻读），但是事务A中执行update/delete都是可以成功的，并没有真正意义上的防止。
2.  因为InnoDB中的操作分为当前读（current read）/快照读（snapshot read）。
3.  当前读：
    
    1.  select … lock in share mode;
    2.  select … for update;
    3.  insert
    4.  update
    5.  delete
4.  快照读：普通的select，不包含当前读中的select。

在RR级别下，快照读是通过MVCC（并发多版本控制）和undo log来实现的；当前读是通过record lock（记录锁）和gap lock（间隙锁）来实现的。所以快照读场景下并没有真正的防止幻读，当前读场景下既支持可重复度也可以防止幻读。

#### 快照读SQL示例 ####

// 测试InnoDB RR级别测试，快照读场景下是否能真正的防止幻读，事务A在快照读场景下能否修改事务B新增的数据
    // 数据库当前有6条数据
    // 事务A快照读
    BEGIN;
    START TRANSACTION;
    select * from `user`;
    SELECT SLEEP(5);
    select * from `user`;
    UPDATE `user` set sex = 2;
    select * from `user`;
    COMMIT;
    
    // 事务A信息
    [SQL]BEGIN;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    START TRANSACTION;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    SELECT SLEEP(5);
    受影响的行: 0
    时间: 5.001s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    UPDATE `user` set sex = 2;
    受影响的行: 7 // 这里休眠5s之后事务B已经提交了，但是事务A update的时候影响行数是7行，说明并没有真正防止幻读
    时间: 0.000s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    COMMIT;
    受影响的行: 0
    时间: 0.002s
    
    // 事务B
    BEGIN;
    START TRANSACTION;
    select * from `user`;
    INSERT INTO `chat_room`.`user` (`id`, `user_name`, `password`, `sex`) VALUES ('1006', 'name', '000', '0');
    select * from `user`;
    COMMIT;
    
    // 事务B信息
    [SQL]BEGIN;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    START TRANSACTION;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    INSERT INTO `chat_room`.`user` (`id`, `user_name`, `password`, `sex`) VALUES ('1006', 'name', '000', '0');
    受影响的行: 1
    时间: 0.002s // 事务A执行selelct之后休眠了之后才执行update，所以事务B执行insert的时候可以直接获取到锁
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    COMMIT;
    受影响的行: 0
    时间: 0.001s

#### 当前读SQL示例 ####

// 当前读的场景就是加上select ... lock in share mode; select for update这里会产生排它锁，在事务A提交事务之前事务B在执行insert操作的时候需要等待
    // 事务A当前读
    BEGIN;
    START TRANSACTION;
    select * from `user` for UPDATE;
    SELECT SLEEP(5);
    select * from `user`;
    UPDATE `user` set sex = 2;
    select * from `user`;
    COMMIT;
    // 事务A信息
    [SQL]BEGIN;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    START TRANSACTION;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    select * from `user` for UPDATE;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    SELECT SLEEP(5);
    受影响的行: 0
    时间: 5.001s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    UPDATE `user` set sex = 2;
    受影响的行: 6 // 当前读场景下只会影响6行，支持可重复读也可以防止幻读
    时间: 0.000s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    COMMIT;
    受影响的行: 0
    时间: 0.002s
    
    // 事务B
    BEGIN;
    START TRANSACTION;
    select * from `user`;
    INSERT INTO `chat_room`.`user` (`id`, `user_name`, `password`, `sex`) VALUES ('1006', 'name', '000', '0');
    select * from `user`;
    COMMIT;
    
    [SQL]BEGIN;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    START TRANSACTION;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.000s
    
    [SQL]
    INSERT INTO `chat_room`.`user` (`id`, `user_name`, `password`, `sex`) VALUES ('1006', 'name', '000', '0');
    受影响的行: 1
    时间: 3.464s // 因为我们在执行当前读的时候是表锁，所以事务B insert需要等待锁释放，如果将select * from `user` for UPDATE;修改为select * from `user` where id = 1000 for UPDATE;那么无法防止幻读，事务B不会阻塞，事务A还是会update 7条，这里面涉及到共享锁、排它锁、和间隙锁后面可以专门再写篇blog细说
    
    [SQL]
    select * from `user`;
    受影响的行: 0
    时间: 0.001s
    
    [SQL]
    COMMIT;
    受影响的行: 0
    时间: 0.002s

> 参考文献：
> 
> 1.  《高性能MySQ》
> 2.  [MySQL-InnoDB-MVCC多版本并发控制][MySQL-InnoDB-MVCC]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Rhdmlkc18_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200619175953125.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Rhdmlkc18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Rhdmlkc18_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200619180014639.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Rhdmlkc18=,size_16,color_FFFFFF,t_70
[MySQL-InnoDB-MVCC]: https://segmentfault.com/a/1190000012650596