mysql 快照读MVCC

mysql的读分快照读和当前读

快照读 是指写的同时，读不阻塞，达到并发的作用

这时候的读 是 记录的历史版本，存在于undo里，当然回滚时就的也是这个undo

 当执行一条update语句时，记录本身保持不变，会再insert一条语句的，新记录的回滚指针指向旧的记录，同时新记录有个新的事务id

当新记录对于其他事务不可见时（也就是该事务的begin时间要早于commit update旧记录的时间），延着回滚指针，找到上一个版本的记录，看该记录 是否能让 该事务看到

delete操作不会直接删除掉，只是在record header中置删除标志位，待purge进程将其真正删除

现在有表mytest

 

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB

insert into mytest  values ('', 'aaa', 30);

那么这条记录对应的逻辑记录是这样的，（逻辑记录指人类能识别的，非物理记录，即非二进制记录）

03    　　　　　　变长字段长度偏移量（逆序）
00    　　　　　　null位图
01　　　　　　　　主键id
01　　　　　　　　事务id
00　　　　　　　　回滚id
97 97 97　　　　aaa的asicii
30

 

假设其事务id为1，同时 回滚id 为空

 

mysql> select * from mytest;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | aaa  |  30 |
+----+------+-----+
1 row in set (0.00 sec)

 

执行update操作时，并不是直接在相应记录更新，而是先复制一份新的记录 到undo log,然后 再更新，产生新的事务id并且 其 回滚指针 指向 被复制那条旧的undo log

read view 读视图，每开启一个事务时，就会把当前活跃的事务id 收集起来，根据id排列，最大的id为max trx id,最小的id为min trx id （活跃记录就是还没有commit的记录)

那么事务中的select中读取每一天记录时，要取出其trx id 跟 上面的id做比较

