ACID特性：

• 原子性（atomicity）：整个事务是不可分割的工作单位。
• 一致性（consistency）：事务将数据库从一种状态转变为下一种一致的状态。
• 隔离性（isolation）：还有其他称呼，如并发控制(concurrency control)、可串行化(serializability)、锁(locking)等。隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常使用锁来实现。
• 持久性（durability）：事务一旦提交，其结果就是永久性的。

1.认识事务

①概述

事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

InnoDB默认的事务隔离级别为READ REPEATABLE，完全遵循和满足事务的ACID特性。

②分类

从事务理论的角度来说，可以把事务分为以下几种类型：

• 扁平事务（Flat Transactions）：最简单的一种，实际生产环境中使用最为频繁的事务。所有操作处于同一层次，其间的操作是原子的，要么都执行，要么都回滚。主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。

• 带有保存点的扁平事务（Flat Transactions With Savepoints）：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销也太大。

  保存点：用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。

• 链事务（Chained Transactions）：可视为保存点模式的一种变种。带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的，而非持久的。

  链事务思想：在提交一个事务时，是否不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就像在一个事务中进行的一样。

  带有保存点的扁平事务能回滚到任意正确的保存点，链事务中的回滚仅限于当前事务，即只能恢复到最近一个保存点。

• 嵌套事务（Nested Transactions）：是一个层次结构框架。由一个顶层事务控制着各个层次的事务。

子事务可以提交也可以回滚，但是它的提交操作并不马上生效，除非其父事务已经提交。

树中的任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留A、C、I特性，不具有D的特性。

• 分布式事务（Distributed Transactions）：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

  如：持卡人从招商银行储蓄卡转账10000元到工商银行储蓄卡。

  将ATM机视为节点A，招商银行后台数据库视为节点B，工商银行后台数据库视为节点C。

  1）节点A发出转账命令。

  2）节点B执行储蓄卡中的余额值减去10000。

  3）节点C执行储蓄卡中的余额值加上10000。

  4）几点A通知用户操作完成或者节点A通知用户操作失败。

  这里需要使用分布式事务，因为节点A不能通过调用一台数据库就完成任务。其需要访问网络中两个节点的数据库，而在每个节点的数据库执行的事务操作又都是扁平的。对于分布式事务，同样需要满足ACID特性，要么都发生，要么都失效。

InnoDB存储引擎支持扁平事务、带有保存点的事务、链事务、分布式事务。原生不支持嵌套事务。

2.事务的实现

事务隔离性由锁来实现。

原子性、持久性通过数据库的 redo log来完成。

一致性通过数据库的 undo log 来完成。

• redo log：

  物理日志，记录的是页的物理修改操作，用来恢复提交事务修改的页操作。

  基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。

• undo log：

  逻辑日志，根据每行记录进行记录，用来回滚行记录到某个特定版本。

  需要进行随机读写的。

①redo

1）基本概念

由两部分组成：

• 内存中的重做日志缓冲(redo log buffer)，其是易失的；
• 重做日志文件(redo log file)，其是持久的

InnoDB通过Force Log at Commit机制实现事务的持久性，即当事务提交（commit）时，必须先将事务的所有日志写入到重做日志文件进行持久化，待事务的commit操作完成才算完成。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB都需要调用一次fsync操作（由于重做日志文件打开并没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存）。

如果为了数据库性能，可以手工设置非持久性的情况发生，即当提交事务时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。当数据库发生宕机时，由于部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。

二进制日志和重做日志的区别：

• 二进制日志：MySQL上层产生的，不仅仅针对InnoDB，任何存储引擎对于数据库的更改都会产生二进制日志。逻辑日志，记录的是对应的SQL语句。只在事务提交完成后进行一次写入。

• 重做日志：InnoDB层产生的。物理格式日志，记录的是对于每个页的修改。在事务进行中不断地被写入，并不是随事务提交的顺序进行写入的。

2）log block

