上篇文章介绍了MySQL的索引机制，基本上对MySQL如何优化查询速度做了比较充分的了解

• 一条MySQL查询语句经历了什么–索引

第二篇文章介绍一下事务，事务不是 MySQL 的原生支持，而是 InnoDB 存储引擎带来的 Feature。旧版本MySQL的默认存储引擎 MyISAM 就不支持事务能力。正因为事物能力，MySQL 的功能越发强大，也是因为事务，MySQL经常存在和索引无关的很多慢查询。

一般提到事务，最常见的例子就是银行卡转帐了。比如A给B转了100元，那必然是 A 账户扣100元和B账户加100元同时完成，不能存在B的钱已经加上了，但是A的钱还没有扣除的问题。

这就引出了事务这个概念。

事务是什么？

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的，但并不是所有引擎都支持事务。

事务的几个特点 ACID（Atomicity、Consistency、Isolation、Durability）即原子性、一致性、隔离性、持久性。

隔离性

当数据库上有个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了隔离级别的问题。

在说隔离级别之前，首先需要知道，隔离等级越高，执行效率就会越低。因此很多时候，需要找到的一个平衡点。SQL 标准的事务隔离级别有：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。

• 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到
• 读提交：一个事务提交之后，它做的变更才会被其他事务看到
• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突时，后访问的事务必须等待前一个事务执行完成，才能继续执行。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。

在“读未提交”隔离级别下直接返回记录上的最新值，没有视图的概念

在“串行化”隔离级别下直接用加锁的方式来避免并行访问。

MySQL 下可以使用如下命令可以看到当前 MySQL设置的事务隔离级别：

1
2
3
4
5
6
7

MySQL debian-sys-maint@(none):(none)> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

事务隔离的实现

先看下面的这个例子

1
2
3
4
5
6

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

这里需要注意：begin/start transaction 命令并不是事务的起点，如果需要立马启动一个事务，可以使用 start transaction with consistent snapshot 命令。事务C没有显式地调用 begin/commit ，指令，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交。

先说答案，事务A查询到的k的值是1，而事务B查询到的值是3。

为了理解这个现象，这里有两个“视图”的概念。

• 一个是 view。他是用一个查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。
• 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC （Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

MVCC

在可重复读隔离级别下，事务在启动的时候拍了一个快照（这个快照是基于整库的）。InnoDB 里面的每个事务有一个唯一的事务ID，叫作 transaction id。它是在事务开始的时候想 InnoDB 的事务系统申请的，按照申请顺序严格递增。

而每行数据也都是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并把 transaction id 赋值给这个数据版本的事务ID，记为 row trx_id 。同时，旧的数据版本要保留，并且在新数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本（row），每个版本有自己的 row trx_id。

图中的 3个虚线箭头即为 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 时，则根据 V4 依次执行U3、U2 算出来。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果，但之后，这个事务执行期间，其他事务的更新对它不可见。

在实现上，InnoDB 为每个事务构造了一个数组，用来保存这个事务的启动的瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没有提交。

这样，对于当前事务启动瞬间来说，一个数据版本的 row trx_id ，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的。
2. 如果是落在红色部分，表示这个版本是由将来启动的事务生成的，不可见
3. 如果是落在黄色部分，那就包括两种情况
   1. 若row trx_id 在数组中，表示这个版本是由还没有提交的事务生成，不可见
   2. 若row trx_id 不在数组中，表示这个版本是已经提交了的事务生成，可见。

undo log

在 MySQL 中，我们知道事务在执行过程中，如果发生了崩溃是可以提供回滚机制的。

实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以的得到前一个状态的值。

undo log 是 MySQL 中比较重要的事务日志之一，undo log 就是用来撤销回退的日志，在事务没提交之前。MySQL 会先记录更新前的数据到 undo log 日志文件中，当事务回滚或者数据库崩溃时，可以利用undo log 来进行回退。

undo log 的作用

1. 提供回滚操作

在一条数据更新操作的时候，不仅会记录 redo log ，还会记录 undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚操作（rollback）操作，利用 undo log 将数据恢复到事务开始之前的状态

2. 提供多版本控制 MVCC

