锤爆MySQL之事务

MySQL 事务

<1> 事务和ACID特性介绍

介绍
MySQL 事务主要用于**处理操作量大，复杂度高的数据。**例如：在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

ACID特性介绍
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

<2> InnoDB架构分析

以下内容转载，
原文链接：-内存结构和磁盘文件分析

（1）InnoDB的内存结构

Buffer Pool缓冲池

• 1.数据页和索引页
  Page是InnoDB存储的最基本结构，也是InnoDB磁盘管理的最小单位，与数据库相关的所有内容都存在Page结构中。Page分为多种类型其中数据页与索引页是最关键的两种类型。

• 2.插入缓冲（Insert Buffer）
  对于InnoDB插入时由于B+Tree的特性，按照主键顺序插入时效率是最高的。但当一张表存在非唯一非聚簇索引时，数据页还是顺序存放，但是叶子节点就需要离散的访问非聚簇页了，这样会造成插入效率下降。
  为了解决这一问题，MySql设计了插入缓冲，对于非聚簇索引的插入，并不是立即插入到索引中，而是先判断插入的索引是否在缓冲池中，**若存在直接插入，若不存在先放入缓冲池，然后再以一定得频率将缓冲池中的信息与索引叶子节点合并，**这样可以将多个插入操作合并到一个操作中，大大提升了对非聚簇索引的插入性能。

• 3.自适应哈希索引（Adaptive Hash Index）
  InnoDB会根据访问频率和模式，对热点页建立哈希索引，来提高检索效率，InnoDB会监控对表上各个索引页的查询，如果发现建立哈希索引会带来检索效率的提升，则建立哈希索引，所以叫自适应哈希索引。建立自适应哈希索引有一个要求就是对一个页的连续访问模式必须是一样的，也就是说查询条件（where）必须一致，且必须是连续的。

• 4.锁信息（Lock Info）
  存放所有的锁信息。

• 5.数据字典信息（Data Dictionary）
  存放的是表结构，数据库名，表名，字段的数据类型，索引、视图字段信息，存储过程，触发器等信息。

• 6.Rodo Log Buffer 重做日志缓冲
  当缓冲池中的页的版本比磁盘要新时（称作脏页，并不是脏数据的意思，只是这个版本比磁盘要新），数据库需要将新版本的页从缓冲池刷新到磁盘中称作数据落盘。但是如果每一次变化就进行刷新，那么性能开销是非常大的，于是InnoDB采用了Write Ahead Log(WAL)策略，即当事务提交时，**先写重做日志，然后再择时（check point检查点）将脏页写入磁盘。**如果发生宕机导致数据丢失，就通过重做日志进行恢复（这也不能保证万无一失，也有可能数据还没有写入重做日志中，但是写入重做日志的频率肯定远远大于脏页落盘的频率）同样InnoDB为了提高日志写入的效率，重做日志信息会先存放在重做日志缓冲当中，然后再按照一定得频率刷新到日志文件中（日志落盘）。重做日志缓冲不用设定的非常大，因为一般来说每一秒都会进行一次日志落盘。参数innodb_log_buffer_size来控制重做日志缓冲大小，默认8MB。
  注意：日志文件是顺序写入，数据落盘是随机写，所以日志落盘效率大于日志落盘

innodb_flush_log_at_trx_commit是InnoDB调优的一个基础参数，定义了日志落盘的策略。

• 0：事务提交时不进行写入日志操作，。

• 1：数据提交时必须进行一次fsync（操作系统命令，fsync函数同步内存中所有已修改的文件数据到储存设备。即进行了数据落盘）。

• 2：表示提交时之写入OS BUFFER但不进行fsync操作。
  这三个机制里面0写入效率最高，但是由于提交时并没有理解写入磁盘，这样丧失了事务的持久性，所以数据安全最差。1写入效率最低，但是数据安全最好。也只有1才能保证事务的持久性。写入效率 与数据安全都是中等水平。一般建议设成1。

