03事务隔离：为什么你改了我还看不见？

写在前面

因为MySQL中只有InnoDB支持事务，所以本文如无特别说明，表存储引擎都是InnoDB。

在02日志系统一条SQL更新语句是如何执行的？ 一文中分析了update语句执行的过程，以及该过程中使用到的binlog和redo log，而update作为可以引起数据库状态发生变化的一种场景，其中事务的概念绝对是不可不谈的，那么什么是事务呢？概念上讲就是，保证一组数据库操作，要么全部成功，要么全部不成功，绝对不可以出现部分成功部分不成功的情况。要保证一组数据库操作，要么全部成功，要么全部不成功就不是那么简单的事情了，需要用到MySQL中很多的技术，比如锁，MVCC等，下面我们就一起来看下吧！

1：ACID

众所周知，事务必须具备四个特性，即ACID，A->Atomic，原子性，所有的操作必须是一个统一的整体，要么全部成功，要么全部不成功其中事务的定义就是使用的该特性。C->Consistency,一致性，执行前后数据库的完整性没有被破坏，这种完整性既包括数据库本身的，如主键依然唯一，也包括业务完整性，如剩余余额和扣除的相加结果不变。I->Isolation，隔离性，即不同事务之间不能相互影响。D->Durability,持久性，事务提交的修改，永久存储，就算是发生宕机也不会丢失，即crash-safe 。

ACID中的ACD，都是比较纯粹的定义，必须达到这种效果才行，但是对于I,即隔离性，表述的是同时执行的多个事务之间的影响，而影响的方式又各不相同，所以对于I，是有不同具体实现的，具体我们通过接下来的内容看下。

2：I隔离性

当多个事务同时执行的时候数据的读取可能存在如下的问题：

1：脏读，读到其它事务未提交的修改，表达的点是修改，因为事务是未提交的，所以读到的本质上是错误的数据。
2：幻读，读到其它事务已提交的删除或者是新增，表达的点是新增或删除，因为事务是已提交的，所以读到的本质上是正确的数据。
3：不可重复读，读到其它事务提交的修改，表达的点是修改，因为事务的提交的，所以读到的本质上是正确的数据。

基于上述分析到的各种问题，mysql提出了隔离级别的概念，来处理这些问题，隔离级别越严格，上述的问题解决的个数越多，相反数据库执行的效率也就越低，在实际中要找到一个合适的折中点，目前提供的数据库隔离级别有如下几种：

首先看事务没有提交的情况，再看已经提交的情况，没有提交的包括修改（对应脏读），已提交的包括新增和删除（幻读），修改（不可重复度）。

读未提交：可以读到其它事务未提交的数据，所以有脏读，可以读到提交的新增或删除，因为有幻读，可以读到其它事务提交的修改，因此有不可重复度。
读提交：别的事务没有提交的修改读不到，因此没有脏读，但是提交的新增和删除可以读到，因此有幻读，可以读到其他事务提交修改，因此有不可重复度。
可重复读：未提交的修改看不到，所以没有脏读，可以读到已提交的新增和删除，因此有幻读，不能读到其他事务已提交的修改，因此没有不可重复度。
串行：读操作加读锁，写操作加写锁，锁冲突则等待，脏读，幻读，不可重复读都没有。

接下来我们先通过一个简单的例子来看下可重复读隔离级别是如何解决不可重复度问题的，先来看下我们当前的日志隔离级别：

mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

接下来创建一张表并插入一条用于测试的数据：

CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t8 (`c`) values(1);

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.01 sec)

我们接下来按照如下的步骤来做下试验：

1：事务A启动
2：事务A查询ID为1的行c的值
3：事务B启动
4：事务B查询ID为1的行c的值
5：事务A将ID为1的行c的值+1，并查询新值，然后提交事务
6：事务B查询ID为1的行c的值

1：事务A启动

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

2：事务A查询ID为1的行c的值

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.01 sec)

3：事务B启动

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