if( record_trx_id < min_trx_id){
    //记录可见
}else {
    if(record_trx_id > max_trx_id ){
        //说明这条记录还没有commit, 这条记录不可见，但可通过其记录中的回滚指针，找到相应记录，看能否可见
    }else{
        if( ! in_array(record_trx_id , array(活跃事务id)){
            //可见
        }else{
            //说明这条记录还没有commit, 这条记录不可见，但可通过其记录中的回滚指针，找到相应记录，看能否可见
        }
    }
}

 

两年前在13号线上看这个规则时，怎么想也想不透，前些日子躺在床上翻手机，一个mysql群里，老叶共享一篇微信文章，讲的是mvcc的内容，里面涉及到读视图这部分，终于恍然大悟

1）假设会话b使用自动提交

会话a　　　　　　　　　　　　　　　　　　　　　　　　　　会话b

　　　　　　　　　　　　　　　　　　　　　　　　　　　　set autocommit=1; //默认自动提交

set autocommit=0;　　　　　　　　　　　　　　　　　　select * from mytest; //这个时候 name还是aaa，是因为此时的事务id为2，aaa这条记录的事务id为1，说明aaa该记录已经提交成功，可见

update mytest set name='bb' where id=1;

id不为原来的1了，现在为3，

同时3事务这条记录的回滚指针指向1　　　　　　　　　　　select * from mytest; //这个时候 name仍是aaa，由于是自动提交，

　　　　　　　　　　　　　　　　　　　　　　　　　　　　//那么默认的begin时间明显早于事务a的commit时间，所以只能看到begin时间前的数据

　　　　　　　　　　　　　　　　　　　　　　　　　　　　//确切来说是 此时新的事务id为4,当分析到aaa该记录时，发现其事务id为3，

commit;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　select * from mytest; //这个时候 name改成bb了,

　　　　　　　　　　　　　　　　　　　　　　　　　　　　//因为默认的begin时间明显晚于事务a的commit时间　

 

 

2）假设会话b放也是手动提交

　　　　　　　　　　　　　　　　　　　　　　　　　　　　set autocommit=0;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　begin;

set autocommit=0;　

begin　　　　　　　　　　　　　　　　　　　　　　　　　select * from mytest; //这个时候 name还是aaa

update mytest set name='bb' where id=1;

commit;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　select * from mytest; //这个时候 name仍是aaa　

　　　　　　

 可以看到会话1在更新id=1这条记录时，会话2没有被阻塞，这里用到的是MVCC 多版本一致性控制 技术　　

在第二个例子里， 事务b的begin 时间 明显早于事务a的commit,那么 事务a的操作 对于 事务b来说是不可见的，也就是说事务b在执行select * from mytest的时候，选取的数据都是 begin之前的数据，之前的数据对于事务b来说，是可见的

 

如果使用快照读，读到的可能是老数据，可以使用当前读，

例如 select * from table where id = 1 for update; 

默认情况下,innodb对于当前读会加next key lock,当 where条件使用了聚焦索引或唯一索引时，会降级为record lock,只锁id为1的这个记录，应该是索引记录？

由于是X lock,排它锁，其他事务不能读，不能写这条记录，倒是可以使用乐观锁，排它锁可理解为悲观锁，先上一把锁，再操作记录，但性能不太好，乐观锁本身没有用到锁，乐观的认为不用锁，只用个version字段来判断

例如

$sql="select * from table where id=1";
$version = $sql->getRow($sql)['versoin'];
$sql ="update table set name='bb' and version=version+1 where id=1 and version=$version";
$affectRows = $db->getAffectRow($sql);
if(!$affectRows){
   再重试几次
}

 假设事务 自动开启

　

假设事务a　　　　　　　　　　　　　　　　　　　　　　　　事务b

取得$version=100;　　　　　　　　　　　　　　　　　　　取得$version=100;

然后更新sql , version变成101　　　　　　　　　　　　　　更新sql,发现更新失败 0 rows affected，再重试

 

如果事务 手动执行

begin　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　begin

取得$version=100　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　取得$version=100　　

update mytest  set version=version+1, name="dd4" where  version=100 and id=1;　　　update mytest  set version=version+1,

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　name="dd4" where  version=100 and id=1;　//被阻塞

 

commit;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　//提交了，但0 rows affected

 

 

每开启一个事务时，都会有一个trx_id

if( record_trx_id < min_trx_id) 可见

else if (record_trx_id > max_trx_id) 不可见

 

 

事务A的三条SQL 结果是一样的

分析:

where id=1 的trx_id为1

执行事务A时的trx_id 为2， 执行事务B时的 trx_id为3

对事务A而言, 能看到trx_id只有2，即自己， oldest_trx_id为1， 且 newest_trx_id也为1

第一条sql: selec * from 表， 里面只有一条记录，且该 记录的 trx_id 为1，  明显小于 2, 即访记录已提交， 其他事务可见

第二条sql: selet * from  表，这个时候, 事务B已经发生了, 且那条记录的 trx_id为3，大于newest_trx_id， 故对事务A来说不可见

第三条sql: select * from  表，虽然事务B已经提交了，但事务A刚begin时，事务b的trx_id大于newest_trx_id, 依然显示id=1的这一条数据

 

 if( ! in_array(record_trx_id , array(活跃事务id)) 可见

 else 不可见

 

事务A  trx_id 2

事务B  trx_id 3

事务C  trx_id 4

事务C中的此时的oldest_trx_id为2， newest_trx_id 4， read view为 2， 3，4 

第一个SQL： select * from 表  , id=1的trx_id 为1， 1小于oldest_trx_id, 对于事务C来说 是已经提交过的，对事务C 是可见了的

第二个SQL:  select * from 表， id=2的trx_id为3， 3 在可见视图里，肯定是未提交 ，对于事务不可见

第三个SQL; select * from 表   ，同上， 此时的 事务看见的视图，还是2，3，4

Read Commited  ，Repeatable read 数据可见性判断

Read Commited 和 Repeatable read 采用相同的数据可见性判断逻辑。
那么怎么在相同的判断逻辑下 分别 实现 RC 和 RR 级别的？

• Read Commited
  在每次语句执行的过程中，都关闭read_view, 重新创建当前的一份新的read_view。
  这样就可以根据当前的全局事务链表创建read_view的事务区间，实现read committed隔离级别。

• Repeatable read
  在repeatable read的隔离级别下，创建事务trx结构的时候，就生成了当前的global read view。
  使用trx_assign_read_view函数创建，一直维持到事务结束，这样就实现了repeatable read隔离级别。

正是因为Read Commited和 Repeatable read的read view 生成方式和时机不同，导致在不同隔离级别下,read committed 总是读最新一份快照数据，而repeatable read 读事务开始时的行数据版本。

链接：https://www.imooc.com/article/details/id/31277

http://roverll.iteye.com/blog/2048079

对于可见性判断，分配聚集索引和二级索引。聚集索引：

 记录的DATA_TRX_ID < view->up_limit_id：在创建read view时，修改该记录的事务已提交，该记录可见

DATA_TRX_ID >= view->low_limit_id：当前事务启动后被修改，该记录不可见

DATA_TRX_ID 位于（view->up_limit_id，view->low_limit_id）：需要在活跃读写事务数组查找trx_id是否存在，如果存在，记录对于当前read view是不可见的。

二级索引：

由于InnoDB的二级索引只保存page最后更新的trx_id，当利用二级索引进行查询的时候，如果page的trx_id小于view->up_limit_id，可以直接判断page的所有记录对于当前view是可见的，否则需要回clustered索引进行判断。

5）如果记录对于view不可见，需要通过记录的DB_ROLL_PTR指针遍历history list构造当前view可见版本数据

6）start transaction和begin语句执行后并没有在innodb层分配事务ID、回滚段、read_view、将事务放到读写事务链表等，这个操作需要第一个SQL语句调用函数trx_start_low来完成，这个需要注意。