InnoDB中，重做日志都是以512字节进行存储的。意味着重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo log block），每块大小为512字节（和磁盘扇区大小一样）。若一个页中产生的重做日志数量大于512字节，那么需要分割为多个重做日志块进行存储。由于块的大小和扇区一样，因此重做日志的写入可以保证原子性，不需要doublewrite技术。

3）log group

重做日志组，其中有多个重做日志文件。InnoDB实际只有一个log group。

重做日志文件中存储的就是之前在log buffer中保存的log block，因此其也是根据块的方式进行物理存储的管理。

InnoDB运行过程中，log buffer根据一定的规则将内存中的log block刷新到磁盘：

• 事务提交时
• 当log buffer中有一半的内存空间已经被使用时
• log checkpoint时

对于log block的写入追加（append）在redo log file的最后部分，当一个redo log file被写满时，会接着写入下一个redo log file。

redo log file除了保存log buffer刷新到磁盘的log block，还保存了一些其他信息，这些信息一共占用2KB大小，即每个redo log file的前2KB的部分不保存log block的信息。log group中的第一个redo log file，其前2KB的部分保存4个512字节大小的块，其中存放的内容：

• log file header：512字节
• checkpoint1：512字节
• 空：512字节
• checkpoint2：512字节

log group中其余的redo log file仅保留这些空间，但不保存上述信息。

4）LSN（Log Sequence Number，日志序列号）

InnoDB中，LSN占用8字节，并且单调递增。LSN表示的含义有：

• 重做日志写入的总量：例如当重做日志的LSN为1000，有一个事务T1写入了100字节的重做日志，那么LSN就变成了1100。
• checkpoint的位置
• 页的版本

LSN不仅记录在重做日志中，还存在于每个页中。在每个页的头部，有一个值FIL_PAGE_LSN，记录了该页的LSN。在页中，LSN表示该页最后刷新时LSN的大小。

因为重做日志记录的是每个页的日志，因此页中的LSN用来判断页是否需要进行恢复操作。

页中的LSN小于重做日志中的LSN，需要进行恢复操作，大于等于重做日志中的LSN，不需要进行重做。

5）事务提交

事务提交时，InnoDB会进行两个阶段的操作：

• 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
• 调用fsync将确保日志都从重做日志缓冲写入磁盘。

②undo

1）基本概念

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作，但是事务有时还需要进行回滚操作，这时就需要undo。

在对数据库进行修改时，InnoDB不但会产生redo，还会产生一定量的undo。

undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment），位于共享表空间内。

误解：undo用于将数据库物理地恢复到执行语句或事务之前的样子。

实际：undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后和可能和回滚之前大不相同。这是因为多用户并发系统中，一个事务在修改当前一个页中某几条记录时，还有别的事务在对同一个页中另几条记录进行修改。因此不能将一个页回滚到事务开始的样子，因为这样会影响其他事务在进行的工作。

例如：执行了一个insert 10W条记录的事务，这个事务会导致分配一个新的段，即表空间会增大。在执行ROLLBACK时，会将插入的事务进行回滚，但表空间的大小并不会因此收缩。

InnoDB回滚时，实际上做的是与先前相反的工作。对于每个insert，InnoDB会完成一个delete；对弈每个delete，InnoDB会执行一个insert；对于每个update，InnoDB会执行一个相反的update将修改前的行放回去。

InnoDB中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo log会产生redo log，因为undolog也需要持久性的保护。

2）undo 存储管理

InnoDB有rollback segment，每个回滚段中记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。

InnoDB 1.1版本之前只有一个rollback segment，因此支持同时在线的事务限制为1024。从1.1版本开始，InnoDB支持最大128个rollback segment。

事务提交时，InnoDB会做以下事情：

• 将undo log放入列表中，以供之后的purge操作
• 判断undo log所在的页是否可以重用，若可以，分配给下个事务使用

事务提交后并不能马上删除undo log及undolog所在的页。因为可能还有其他事务需要通过undo log来得到行记录之前的版本。所以事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。