4：事务B查询ID为1的行c的值

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.01 sec)

5：事务A将ID为1的行c的值+1，并查询新值，然后提交事务

mysql> update t8 set c=c+1 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

6：事务B查询ID为1的行c的值

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

此时事务B并没有读到事务A已经提交的修改，说明不存在不可重复读的问题。那么在6：事务B查询ID为1的行c的值中是怎么做到读到的值是依然是1的呢，我们再来一起看下。在可重复读隔离级别中，在我们调用start transaction启动事务时，MySQL就会为该事物创建一个读视图，所有select的数据都是通过该读视图获取的，而视图的数据又是来自哪里呢？答案是undo log(回滚操作时也会用到),当执行6：事务B查询ID为1的行c的值如下图：

其中红色块就是undo log的内容(正常应该有多个节点，这里只是为了示例)，当执行语句select * from t8 where id=1;，会从最右侧节点一路回退到红色节点，然后根据内容C值从2回滚为1，获取最终的结果1。为了对这个过程更加清晰我们通过2.1：读视图来进一步看下。

关于一致性视图数组，也可以参考下图事务A产生修改，但不commit，事务B基于一致性视图数组查询数据的过程分析：

视图实际上是一个逻辑上的概念，可以认为是一致性视图数据组+undo log链的组合而构成的概念。

2.1：读视图

在可重复读的事务隔离级别下，每次启动事务的时候，MySQL都会为这个事务创建一个读视图，该视图可以理解为事务启动时的数据库状态的快照，因此不会受到其它事务对数据修改的影响,按照如下的步骤进行测试：

0：准备ID为1的数据，c值为1
1：事务1启动，查询ID为1的c的值，结果为1
2：事务B启动，修改ID为1的c的值为2，并提交事务
3：事务2启动，查询ID为1的c的值，结果为2
4：事务C启动，修改ID为1的c的值为3，并提交事务
5：事务D启动，修改ID为1的c的值为4，并提交事务，此时c的当前值为4
6：事务1查询ID为1的c的值，结果依然为1
7：事务2查询ID为1的c的值，结果依然为2

0：准备ID为1的数据，c值为1

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

1：事务1启动，查询ID为1的c的值，结果为1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

2：事务B启动，修改ID为1的c的值为2，并提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t8 set c=2 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

3：事务2启动，查询ID为1的c的值，结果为2

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
+----+------+
1 row in set (0.00 sec)

4：事务C启动，修改ID为1的c的值为3，并提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t8 set c=3 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.05 sec)

5：事务D启动，修改ID为1的c的值为4，并提交事务，此时c的当前值为4

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t8 set c=4 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

6：事务1查询ID为1的c的值，结果依然为1

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.02 sec)

7：事务2查询ID为1的c的值，结果依然为2

mysql> select * from t8 where id=1;
+----+------+
| id | c    |
+----+------+
|  1 |    2 |
+----+------+
1 row in set (0.01 sec)

以上事务1和事务2对应的视图，以及事务A，B，C对于数据的修改生成的undo log如下图（从左到右为时间顺序）：

当事务1执行select * from t8 where id=1;会从当前值4一直反推到初始值1，获取结果1,当事务2执行select * from t8 where id=1;,从当前值4一路反推到c从2变为1,判断改undo log的事务提交的时候本事务还没有启动，所以使用修改后的结果，即c从2变为1中的2作为结果。可以看到c的值在以上undo log中存在多个版本，这其实就是MVCC,是MySQL实现快照的几个重要技术。

另外需要注意到，如果是undo log一直不删除，势必会占用大量的磁盘空间，那么undo log什么时间删除呢，是在不需要的时候，那么什么时候不需要呢，首先可能使用其进行回滚操作的事务已经提交了，其次不存在使用来进行视图读操作的事务了，总结就是，当没有比undo log更早的视图时，该部分undo log就可以被删除了，按照上图的例子，如果是事务1视图和事务2视图删除，即事务1和事务2都commit后，这部分undo log就可以删除了。这也间接说明了，为什么在实际工作中要避免长事务，因为长事务会导致大量的undo log无法删除，从而大量的磁盘空间无法得到释放，另一个原因是长事务会生成很多的undo log节点，在长事务中通过事务获取某值时需要经过很多步骤的反推，节点过多时严重影响查询速度（对应的算法其实就是链表中数据的查找）。

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

