探究InnoDB可重复读

在RC(Read Committed)和RR(Repeatable Read)两种事务隔离级别下,InnoDB存在两种数据读取方式:

快照读(Snapshot Read)

故名思意,快照读读取的都是快照数据,快照怎么来,在InnoDB引擎下是基于undo log,那undo log又是什么?举例说明,假设有这样一个表:

-- 表结构
CREATE TABLE `innodb_test` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL DEFAULT '0',
    `age` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `idx_age` (`age`)
)
ENGINE=InnoDB;

-- 初始数据
INSERT INTO `innodb_test` (`id`, `name`, `age`) VALUES (1, '貂蝉', 100),(2, '庄周', 120),(3, '项羽', 130);
  • id=1的初始数据行
  • 事务A执行如下语句 UPDATE innodb_test SET name='嬴政', age=90 WHERE id=1; 此时innodb会做如下操作:
    1. 把该行修改前的值Copy到undo log(Copy on write);
    2. 修改当前行的值,填写事务编号,使回滚指针指向undo log中的修改前的行。
  • 事务B执行如下语句 UPDATE innodb_test SET name='甄姬', age=91 WHERE id=1; 此时undo log中有2条记录,它们通过回滚指针相连。

undo log的存在解决了两个问题,一是数据回滚,二是实现了MVCC (Multi-Version Concurrency Control) ,快照读读取的就是undo log中的数据,所以这种读取是不需要加锁的,避免了读写冲突。常见的快照读语句就是最常见的SELECT,比如:

SELECT * FROM innodb_test WHERE id=1;

快照读在RC和RR隔离级别下的表现却是不一样的,为了方便说明,现在将数据还原到初始数据,然后按照下表的顺序操作。

#

事务1

事务2

1

begin;

begin;

2

SELECT * FROM innodb_test WHERE id=1;// 输出1-貂蝉-100

3

UPDATE innodb_test SET name='嬴政', age=90 WHERE id=1;

4

commit;

5

SELECT * FROM innodb_test WHERE id=1;// 输出???

  • RC 输出的是最新提交的结果(1-嬴政-90),RC级别的快照读遵循以下规则:
    • 优先读取当前事务修改的数据,自己修改的,当然可以读到了;
    • 其次读取最新已提交数据。

    会出现前后读取结果不一样的情况,但读取的是最新数据。

  • RR 输出结果和第一次查询是一样的(1-貂蝉-100),RR级别的快照读遵循以下规则:
    • 优先读取当前事务修改的数据,和RC一样;
    • 其次读取小于当前事务id的最新一条已提交数据,此时数据版本已经确定了,后面的快照读取始终读取这个版本。

    通过这样的机制,保证了快照读的可重复读,但读取到的数据很可能已经过期了。

当前读(Current Read)

而当前读,读取的是最新已提交数据,并且都会加行锁,如下语句都会产生当前读:

SELECT balabala LOCK IN SHARE MODE;
SELECT balabala FOR UPDATE;
INSERT balabala;
UPDATE balabala;
DELETE balabala;

当前读需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,第一条语句,对读取记录加S锁(共享锁),其他的操作,都加的是X锁(排它锁)。当前读在RC和RR隔离级别下的表现也是不一样的,为了方便说明,现在将数据还原到初始数据,然后按照下表的顺序操作。

#

事务1

事务2

1

begin;

begin;

2

SELECT * FROM innodb_test WHERE age=120 FOR UPDATE;

3

insert into innodb_test(name, age) values('嬴政', 120);// 此时会发生什么?

  • RC 成功执行,但会造成事务1的幻读,前后两次读取结果不一样。
  • RR 会锁等待,在RR隔离级别下,事务1的sql不仅会对该记录加X锁,还会对上下两个数据间隙加间隙锁,以此确保在数据读取期间,其它事物不会在该间隙内增加数据,从而保证可重复读。

总结

RR隔离级别下,快照读通过undo log来保证可重复读,当前读通过X(S)锁+GAP锁来保证可重复读,但显然快照读和当前读之间无法保证可重复读。本文对可重复读的实现机制做了阐述,关于undo log、锁等知识仅仅从简描述,后面有时间再详细写一下。

版权声明 本博客所有的原创文章,作者皆保留版权。转载必须包含本声明,保持本文完整,并以超链接形式注明作者高爽。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

导出MySQL用户权限

    在对MySQL数据库进行迁移的时候,有时候也需要迁移源数据库内的用户与权限。对于这个迁移我们可以从mysql.user表来获取用户的相关权限来生成相应的...

923
来自专栏蓝天

测试mktime和localtime_r性能及优化方法

1451
来自专栏xiaoheike

mysql二进制文件操作语法(mysql binary log operate statements)

在 mysql 配置文件中配置 log-bin,重启 mysql my.cnf (on Linux/unix) or my.ini (on Windows) ...

1023
来自专栏MYSQL轻松学

一个MySQL死锁的问题分析

两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。一般不需要人为介入,这种方法简单有效,在innodb中,...

3698
来自专栏Albert陈凯

SQL语句各个部分执行顺序

http://www.tuicool.com/articles/fERNv2 下面先给出上面的SQL语句的执行顺序,然后进行讲解: (8)  select (...

2797
来自专栏青青天空树

mysql用户创建及授权

一、 创建用户:  命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';  说明:usern...

631
来自专栏JavaQ

MySQL存储引擎InnoDB和MyISAM对比总结(更新)

本篇内容对MySQL两个主要存储引擎InnoDB和MyISAM进行对比总结。 InnoDB引擎 InnoDB是一个事务型的存储引擎,设计目标是处理大数量数据时提...

3548
来自专栏别先生

根据id查询所有子节点/父节点,mysql 以及ssm前后台处理流程

注意:自己的数据表表名称,切记手动修改,字段名称(特别注意id,parent_id字段名称,不然肯定查询不出来的)。

1213
来自专栏文渊之博

具体问题解决:分离脚本

有时候需要从一个服务器转移数据库到另一个。如果打算一下子分离所有数据库,那么下面的脚本就是有用了。 生成分离脚本: SELECT DISTINCT 'use m...

1908
来自专栏个人随笔

MySQL 视图

数据库视图是虚拟表或逻辑表,它被定义为具有连接的SQL SELECT查询语句。 因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据。 大...

34311

扫码关注云+社区