mysqlshow原理_mysql事务的实现原理

此篇文章算是对mysql事务的一个总结&＃xff0c;基本把mysql事务相关的知识点都涵盖到了&＃xff0c;面试问来问去无非也就是这些&＃xff0c;在了解这些之前我们先对mysql在执行的过程中  有一个整体的认识&＃xff0c;如下图

如上图所示&＃xff0c;MySQL服务器逻辑架构从上往下可以分为三层&＃xff1a;

(1)第一层&＃xff1a;处理客户端连接、授权认证等。

(2)第二层&＃xff1a;服务器层&＃xff0c;负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。

(3)第三层&＃xff1a;存储引擎&＃xff0c;负责MySQL中数据的存储和提取。MySQL中服务器层不管理事务&＃xff0c;事务是由存储引擎实现的。MySQL支持事务的存储引擎有InnoDB、NDB Cluster等&＃xff0c;其中InnoDB的使用最为广泛&＃xff1b;其他存储引擎不支持事务&＃xff0c;如MyIsam、Memory等。

具体过程都在图中有所标注&＃xff0c;大概看看有个认识就可以了。接下来咱们逐一总结

典型的MySQL事务是如下操作的&＃xff1a;start transaction;

……  #一条或多条sql语句

commit;

其中start transaction标识事务开始&＃xff0c;commit提交事务&＃xff0c;将执行结果写入到数据库。如果sql语句执行出现问题&＃xff0c;会调用rollback&＃xff0c;回滚所有已经执行成功的sql语句。当然&＃xff0c;也可以在事务中直接使用rollback语句进行回滚。

自动提交

MySQL中默认采用的是自动提交(autocommit)模式&＃xff0c;如下所示&＃xff1a;mysql> show variables like &＃39;autocommit&＃39;;

&＃43;---------------&＃43;-------&＃43;

| Variable_name | Value |

&＃43;---------------&＃43;-------&＃43;

| autocommit    | ON    |

&＃43;---------------&＃43;-------&＃43;

1 row in set (0.00 sec)

在自动提交模式下&＃xff0c;如果没有start transaction显式地开始一个事务&＃xff0c;那么每个sql语句都会被当做一个事务执行提交操作。

通过如下方式&＃xff0c;可以关闭autocommit&＃xff1b;需要注意的是&＃xff0c;autocommit参数是针对连接的&＃xff0c;在一个连接中修改了参数&＃xff0c;不会对其他连接产生影响。mysql> set autocommit &＃61;0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like &＃39;autocommit&＃39;;

&＃43;---------------&＃43;-------&＃43;

| Variable_name | Value |

&＃43;---------------&＃43;-------&＃43;

| autocommit    | OFF   |

&＃43;---------------&＃43;-------&＃43;

1 row in set (0.00 sec)

如果关闭了autocommit&＃xff0c;则所有的sql语句都在一个事务中&＃xff0c;直到执行了commit或rollback&＃xff0c;该事务结束&＃xff0c;同时开始了另外一个事务。

特殊操作

在MySQL中&＃xff0c;存在一些特殊的命令&＃xff0c;如果在事务中执行了这些命令&＃xff0c;会马上强制执行commit提交事务&＃xff1b;如DDL语句(create table/drop table/alter/table)、lock tables语句等等。

不过&＃xff0c;常用的select、insert、update和delete命令&＃xff0c;都不会强制提交事务。

事务的特点&＃xff1a;ACID

原子性(Atomicity)

「定义」

原子性是指一个事务是一个不可分割的工作单位&＃xff0c;其中的操作要么都做&＃xff0c;要么都不做&＃xff1b;如果事务中一个sql语句执行失败&＃xff0c;则已执行的语句也必须回滚&＃xff0c;数据库退回到事务前的状态。

简单来说&＃xff0c;就是

「实现原理」在说明原子性原理之前&＃xff0c;首先介绍一下MySQL的事务日志。MySQL的日志有很多种&＃xff0c;如二进制日志、错误日志、查询日志、慢查询日志等&＃xff0c;此外InnoDB存储引擎还提供了两种事务日志&＃xff1a;redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性&＃xff1b;undo log则是事务原子性和隔离性实现的基础。

