mysql 源码：mvcc一致性读的可见性实现

未分类

一、环境准备

CREATE TABLE `students` (
    `id` INT ( 11 ) NOT NULL,
    `name` VARCHAR ( 255 ) DEFAULT '',
    `age` INT ( 11 ) DEFAULT 0,
    `sex` TINYINT ( 1 ) DEFAULT 0,
    `addr` VARCHAR ( 255 ) DEFAULT '',
    PRIMARY KEY ( `id` ),
    KEY `age` ( `age` ),
    KEY `name_age_sex` ( `name`, `age`, `sex` ) USING BTREE 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
Insert into students values(1, 'zhangsan', 21, 1, 'beijing');
Insert into students values(2, 'xiaohong', 22, 2, 'shandong');
Insert into students values(3, 'jack', 23, 1, 'hebei');

二、问题

1. readview 的开启、初始化流程
2. readview 的关闭
3. 如何确定当前事务进行当前读或快照读
4. 聚簇索引、非聚簇索引快照读的异同

三、实验

1. readview 的开启、初始化流程

断点：

• b trx_assign_read_view
• b row0sel.cc:4813（row_search_mvcc)
• b ReadView::prepare

开启方式：

• begin; select * from students;
• start transaction with consistent snapshot;

ReadView *trx_assign_read_view(trx_t *trx)
{
  ...
  } else if (!MVCC::is_view_active(trx->read_view)) {
    trx_sys->mvcc->view_open(trx->read_view, trx);
  }
  return (trx->read_view);
}
dberr_t row_search_mvcc(...) {
...
// row0sel.cc:4813
if (!srv_read_only_mode) {   
      trx_assign_read_view(trx);
    }
...
}

实验：

session A:
begin;
update students set name = "zhangsanA" where id=1;
//select * from students;
session B:
begin;
update students set name = "jackB" where id=3;
commit;
session A:
select * from students;
rollback;

2. readview 的关闭

断点：

• b MVCC::view_close
• b ha_innodb.cc:17833

int ha_innobase::external_lock(THD *thd, int lock_type)
{
...
else if (trx->isolation_level <= TRX_ISO_READ_COMMITTED &&
               MVCC::is_view_active(trx->read_view)) {
      mutex_enter(&trx_sys->mutex);
      trx_sys->mvcc->view_close(trx->read_view, true);
...
}

set session transaction isolation level read committed;

3. 如何确定本次进行当前读、快照读

影响因素：sql(select、select for update)、隔离级别(ru、rc、rr、se)
断点：b row0sel.cc:5193（row_search_mvcc）

show variables like "%isolation%”;

//加锁当前读
  if (prebuilt->select_lock_type != LOCK_NONE) {
    auto row_to_range_relation = row_compare_row_to_range(
        set_also_gap_locks, trx, unique_search, index, clust_index, rec, comp,
        mode, direction, search_tuple, offsets, moves_up, prebuilt);
    //确定锁类型
    if (row_to_range_relation.row_can_be_in_range) {
      if (row_to_range_relation.gap_can_intersect_range) {
        lock_type = LOCK_ORDINARY;
      } else {
        lock_type = LOCK_REC_NOT_GAP;
      }
    } else {
      if (row_to_range_relation.gap_can_intersect_range) {
        lock_type = LOCK_GAP;
      } else {
        err = DB_RECORD_NOT_FOUND;
        goto normal_return;
      }
    }
    //加锁
    err = sel_set_rec_lock(pcur, rec, index, offsets, prebuilt->select_mode,
                           prebuilt->select_lock_type, lock_type, thr, &mtr);
        //当前读
        row_sel_build_committed_vers_for_mysql(
            clust_index, prebuilt, rec, &offsets, &heap, &old_vers,
            need_vrow ? &vrow : nullptr, &mtr);
  } else {
    //无锁 一致性读
    if (trx->isolation_level == TRX_ISO_READ_UNCOMMITTED) {
    //读未提交，直接返回最新记录
    } else if (index == clust_index) {
      //聚簇索引可见性判断
      if (srv_force_recovery < 5 &&
          !lock_clust_rec_cons_read_sees(rec, index, offsets,
                                         trx_get_read_view(trx))) {
        err = row_sel_build_prev_vers_for_mysql(
            trx->read_view, clust_index, prebuilt, rec, &offsets, &heap,
            &old_vers, need_vrow ? &vrow : nullptr, &mtr,
            prebuilt->get_lob_undo());
      }
    } else {
      //二级索引可见性判断
      if (!srv_read_only_mode &&
          !lock_sec_rec_cons_read_sees(rec, index, trx->read_view)) {
        //如果不可见，先尝试索引下推
        switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {
          case ICP_NO_MATCH:
            goto next_rec;
          case ICP_OUT_OF_RANGE:
            err = DB_RECORD_NOT_FOUND;
            goto idx_cond_failed;
          case ICP_MATCH:
            goto requires_clust_rec;
        }
      }
    }
  }

数据更新：

• 聚簇索引：

  • 写 undolog
  • 更新数据
  • 更新该行 trx_id
  • 写redolog
  • 

• 二级索引：

  • 标记当前索引项被删除
  • 插入新的索引项
  • 更新新索引项所在页 trx_id
  • 

session B:
begin;
select * from students;
session A:
begin;
update students set age=23 where id=1;
commit;
session B:
select * from students for update;               //当前读
select * from students;                          //聚簇索引
select * from students where age=23 and name like "%ac%";   //二级索引

四、参考文档

• https://blog.csdn.net/waves___/article/details/105295060
• https://awj.dev/database/mysql/2018/09/16/understanding-mysql-multiversion-concurrency-control.html
• https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html