在以上的分析中，一个非常核心的概念就是事务，所以怎么开始一个事务，怎么结束一个事务，我们需要非常的清楚，才能应对各种可能的问题，下面我们就来看下事务都有哪些启动方式，详细参考3：事务的启动方式。

3：事务的启动方式

事务启动分为两种方式，第一种是显式启动，另外一种是隐式启动，其中显式启动是通过start transaction,或者是begin。隐式启动就是当sql语句不在事务中时，自动的开始一个事务，自动开始的事务，如果是autoCommit=ON时，语句执行完毕会自动commit，如果是autoCommit=OFF的话，则必须使用commit，rollback等结束事务，如果忘记结束事务，就会出现长事务，这也是为什么不建议将autoCommit设置为OFF的原因。下面我们从显式启动和隐式启动两方面来分析下。

3.1：显式启动

这种方式是使用start transaction，begin关键字开启事务，这种情况下不管自动提交是否打开，都会启动一个事务(在执行第一个sql语句的时候启动,如果是想要立即启动可以使用start transaction with consistent snapshot)，最终需要通过commit，rollback提交或者是回滚事务。如下:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

以上虽然是执行了start transaction但是事务并没有启动，因为必须至少执行一个sql语句（查询语句也可以），如下：

mysql> select * from t8 where id=900;
Empty set (0.05 sec)

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.02 sec)

最后显式commit(rollback也行)即可，如下：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

3.2：隐式启动

注意不执行start transaction,begin等显式启动事务。

隐式启动有两种情况，第一种是当autoCommit=ON时，任何一条语句的执行都会启动一个事务，并且自动提交，这个过程对于用户是透明的，第二种是当autoCommit=OFF时，任何的一条语句的执行也会启动一个事务，但是这个事务不会自动提交，必须显式的执行commit，rollback等结束事务的语句。

3.2.1：autoCommit=ON

mysql> set autocommit=on;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.14 sec)

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

（这里会自动加一个start transaction）
mysql> update t8 set c=c where id=1;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1  Changed: 0  Warnings: 0
（这里会自动加一个commit）

事务数依然是0；
mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3.2.2：autoCommit=OFF

mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.03 sec)

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

（这里会自动加一个start transaction）
mysql> select * from t8 where id=900;
Empty set (0.01 sec)
（这里不会自动加commit，所以这里就可能埋下长事务的坑）

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

必须显式commit才会结束事务，如下：
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

简单总结就是，在执行一个sql语句的时候会判断当前会话是否有事务，如果是有使用已存在的事务（这个存在的事务可能是显式创建的也可能是隐式创建的），如果是没有则隐式创建，sql语句执行完毕后，分为以下的几种情况做不同处理：

1：事务是通过start transaction，则不做任何操作。
2：事务是隐式创建的，并且autoCommit=ON，则自动执行commit语句提交事务。
3：事务是隐式创建的，并且autoCommit=OFF，则不做任何操作。

对于1,3必须显式提交事务，但是1是显式启动的，所以开发人员本身是知道的，一般不会出现事务忘记提交导致长事务的问题，但是3很有可能导致长事务问题。

那么，在实际工作中，应该怎么选择呢，如无特殊情况设置set autocommot=ON,防止因为忘记显式启动事务而出现一个隐式启动的事务，导致长事务，其次就是使用start transaction,begin，显式的启动事务，这样能够让开发人员更好的知道哪个语句是否在事务中，从而更好的控制事务。

可通过语句：
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60 查询长事务，示例语句是查询事务时间超过60秒的长事务。