下面说回undo log。实现原子性的关键&＃xff0c;是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚&＃xff0c;靠的是undo log&＃xff1a;当事务对数据库进行修改时&＃xff0c;InnoDB会生成对应的undo log&＃xff1b;如果事务执行失败或调用了rollback&＃xff0c;导致事务需要回滚&＃xff0c;便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志&＃xff0c;它记录的是sql执行相关的信息。当发生回滚时&＃xff0c;InnoDB会根据undo log的内容做与之前相反的工作&＃xff1a;对于每个insert&＃xff0c;回滚时会执行delete&＃xff1b;对于每个delete&＃xff0c;回滚时会执行insert&＃xff1b;对于每个update&＃xff0c;回滚时会执行一个相反的update&＃xff0c;把数据改回去。

以update操作为例&＃xff1a;当事务执行update时&＃xff0c;其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息&＃xff0c;回滚时便可以使用这些信息将数据还原到update之前的状态。

从上图可以了解到数据的变更都伴随着回滚日志的产生&＃xff1a;

(1) 产生了被修改前数据(zhangsan,1000) 的回滚日志

(2) 产生了被修改前数据(zhangsan,0) 的回滚日志

根据上面流程可以得出如下结论&＃xff1a;每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上

所谓的回滚就是根据回滚日志做逆向操作&＃xff0c;比如delete的逆向操作为insert&＃xff0c;insert的逆向操作为delete&＃xff0c;update的逆向为update等。

回滚过程如图

tips&＃xff1a;undo log也可以这么理解当delete一条记录时&＃xff0c;undo log中会记录一条对应的insert记录

当insert一条记录时&＃xff0c;undo log中会记录一条对应的delete记录

当update一条记录时&＃xff0c;它记录一条对应相反的update记录

tips:逻辑日志和物理日志的区别

看记日志的时候 是针对一行记录&＃xff0c;就是逻辑日志 如果是一个数据页&＃xff0c;就是物理日志

持久性(Durability)

「定义」

事务一旦提交&＃xff0c;其所做的修改会永久保存到数据库中&＃xff0c;此时即使系统崩溃修改的数据也不会丢失。

「实现原理:Redo log(WAL  write ahead log)」

先了解一下MySQL的数据存储机制&＃xff0c;MySQL的表数据是存放在磁盘上的&＃xff0c;因此想要存取的时候都要经历磁盘IO,然而即使是使用SSD磁盘IO也是非常消耗性能的。

为此&＃xff0c;为了提升性能InnoDB提供了缓冲池(Buffer Pool)&＃xff0c;Buffer Pool中包含了磁盘数据页的映射&＃xff0c;可以当做缓存来使用&＃xff1a;

读数据&＃xff1a;会首先从缓冲池中读取&＃xff0c;如果缓冲池中没有&＃xff0c;则从磁盘读取再放入缓冲池&＃xff1b;

写数据&＃xff1a;会首先写入缓冲池&＃xff0c;缓冲池中的数据会定期同步到磁盘中(这一过程称为刷脏)&＃xff1b;

上面这种缓冲池的措施虽然在性能方面带来了质的飞跃&＃xff0c;但是它也带来了新的问题&＃xff0c;当MySQL系统宕机&＃xff0c;断电的时候可能会丢数据&＃xff01;&＃xff01;&＃xff01;

因为我们的数据已经提交了&＃xff0c;但此时是在缓冲池里头&＃xff0c;还没来得及在磁盘持久化&＃xff0c;所以我们急需一种机制需要存一下已提交事务的数据&＃xff0c;为恢复数据使用。

于是 redo log就派上用场了。下面看下redo log是什么时候产生的

既然redo log也需要存储&＃xff0c;也涉及磁盘IO为啥还用它&＃xff1f;

(1)刷脏是随机IO&＃xff0c;因为每次修改的数据位置随机&＃xff0c;但写redo log是追加操作&＃xff0c;属于顺序IO。

(2)刷脏是以数据页(Page)为单位的&＃xff0c;MySQL默认页大小是16KB&＃xff0c;一个Page上一个小修改都要整页写入&＃xff1b;而redo log中只包含真正需要写入的部分&＃xff0c;无效IO大大减少。

「redo log与binlog」