• 7.Double Write 双写
  双写由两部分组成，一部分是内存中2MB的双写缓冲，一部分是磁盘中连续128页加起来大小同样为2MB。在对缓冲池脏页进行刷新时并不是直接写入磁盘而是通过memcpy函数将藏也先复制到内存中的double write buffer，在分两次每次1MB顺序写入共享表空间物理磁盘上，然后没，马上通过fsync函数同步磁盘，避免操作系统缓冲写带来的问题。在完成double write页的写入后，再将double write buffer的页吸入各个表空间文件中。
  如果操作系统在将页吸入磁盘的过程中发生了崩溃，在回复过程中，InnoDB存储引擎可以从共享表空间double write页找到该页的副本将其复制到表空间文件中，再应用重做日志。

（2）Innodb的磁盘文件

innodb磁盘文件分为

1.系统表空间
2.用户表空间
3.frm表元数据文件

• 1.系统表空间（ibdata文件）
  所有的表共享的表空间存放下面的内容

• 数据字典：存放数据库相关信息，也就是 InnoDB 表的元数据

• double write buffer: 双写缓存，防止页锻炼，解决部分写失败

• insert buffer ：内存insert buffer数据，周期性写入表空间，防止意外宕机

• 回滚段

• undo空间

• 2.用户表空间
  所有的表的元数据默认存放在系统表空间中，当my.ini中配置innodb_file_per_table = 1时，表的数据和索引的文件会存放在一个单独的文件中。该文件主要存放下列信息

  • 表的数据和索引
  • 表的结构
  • undo空间

• 3…frm文件
  保存了每个表的元数据，包括表结构的定义等；

重做日志

redo日志，在mysql中默认以ib_logfile0,ib_logfile1名称存在,可以手工修改参数，调节开启几组日志来服务于当前mysql数据库**,mysql采用顺序，循环写方式，每开启一个事务时，会把一些相关信息记录事务日志中(记录对数据文件数据修改的物理位置或叫做偏移量);**
这个系列文件个数由参数innodb_log_files_in_group控制，若设置为4，则命名为ib_logfile0~3。
这些文件的写入是顺序、循环写的，logfile0写完从logfile1继续，logfile3写完则logfile0继续。

redo log只是记录所有innodb表数据的变化。
redo log只是记录正在执行中的dml以及ddl语句。
redo log可以作为异常down机或者介质故障后的数据恢复使用

<3> 原子性，一致性，持久性实现原理

原文链接：InnoDB事务原理分析

事务四个特性实现

1.原子性的实现
通过redolog的持久化以及undolog回滚来保证原子性

• redo log保证提交时的数据落盘
  redo log 通过Force log at commit 机制保证事务提交后redo log日志都已经持久化。innodb_flush_log_at_trx_commit需要默认设置为1才可以保证原子性。详细可以参照上一篇博文。

• undo log保证数据回滚
  

undo log 分为两种，一种是insert undo log,一种是update undo log

insert undo log:
是在 insert 操作中产生的 undo log。 因为 insert 操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以insert undo log 可以在事务提交后直接删除而不需要进行 purge 操作。

update undo log
是 update 或 delete 操作中产生的 undo log。 因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此update undo log 不能在事务提交时 就进行删除，而是将事务提交时放到入 history list 上，等待 purge线程进行最后的删除操作。

示例：

当事务2使用UPDATE语句修改该行数据时，会首先使用排他锁锁定改行，将该行当前的值复制到
undo log中，然后再真正地修改当前行的值，最后填写事务ID，使用回滚指针指向undo log中修改前的行。

每次对记录进行改动，都会记录一条 undo日志 ，每条 undo日志 也都有一个 roll_pointer 属性
（ INSERT 操作对应的 undo日志 没有该属性，因为该记录并没有更早的版本），可以将这些 undo日志
都连起来，对该记录每次更新后，都会将旧值放到一条 undo日志 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为 版本链 ，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo
log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式。

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（ row_id 并不是
必要的，我们创建的表中有主键或者非NULL唯一键时都不会包含 row_id 列）：

**trx_id ：**每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给 trx_id 隐藏列。
**roll_pointer ：**每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然
后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
2.一致性（consistency）实现

过redo log、undo log和Force Log at Commit机制机制来完成

3.隔离性（isolation）实现

InnoDB是由多版本控制机制（MVCC）和锁实现。

事务5种并发异常：回滚丢失、覆盖丢失、脏读、幻读、不可重复读

事务的隔离级别