MVCC，即多版本控制，在 MySQL 数据库 InnoDB 存储引擎中，用 undo log 实现多版本并发控制（MVCC），当读取的某一行数据被其他事务锁定时，他可以从 undo log 中分析出该行记录之前的数据版本是怎样的。从而让用户能够读取当前事务操作之前的数据（快照读）

快照读：SQL读取的数据是快照版本，也就是历史版本，不用加锁，普通的 SELECT 就是快照读

当前读：读取最新的版本，通过锁机制来保证读取的数据无法通过其他事务进行修改 LOCK IN SHARE MODE 就是当前读。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务在提交之前，数据库里面它可能用到的回滚记录都必须保留，这就导致非常占空间。

事务带来的性能问题

性能杀手-行锁

行锁是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，这时事务B也需要更新同一行，这时必须等待事务A的操作完成之后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上，但并不是不需要就立刻释放，而是要等待事务结束时才释放。

过多的事务会造成 MySQL 压力陡增，这里的原因涉及一个概念 “死锁和死锁检测”

当并发系统中的不同线程出现循环资源依赖时，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。这就是死锁。

上面这个情况，事务A在等待事务B释放 id = 2 的行锁，而事务B在等待事务 A 释放 id = 1 的行锁，事务A和事务B在互相等待对方释放资源，从而陷入了死锁状态。一般出现死锁后有两种策略：

• 一种是，直接进入等待，直到超时，超时时间可以设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条上的某一条事务，让其他事务得以继续执行。可以通过参数设置开启

正常情况下，我们采用的是第二种策略，即：主动的死锁检测。而且参数 innodb_deadlock_detect 的默认值本身也是 on。

但是这也是成本比较高的操作，每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁。这是一个 O(n) 的操作，假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是期间需要消耗大量CPU。一般解决这个问题的办法就是控制并发度，比如同一行最多只能有10个线程在更新。或者设计相关的中间件，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

事务隔离-幻读问题

先构造这样一个场景。表结构如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

上图中，Session A 执行了三次查询，分别是Q1、Q2、Q3，三次的语句相同并且使用了 for update 关键字，表示使用当前读，对d=5的行加上了写锁。这里看到 Q3 里读到了id=1这一行的现象，这就是“幻读”，也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里需要对 “幻读” 做一个说明：

1. 在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据，因此，幻读只在“当前读”下才会出现。
2. 上面 session B 的修改结果，被 session A 之后的 select 语句用 “当前读” 看到，不能成为幻读，幻读仅指“新插入的行”。

因为三个查询都是加了 for update,都是当前读，而当前读的规则就是读到所有已经提交记录的最新值。这和事务的可见性规则并不矛盾。

那么幻读有什么问题呢?

首先是语义上

session A 在 T1 时刻就声明了，“需要把所有 d = 5 的行锁住，不准别的事务进行读写操作”，而事实上，d = 5 的数据多了一行出来。语义被破坏了。并且多出来的（1,1,5）还是可以被随意操作的。比如这行数据其实还能再被改成（1,2,5）甚至是以前的（0,0,0）被改成了（0,0,5），但是id=0也是不受锁控制的。

其次是数据一致性的问题

锁是保证一致性的，这个一致性不止是数据库内部的数据状态的一致性，还有数据和日志在逻辑上的一致性。

我们给整个表的所有记录加上锁，但是阻止不了新插入的记录的变更。

那么幻读如何解决呢?

由于行锁只能锁住行，新插入的记录这个动作，要更新的是记录之间的间隙，所以为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁（Gap Lock）

比如开头插入6个初始记录，这就产生了 7 个间隙。这样就确保了无法插入新的记录，也就是说不仅给行加了行锁，还在间隙上加了间隙锁。

间隙锁 和 行锁 合称为 next-key lock。所以 select * from t for update 可以将整个表的所有记录锁起来。

需要注意的是，间隙锁是只有在可重复读隔离级别下才会生效。间隙锁的引入虽然解决了幻读问题，但是也增加了锁分析的复杂度。

由于忽略了间隙锁，在可重复读的隔离级别下，一条查询语句可能在开发人员不经意间成了整个系统死锁的最终元凶！

• 参考：MySQL回滚日志（undo log）总结