我们知道&＃xff0c;在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复&＃xff0c;但二者是有着根本的不同的&＃xff1a;

(1)作用不同&＃xff1a;redo log是用于crash recovery的&＃xff0c;保证MySQL宕机也不会影响持久性&＃xff1b;binlog是用于point-in-time recovery的&＃xff0c;保证服务器可以基于时间点恢复数据&＃xff0c;此外binlog还用于主从复制。

(2)层次不同&＃xff1a;redo log是InnoDB存储引擎实现的&＃xff0c;而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的&＃xff0c;同时支持InnoDB和其他存储引擎。

(3)内容不同&＃xff1a;redo log是物理日志&＃xff0c;内容基于磁盘的Page&＃xff1b;binlog的内容是二进制的&＃xff0c;根据binlog_format参数的不同&＃xff0c;可能基于sql语句、基于数据本身或者二者的混合。

(4)写入时机不同&＃xff1a;binlog在事务提交时写入&＃xff1b;redo log的写入时机相对多元&＃xff1a;

前面曾提到&＃xff1a;当事务提交时会调用fsync对redo log进行刷盘&＃xff1b;这是默认情况下的策略&＃xff0c;修改innodb_flush_log_at_trx_commit参数可以改变该策略&＃xff0c;但事务的持久性将无法保证。

除了事务提交时&＃xff0c;还有其他刷盘时机&＃xff1a;如master thread每秒刷盘一次redo log等&＃xff0c;这样的好处是不一定要等到commit时刷盘&＃xff0c;commit速度大大加快。

隔离性(Isolation)

「定义」

与原子性、持久性侧重于研究事务本身不同&＃xff0c;隔离性研究的是不同事务之间的相互影响。隔离性是指&＃xff0c;事务内部的操作与其他事务是隔离的&＃xff0c;并发执行的各个事务之间不能互相干扰。严格的隔离性&＃xff0c;对应了事务隔离级别中的Serializable (可串行化)&＃xff0c;但实际应用中出于性能方面的考虑很少会使用可串行化。

「实现原理」

隔离性追求的是并发情形下事务之间互不干扰。简单起见&＃xff0c;我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)&＃xff0c;那么隔离性的探讨&＃xff0c;主要可以分为两个方面&＃xff1a;

(一个事务)写操作对(另一个事务)写操作的影响&＃xff1a;锁机制保证隔离性

(一个事务)写操作对(另一个事务)读操作的影响&＃xff1a;MVCC保证隔离性

「脏读、不可重复读和幻读」

首先来看并发情况下&＃xff0c;读操作可能存在的三类问题&＃xff1a;脏读&＃xff1a;当前事务(A)中可以读到其他事务(B)未提交的数据(脏数据)&＃xff0c;这种现象是脏读。举例如下(以账户余额表为例)

不可重复读&＃xff1a;在事务A中先后两次读取同一个数据&＃xff0c;两次读取的结果不一样&＃xff0c;这种现象称为不可重复读。脏读与不可重复读的区别在于&＃xff1a;前者读到的是其他事务未提交的数据&＃xff0c;后者读到的是其他事务已提交的数据。举例如下&＃xff1a;

幻读&＃xff1a;在事务A中按照某个条件先后两次查询数据库&＃xff0c;两次查询结果的条数不同&＃xff0c;这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为&＃xff1a;前者是数据变了&＃xff0c;后者是数据的行数变了。举例如下

「事务隔离级别」

在实际应用中&＃xff0c;读未提交在并发时会导致很多问题&＃xff0c;而性能相对于其他隔离级别提高却很有限&＃xff0c;因此使用较少。可串行化强制事务串行&＃xff0c;并发效率很低&＃xff0c;只有当对数据一致性要求极高且可以接受没有并发时使用&＃xff0c;因此使用也较少。因此在大多数数据库系统中&＃xff0c;默认的隔离级别是读已提交(如Oracle)或可重复读(后文简称RR)。

可以通过如下两个命令分别查看隔离级别&＃xff1a;select &＃64;&＃64;tx_isolation;

&＃43;-----------------&＃43;

| &＃64;&＃64;tx_isolation  |

&＃43;-----------------&＃43;

| REPEATABLE-READ |

&＃43;-----------------&＃43;