InnoDB设计中对undo页可以进行重用。事务提交时，首先将undo log放入链表中，然后判断undo页的使用空间是否小于3/4，若是则表示该undo页可以被重用，之后新的undo log记录在当前undo log的后面。由于存放undo log的列表是以记录进行组织的，而undo页可能存放着不同事务的undo log，因此purge操作需要涉及磁盘的离散读取操作，是一个比较缓慢的过程。

3）undo log格式

InnoDB中，undo log分为：

• insert undo log：在insert操作中产生的undo log。因为insert操作的记录只对事务本身可见，对其他事务不可见（事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。

• update undo log：记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

• insert操作，产生一个类型为TRX_UNDO_INSERT_REC的undo log。
• delete操作，并不直接删除记录，而只是将记录标记为已删除，也就是将记录的delete flag设置为1.而记录最终的删除是在purge操作中完成的。产生一个类型为TRX_UNDO_DEL_MARK_REC的undo log。
• update主键值的操作，分两步完成：首先将原主键记录标记为已删除，因此需要产生一个类型为TRX_UNDO_DEL_MARK_REC的undo log，之后插入一条新的记录，因此需要产生一个类型为TRX_UNDO_INSERT_REC的undo log。
• update一个非主键值操作，产生一个类型为TRX_UNDO_UPD_EXIST_REC的undo log。

③purge

delete和update操作可能并不直接删除原有的数据。这样设计是因为InnoDB支持MVCC，所以记录不能在事务提交时立即进行处理。purge用于最终完成delete和update操作，而实际执行的操作为delete操作，清理之前行记录的版本。

InnoDB的undo log设计是这样的：一个页上允许多个事务的undo log存在。虽然这不代表事务在全局过程中提交的顺序，但是后面的事务产生的undo log总在最后。此外，InnoDB还有一个history列表，它根据事务提交的顺序，将undo log进行链接。

在执行purge的过程中，InnoDB首先从history list中找到第一个需要被清理的记录，清理之后InnoDB会在undo log所在的页中继续寻找是否存在可以被清楚的记录，直到发现不能清理的记录。若undo page中所有的记录都被清理了，该undo page可以被重用。

InnoDB这种先从history list中找undo log，然后再从undo page中找undo log的设计模式是为了避免大量的随机读取操作，从而提高purge的效率。

④group commit

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入磁盘。

3.事务控制语句

事务控制语句：

• start transaction | begin ： 显示地开启一个事务。

  在存储过程中，MySQL数据库的分析器会自动将begin识别为begin...end，因此存储过程中只能用start transaction语句来开启一个事务。

• commit | commit work ：提交事务，并使得已对数据库做的所有修改成为永久性的。

  commit work用来控制事务结束后的行为是chain还是release的。如果是chain方式，那么事务就变成了链事务。

• rollback | rollback work：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

  同commit work。

• savepoint identifier： savepoint 允许在事务中创建一个保存点，一个事务中可以有多个savepoint 。

• release savepoint identifier：删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常。

• rollback to [ savepoint ] identifier：和savepoint identifier一起使用。可以把事务回滚到标记点。

• set transaction：用来设置事务的隔离级别。

注意：开启了一个事务后，一条语句失败并抛出异常时，并不会导致先前已经执行的语句自动回滚。所有的执行都会得到保留，必须由用户自己来决定是否对其进行提交或回滚的操作。（提交：之前执行保留，回滚：之前执行不保留）

4.隐式提交的SQL语句

一下这些SQL语句会产生一个隐式的提交操作，即执行完这些语句后，会有一个隐式的commit操作。

• DDL语句：alter database...upgrade data directory name，alter event，alter procedure，alter table，alter view，create database，create event，create index，create procedure，create table，create trigger，create view，drop database，drop event，drop index，drop procedure，drop table，drop trigger，drop view，rename table，truncate table。
• 用来隐式地修改MySQL架构的操作：create user，drop user，grant，rename user，revoke，set password。
• 管理语句：analyze table，cache index，check table，load index into cache，optimize table，repair table。

注意：truncate table是不能被回滚的

5.事务的隔离级别

