MySQL MVCC(多版本控制)

1. MySQL 中的事务

MySQL 提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster 。另外还有一些第三方存储引擎也支持事务

  • 1. MySQL 中的事务
    • 1.1. 自动提交(AUTOCOMMIT)
    • 1.2. 在事务中混用存储引擎
  • 2. 多版本并发控制(MVCC)
    • 2.1. InnoDB 的MVCC
  • 3. MySQL 中的事务
    • 3.1. 自动提交(AUTOCOMMIT)
    • 3.2. 在事务中混用存储引擎
  • 4. 多版本并发控制(MVCC)
    • 4.1. InnoDB 的MVCC

1.1. 自动提交(AUTOCOMMIT)

MySQL 默认采用自动提交模式。也就是说每个查询都被当作一个事务执行提交操作,可以设置 AUTOCOMMIT变量来启用或者禁止自动提交模式:

# 查询当前的模式
 show variables like 'AUTOCOMMIT'

# 禁用自动提交
SET AUTOCOMMIT = 0;

AUTOCOMMIT=0时,所有的查询都是在一个事务中,直到显示的执行 COMMIT 或者 ROLLBACK AUTOCOMMIT对非事务存储引擎不会有任何影响

1.2. 在事务中混用存储引擎

MySQL 在服务层不管理事务,事务由下层的存储引擎实现,所以在不同的存储引擎中处理同一个事务是不可靠的。 例如: tablea 使用 innodb 引擎, tableb 使用 MyISAM 引擎(不支持事务) 假设以下操作在一个事务中:

insert table_a ...    # 1
insert table_b ...    # 2
update table_a ...    # 3

假设在 执行 # 3 时出现了异常,这时事务要回滚,因为 tableb 并不支持事务,这就导致 tableb 的修改无法回滚,导致违反事务的 一致性 和 原子性

2. 多版本并发控制(MVCC)

MySQL 大部分事务型存储引擎并不是简单的行级锁。基于提升并发行的考虑,它们一般都同时实现了多版本并发控制 MVCC.

2.1. InnoDB 的MVCC

InnoDB 的 MVCC ,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一把保存了行的创建时间,一个保存行的过期时间(或删除时间),当然存储的并不是真正的时间,而是系统版本号。每开始一个事务,系统版本号就会自动递增,事务开始时刻的版本号作为当前事务的版本号,用来和查询到的每行记录的版本号就行比较。

以下是 REPEATABLE READ 的隔离级别下具体操作:

  • SELECT InnoDB 会根据以下两个条件检查每行记录: a. InnoDB 只查询版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版号),这样可以确保事务读取的行,要么是在事务开始前的已经存在的,要么是事务自身插入或者修改过的。 b. 行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。 只有符合上述两个条件的记录,才能返回作为查询结果
  • INSERT InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
  • DELETE InnoDB 为删除的每一行保存当前系统版本号作为行删除标识
  • UPDATE InnoDB 为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识

保存着两个额外的系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行

=======

3. MySQL 中的事务

MySQL 提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster 。另外还有一些第三方存储引擎也支持事务

3.1. 自动提交(AUTOCOMMIT)

MySQL 默认采用自动提交模式。也就是说每个查询都被当作一个事务执行提交操作,可以设置 AUTOCOMMIT变量来启用或者禁止自动提交模式:

# 查询当前的模式
 show variables like 'AUTOCOMMIT'

# 禁用自动提交
SET AUTOCOMMIT = 0;

AUTOCOMMIT=0时,所有的查询都是在一个事务中,直到显示的执行 COMMIT 或者 ROLLBACK AUTOCOMMIT对非事务存储引擎不会有任何影响

3.2. 在事务中混用存储引擎

MySQL 在服务层不管理事务,事务由下层的存储引擎实现,所以在不同的存储引擎中处理同一个事务是不可靠的。 例如: tablea 使用 innodb 引擎, tableb 使用 MyISAM 引擎(不支持事务) 假设以下操作在一个事务中:

insert table_a ...    # 1
insert table_b ...    # 2
update table_a ...    # 3

假设在 执行 # 3 时出现了异常,这时事务要回滚,因为 tableb 并不支持事务,这就导致 tableb 的修改无法回滚,导致违反事务的 一致性 和 原子性

4. 多版本并发控制(MVCC)

MySQL 大部分事务型存储引擎并不是简单的行级锁。基于提升并发行的考虑,它们一般都同时实现了多版本并发控制 MVCC.

4.1. InnoDB 的MVCC

InnoDB 的 MVCC ,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一把保存了行的创建时间,一个保存行的过期时间(或删除时间),当然存储的并不是真正的时间,而是系统版本号。每开始一个事务,系统版本号就会自动递增,事务开始时刻的版本号作为当前事务的版本号,用来和查询到的每行记录的版本号就行比较。

以下是 REPEATABLE READ 的隔离级别下具体操作:

  • SELECT InnoDB 会根据以下两个条件检查每行记录: a. InnoDB 只查询版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版号),这样可以确保事务读取的行,要么是在事务开始前的已经存在的,要么是事务自身插入或者修改过的。 b. 行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。 只有符合上述两个条件的记录,才能返回作为查询结果
  • INSERT InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
  • DELETE InnoDB 为删除的每一行保存当前系统版本号作为行删除标识
  • UPDATE InnoDB 为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识

保存着两个额外的系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行

原文发布于微信公众号 - 全栈布道士(gh_773193545262)

原文发表时间:2017-09-24

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Danny的专栏

PL/SQL和SQLPLUS查询结果不一样——千万别忘记commit !

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

1712
来自专栏Jackson0714

30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

3016
来自专栏武培轩的专栏

MySQL存储引擎

定义 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定...

3654
来自专栏文渊之博

解决session阻塞的问题

简介     对于数据库运维人员来说创建session或者查询时产生问题是常规情况,下面介绍一种很有效且不借助第三方工具的方式来解决类似问题。 最近开始接触运维...

2035
来自专栏喵了个咪的博客空间

原 EMQ百万级MQTT消息服务(ACL鉴权)

虽然EMQ已经搭建起来了,但是投入到业务使用中还面临着一些问题,当然MQTT设计之初也考虑了这一点,比如不是任何一个客户端都能链接到服务器和限制客户端能够对to...

5904
来自专栏CodingToDie

MySQL 事务与 MVCC

MySQL 中的事务 MySQL 提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster 。另外还有一些第三方存储引擎也支持事务 自动提交(AUT...

3679
来自专栏程序员的SOD蜜

获取SqlServer存储过程定义的3种方法

第一种: declare @p_text varchar(max) SELECT @p_text= text FROM syscomments WHERE...

2547
来自专栏野路子程序员

整合用户篇—Oauth2理解与构造简单的系统

4018
来自专栏tiane12

DISCUS X3后台更新缓存失败提示:(1146) TABLE ‘COMMON_MEMBER_ARCHIVE’ DOESN’T EXIST的解决方案\

1914
来自专栏博客园迁移

几种事务的隔离级别,InnoDB如何实现?

隔离性是指,多个用户的并发事务访问同一个数据库时,一个用户的事务不应该被其他用户的事务干扰,多个并发事务之间要相互隔离。

831

扫码关注云+社区

领取腾讯云代金券