1 row in set (0.00 sec)

「MVCC」

RR解决脏读、不可重复读、幻读等问题&＃xff0c;使用的是MVCC&＃xff1a;MVCC全称Multi-Version Concurrency Control&＃xff0c;即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点&＃xff1a;在同一时刻&＃xff0c;不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻&＃xff0c;事务A和事务C可以读取到不同版本的数据。

MVCC最大的优点是读不加锁&＃xff0c;因此读写不冲突&＃xff0c;并发性能好。InnoDB实现MVCC&＃xff0c;多个版本的数据可以共存&＃xff0c;主要是依靠数据的隐藏列(也可以称之为标记位)和undo log。其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等&＃xff1b;当读取数据时&＃xff0c;MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log&＃xff0c;从而实现MVCC&＃xff1b;隐藏列的详细格式不再展开。

下面结合前文提到的几个问题分别说明「脏读」

当事务A在T3时间节点读取zhangsan的余额时&＃xff0c;会发现数据已被其他事务修改&＃xff0c;且状态为未提交。此时事务A读取最新数据后&＃xff0c;根据数据的undo log执行回滚操作&＃xff0c;得到事务B修改前的数据&＃xff0c;从而避免了脏读。

「不可重复读」

当事务A在T2节点第一次读取数据时&＃xff0c;会记录该数据的版本号(数据的版本号是以row为单位记录的)&＃xff0c;假设版本号为1&＃xff1b;当事务B提交时&＃xff0c;该行记录的版本号增加&＃xff0c;假设版本号为2&＃xff1b;当事务A在T5再一次读取数据时&＃xff0c;发现数据的版本号(2)大于第一次读取时记录的版本号(1)&＃xff0c;因此会根据undo log执行回滚操作&＃xff0c;得到版本号为1时的数据&＃xff0c;从而实现了可重复读。

「幻读」

InnoDB实现的RR通过next-key lock机制避免了幻读现象。

next-key lock是行锁的一种&＃xff0c;实现相当于record lock(记录锁) &＃43; gap lock(间隙锁)&＃xff1b;其特点是不仅会锁住记录本身(record lock的功能)&＃xff0c;还会锁定一个范围(gap lock的功能)。当然&＃xff0c;这里我们讨论的是不加锁读&＃xff1a;此时的next-key lock并不是真的加锁&＃xff0c;只是为读取的数据增加了标记(标记内容包括数据的版本号等)&＃xff1b;准确起见姑且称之为类next-key lock机制。还是以前面的例子来说明&＃xff1a;

当事务A在T2节点第一次读取0

总结

概括来说&＃xff0c;InnoDB实现的RR&＃xff0c;通过锁机制、数据的隐藏列、undo log和类next-key lock&＃xff0c;实现了一定程度的隔离性&＃xff0c;可以满足大多数场景的需要。不过需要说明的是&＃xff0c;RR虽然避免了幻读问题&＃xff0c;但是毕竟不是Serializable&＃xff0c;不能保证完全的隔离&＃xff0c;下面是一个例子&＃xff0c;大家可以自己验证一下。

一致性

基本概念

一致性是指事务执行结束后&＃xff0c;数据库的完整性约束没有被破坏&＃xff0c;事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于&＃xff1a;实体完整性(如行的主键存在且唯一)、列完整性(如字段的类型、大小、长度要符合要求)、外键约束、用户自定义完整性(如转账前后&＃xff0c;两个账户余额的和应该不变)。

实现

可以说&＃xff0c;一致性是事务追求的最终目标&＃xff1a;前面提到的原子性、持久性和隔离性&＃xff0c;都是为了保证数据库状态的一致性。此外&＃xff0c;除了数据库层面的保障&＃xff0c;一致性的实现也需要应用层面进行保障。

实现一致性的措施包括&＃xff1a;保证原子性、持久性和隔离性&＃xff0c;如果这些特性无法保证&＃xff0c;事务的一致性也无法保证

数据库本身提供保障&＃xff0c;例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等

应用层面进行保障&＃xff0c;例如如果转账操作只扣除转账者的余额&＃xff0c;而没有增加接收者的余额&＃xff0c;无论数据库实现的多么完美&＃xff0c;也无法保证状态的一致