ISO和 ANIS SQL标准定制了四种事务隔离级别的标准，但是很少有数据库厂商遵循这些标准。比如Oracle数据库就不支持read uncommitted和repeatable read的事务隔离级别。

SQL标准定义的四个隔离级别是：read uncommitted、read committed、repeatable read、serializable。SQL和SQL2标准的默认事务隔离级别是serializable。

InnoDB事务隔离级别说明：

• read uncommitted：会读取到脏数据。

• read committed：不加锁的时候，能读取到别人提交的修改的数据，有不可重复读问题；使用非锁定的一致性读，总是读取被锁定行的最新一份快照数据（读取不加锁的时候，不会被update语句的排他锁阻塞）。加锁的时候（如for update）采用Record Lock，单个行记录上的锁。

• repeatable read：默认。使用非锁定的一致性读，总是读取事务开始时的行数据版本，所以不加锁的时候也不会有不可重复读问题。在此事务隔离级别下，加锁的时候（如for update）使用Next-Key-Lock锁的算法，当查询条件为非唯一辅助索引加锁时，会加行锁和间隙锁，避免相同辅助索引的行插入，出现幻读。在此隔离级别下使用锁已经能完全保证事务的隔离性要求，即达到SQL标准的serializable隔离级别。

  不加锁出现幻读案例：

  create table z (a int, b int, primary key(a), key(b));
  insert into z(a,b) values (1,1),(3,1),(5,3),(7,6),(10,8);

  事务A：

  START TRANSACTION;
  SELECT * FROM z WHERE b=3;
  SELECT * FROM z WHERE b=5;

  事务B：

  INSERT INTO z(a,b) VALUES (9,3);

  事务A：

  UPDATE z SET b=5 WHERE b=3;//因为update时会加排他锁，所以不走版本控制，更新后的数据是本事务版本的数据，就会被查到了。
  SELECT * FROM z WHERE b=5;//出现幻读，若事务A第一个查询加上锁，就不会

• serializable：InnoDB会对每个select语句后自动加上lock in share mode，即为每个读取操作加上一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

隔离级别越低，事务请求的锁越少或保持锁的时间越短。这也是为什么大多数数据库默认的事务隔离级别是read committed。

大多数用户质疑serializable隔离级别带来的性能问题，但是根据Jim Gray在《Transaction Processing》一书中指出，两者的开销几乎是一样的，甚至serializable可能更优。因此InnoDB中选择repeatable read的事务隔离级别并不会有任何性能损失。同样地，即使使用read committed的隔离级别，用户也不会得到性能的大幅提升。

不可重复读和幻读的区别：

• 不可重复读：第一次读取的某行数据，第二次再读取时可能不一样。（别的事务提交了update或delete）。

• 幻读：第一次读取的某行数据，第二次再读取时是一样的，但是几行数据中间可能多了数据（别的事务提交了insert）。

6.其他

1.行锁升表锁。当update的where条件是非索引（或索引失效）时，会对每行及聚集索引的间隙进行加锁，相当于表锁。

2.死锁：回滚其中一个事务。

示例1：

• 事务A：select * from t where a=1 for update;
• 事务B：select * from t where a=2 for update;
• 事务A：select * from t where a=2 for update;//等待
• 事务B：select * from t where a=1 for update;//死锁，回滚，ERROR 1212（40001）：Deadlock found when trying to get lock;try restarting transaction
• 事务A得到记录为2的这个资源。

3.索引失效：

①联合索引：不使用开头，后面的无效，中间断开，后面无效。

②计算、函数、类型转换等：因为结果是不可预计的，所以无法走索引。

③使用不等于索引：得到的数据行数占表中大多数数据，离散读取不如全表扫描

④is not null：如果本列是索引，那么相当于得到的数据占本表大部分数据，离散不如全表读取。

⑤is null：如果本列是索引，得到的数据将是表中较少部分，可以使用索引。

⑥like 开始使用%：无法使用索引。

⑦字符串不加单引号索引失效：猜测是因为不加引号和加引号的排序算法不一样