• 未提交读（read-uncommitted）
  脏读，就是发生在这一隔离级别下
  **脏读：**一个事物读取到其他事物未提交的数据

• 已提交读（read-committed）
  不可重复读，就是发生在这一隔离级别下。
  不可重复读：在一个事务中一条记录，因其他事物修改导致两次读取的结果不一样称为不可重复读

• 可重复读（repeatable-read）
  可重复读示意：repeatable-read
  幻读：一个事物因读取到另一个事物insert,delete的数据，导致同一个条件对一张表的两次检索结果不一致。

注意：大多数数据库，幻读是在serializable级别下解决的。而MySql是在repeatable-read解决的，原理就是之前博文中讲到的间隙锁。

3.串行化（serializable）
该级别禁止并发性操作，默认对所有的操作都加上锁。
MVCC
InnoDB是靠ReadView来保证数据的可见性的
ReadView

• 对于使用 READ UNCOMMITTED 隔离级别的事务来说，直接读取记录的最新版本就好了。
• 对于使用 SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来访问记录。
• ReadView主要是针对RC与RR级别的。

设计 InnoDB 的设计者提出了一个ReadView的概念，这个 ReadView 中主要包含当前系统中还有哪些活跃1的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为m_ids。

这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本（版本链中的版本）是否可
见：

1.如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id，表明生成该版本的事务在生成
ReadView 前已经提交，所以该版本可以被当前事务访问。
指的是已经开始还没有提交的事务，且事务ID生成的时机并不是在事物begin的时候而是在一个事物中第一次对数据增删改操作
2.如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id，表明生成该版本的事务在生成
ReadView 后才生成，所以该版本不可以被当前事务访问。
3.如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间，那就需要判断
一下 trx_id 属性值是不是在 m_ids 列表中：
- 如果在，说明创建 ReadView 时生成该版本的事务还-是活跃的，该版本不可以被访问；
- 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问

ReadView生成时机
readview只会在select操作时产生

-在已提交读（read-committed）级别中，每次select都会生成一个readview，这样也很好的说明了，为什么RC级别下无法重复读
在 **可重复读（repeatable-read）级别中，只有第一次select操作才会生成readview，**所以在一个事物中，针对一条记录的可见性是不变的，所以是可以重复读的。

持久性（durability）
持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

实现
同样是过redo log、undo log和Force Log at Commit机制机制来完成

总结
事务的隔离性由多版本控制机制和锁实现，而原子性，持久性和一致性主要是通过redo log、undo log和Force Log at Commit机制机制来完成的。
redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控
制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化。

注意事项：
开启一个事务后，用户可以使用COMMIT来提交，也可以用ROLLBACK来回滚。其中COMMIT或 者ROLLBACK执行成功之后，数据一定是会被全部保存或者全部回滚到最初状态的，这也体现了事务的原子性。但是也会有很多的异常情况，比如说事务执行中途连接断开，或者是执行COMMIT或者ROLLBACK时发生错误，Server Crash（服务器崩溃）等，此时数据库会自动进行回滚或者重启之后进行恢复。

我们再来总结一下数据库事务的整个流程，如下图所示

事务的相关流程说明：

1.事务进行过程中，每次DML sql语句执行，都会记录undo log和redo log，然后更新数据形成脏
页。
2.然后redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落
盘后相应的redo log就可以删除了。
3.此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进
行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用undo log进
行事务回滚。
4.事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成
功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了
崩溃，则只使用redo log恢复数据。

行锁和死锁原理分析

深入理解MySQL如何加锁，有两个比较重要的作用：

可以根据MySQL的加锁规则，写出不会发生死锁的SQL；
可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

死锁的发生与否，并不在于事务中有多少条SQL语句
【死锁的关键在于】：两个(或以上)的Session【加锁的顺序】不一致。
分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因

如何解决死锁呢？

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

如何避免死锁

1、注意程序的逻辑
根本的原因是程序逻辑的顺序，最常见的是交替更新
Transaction 1: 更新表A -> 更新表B
Transaction 2:更新表B -> 更新表A
Transaction获得两个资源
2、保持事务的轻量
越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
3、提高运行的速度
避免使用子查询，尽量使用主键等等
4、尽量快提交事务，减少持有锁的时间
越早提交事务，锁就越早释放