MySQL事务

MySQL的InnoDB存储引擎，是通过事务来保证数据的一致性的。
数据库事务通常包含了一个序列的对数据库的读/写操作。为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。

特性

事务的四个特性ACID。分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性（Automicity）：事务作为一个 整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。一致性是说无论事务提交还是回滚，不会破环数据的完整性。比如A给B转100元，如果成功了，A的账户必定会扣100，而B的账户必定会增加100元；如果失败了，A和B的账户余额不会改变。不会存在其他的情况，这样就是符合一致性的。事实上，原子性、持久性和隔离性都是为了保证一致性。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

原子性

事务的原子性是指：一个事务中的多个操作都是不可分割的，只能是全部执行成功、或者全部执行失败。
MySQL事务的原子性是通过undo log来实现的。undo log是InnoDB存储引擎特有的。具体的实现方法是：将所有对数据库的修改（增、删、改）都写入日志（undo log）。如果一个事务中的一部分操作已经成功，但另一部分操作，由于断电/系统崩溃/其它的软硬件错误而无法成功执行，则通过回溯日志，将已经执行成功的操作撤销，从而达到全部操作失败的目的。
undo log是逻辑日志，可以理解为：记录和事务操作相反的SQL语句，事务执行insert语句，undo log就记录delete语句。它以追加写的方式记录日志，不会覆盖之前的日志。除此之外undo log还用来实现数据库多版本并发控制（Multiversion Concurrency Control，简称MVCC）。

持久性

事务的持久性是指：一个事务对数据库的所有修改，都会永久的保存在数据库中。
MySQL事务的持久性是通过redo log来实现的。redo log也是InnoDB存储引擎特有的。具体的实现方式是：当发生数据修改（增、删、改）的时候，InnoDB引擎会先将记录写道redo log中，并更新内存，此时更新就算完成了。同时InnoDB引擎会在合适的时机将记录刷到磁盘中。
redo log是物理日志，记录的是在某个数据页做了什么修改，而不是SQL语句的形式。它有固定大小，是循环写的方式记录日志，空间用完后会覆盖之前的日志。
undo log和redo log并不是直接写到磁盘上的，而是先写入log buffer。再等待合适的时机同步到OS buffer，再由操作系统决定何时刷到硬盘，具体过程如下：

既然undo log和redo log都是从log buffer到OS buffer，再到磁盘，所以中途还是有可能因为断电/硬件故障等原因导致日志丢失。为此MySQL提供了三种持久化方式。innoDB_flush_log_at_trx_commit这个参数主要控制InnoDB将log buffer中的数据写入OS buffer，并刷到磁盘的时间点，取值分别为0，1，2，默认值是1。这三个值的意思分别如下：

MySQL默认设置的方式1，也就是每次提交后直接写入OS buffer，并且调用系统函数fsync()把日志写到硬盘上。就保证数据一致性的角度来说，这种方式无疑是最安全的。但是安全大多数时候意味着效率偏低。每次提交都直接写入OS buffer并写到磁盘，无疑会导致单位时间内IO的次数过多而效率低下。除此之外，还有方式0和方式2。基本上都是每秒写入磁盘一次，所以效率都比方式1更高。但是方式0是把数据先写入log buffer再写入OS buffer再写入磁盘，而方式2是直接写入OS buffer，再写入磁盘，少了一次数据拷贝的过程（从log buffer到OS buffer），所以方式2比方式0更加效率。
数据库系统崩溃后重启，此时数据库处于不一致的状态，必须先执行一个crash recovery的过程：首先读取redo log，把成功提交但是还没来得及写入磁盘的数据重新写入磁盘，保证了持久性。再读取undo log将还没有成功提交的事务进行回滚，保证了原子性。crash recovery结束后，数据库恢复到一致性状态，可以继续被使用。

隔离性

数据库事务的隔离性是指：多个事务并发执行时，一个事务的执行并不应影响其他事务的执行。正常情况下，肯定是多个事务同时操作同一个数据库，所以事务之间的隔离就显得必不可少。
如果没有隔离性，会发生哪些问题？

第一类丢失更新

第一类丢失更新是指：一个事务在撤销的时候，覆盖了另一个事务已提交的更新数据。
假设事务A和事务B操作同一个账户的金钱：

时间	事务A	事务B
T1	开启事务	开启事务
T2	查询账户余额：500元	查询账户余额：500元
T3	取出100元，剩余400元	取出100元，剩余400元
T4	提交事务，账户余额：400元	-
T5	-	撤销事务，账户余额：500元

事务B在撤销事务的时候，覆盖了事务A在T4的时候已经提交的更新数据。A在T3的时候已经取走了100元，此时的余额应该是400元，但是由于事务B开始的时候，余额是500元，所以回滚后，余额也会变成500元。

脏读

脏读：一个事务读到了另一个事务未提交的更新数据。

时间	事务A	事务B
T1	开启事务	开启事务
T2	查询账户余额：500元	-
T3	取出100元，剩余400元	-
T4	-	查询余额：400元
T5	撤销事务，账户余额：500元	-

事务A在T3的时候取走了400元，但是未提交。事务B在T4时查询余额就能看到事务A未提交的更新。

幻读

幻读（虚读）是指：一个事务读到了另一个事务已提交的新增数据。

时间	事务A	事务B
T1	开启事务	开启事务
T2	-	执行select count统计
T3	新增一条数据	-
T4	提交事务	-
T5	-	执行select count统计

事务B在同一个事务中执行两次统计操作，得到的结果不一样。

不可重复读

不可重复读：一个事务读到了另一个事务已提交的更新数据。

时间	事务A	事务B
T1	开启事务	开启事务
T2	查询余额：500元	查询余额：500元
T3	取走100元，剩余：400元	-
T4	提交事务	-
T5	-	查询余额：400元

事务B在同一个事务中，两次读取余额，得到的结果却不一样。

第二类丢失更新

第二类丢失更新是指：一个事务在提交的时候，覆盖了另一个事务已提交的更新数据。

时间	事务A	事务B
T1	开启事务	开启事务
T2	查询余额：500元	查询余额：500元
T3	取走100元，剩余：400元	取走100元，剩余：400元
T4	提交事务，账户余额：400元	-
T5	-	提交事务，账户余额：400元

事务A和事务B分别取了100元，所以余额应该为300元。但是事务B在提交的时候，覆盖了事务A已经提交的更新数据，所以导致结果出错。

为了解决这五类问题，MySQL提供了四种隔离级别

• Serializable（串行化）：事务之间以一种串行的方式执行，安全性非常高，效率低。
• Repeatable Read（可重复读）：是MySQL默认的隔离级别，同一个事务中相同的查询会看到同样的数据行，安全性较高，效率较好。
• Read Commited（读已提交）：一个事务可以读取到另一个事务已经提交的数据，安全性较低，效率较高。
• Read Uncommited（读未提交）：一个事务可以读取到另外一个事务未提交的数据，安全性低，效率高。
  隔离级别与并发性能的关系图如下：
  
  需要注意的是，除了Serializable能完完全全的解决这五类问题之外，其余的三种隔离级别都不能完全解决这五类问题。各种隔离级别能解决
  的问题对应如下

隔离级别	是否出现第一类丢失更新	是否出现脏读	是否出现虚读	是否出现不可重复读	是否出现第二类丢失更新
Serializable	否	否	否	否	否
Repeatable Read	否	否	是	否	否
Read Commit	否	否	是	是	是
Read Uncommited	否	是	是	是	是

Repeatablee Read

Repeatable Read（可重复读）是MySQL默认的隔离级别，也是使用最多的隔离级别。Reportable Read无法解决虚读（幻读）问题。下面来看一个实例

# 建表
CREATE TABLE student  (
  id int(4) primary key auto_increment,
  name varchar(10)
) ENGINE = InnoDB;

# 插入一条数据
insert into student(name) values('zhangsan');

开启连个操作窗口，均关闭自动提交set autocommit = 0，其余的操作的时间线如下：

时间	事务A	事务B
T1	select * from student	-
T2	-	insert into student(name) values (‘lisi’)
T3	-	commit
T4	select * from student	-

按照上述理论，会出现幻读现象。也就是事务A的第二次select会看到事务B提交的新增数据。
执行结果如下：

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

和预期的结果并不一致，没有出现幻读现象。
实际上MySQL在Repeatable Read隔离级别下，用MVCC（Multiversion Concurrency Control，多版本控制器）解决了select普通查询的幻读现象。
具体的实现方式就是事务开始时，第一条select语句查询结果集会生成一个快照（snapshot），并且这个事务结束前，同样的select语句返回的都是这个快照的结果，而不是最新的查询结果，这就是MySQL在Repeatable Read隔离级别对普通select语句使用的快照读（snapshot read）。

快照读和MVCC是什么关系？

MVCC是多版本并发控制，快照就是其中的一个版本。所以可以说MVCC实现了快照读，具体的实现方式涉及到MySQL的隐藏列。
MySQL会给每个表自动创建三个隐藏列：

• DB_TRX_ID：事务ID，记录操作（增、删、改）该数据事务的事务ID
• DB_ROLL_PTR：回滚指针，记录上一个版本的数据在undo log中的位置。
• DB_ROW_ID：隐藏ID，创建表没有合适的索引作为聚簇索引时，会用该隐藏ID创建聚簇索引。
  由于undo log中记录了各个版本的数据，并且通过DB_ROLL_PTR可以找到各个历史版本，并且由DB_TRX_ID决定使用哪个版本（快照）。所以相当于undo log实现了MVCC，MVCC实现了快照读。
  如此看来，MySQL的Repeatable Read隔离级别利用快照读，已经解决了幻读的问题。但是事实并非如此，接下来再看一个例子。
  继续上面的操作（注意：开始前，先提交上一次操作的事务）

时间	事务A	事务B
T1	select * from student	-
T2	-	insert into student(name) values (‘wangwu’)
T3	-	commit
T4	update student set name = ‘zhaoliu’ where name = ‘wangwu’	-
T5	select * from student	-

事务A在T1的时候生成快照，事务B在T2的时候插入一条数据wangwu，然后提交。在T4的时候把wabgwu更新成zhaoliu，根据上一个例子，此时事务A是看不到wangwu这条数据的，所以更新也不会成功，并且在T5的时候查询，和T1时候一样，只有zhangsan和lisi两条数据。
执行结果如下：

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)

但是执行结果却不是预期的那样，事务A不仅看到了wangwu，还把它成功的改成了zhaoliu。即使事务A成功commit之后，再次查询还是这样。
这其实时MySQL对insert、update和delete语句所使用的当前读（current read）。因为涉及到数据的修改，所以MySQL必须拿到最新的数据才能修改，所以涉及到数据的修改肯定不能使用快照读（snapshot read）。由于事务A读到了事务B已提交的新增数据，所以就产生了前文所说的幻读。

那么在Reportable Read隔离级别是怎么解决幻读的呢？

是通过间隙锁（Gap Lock）来解决的。InnoDB支持行级锁，并且行锁时锁住索引。而间隙锁是用来锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务的隔离级别为Reportable Read或以上级别而已的，间隙锁和行锁一起组成了Next-Key Lock。当InnoDB扫描索引记录的时候，会首先对索引记录加上行级锁，再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙插入记录。这样就有效的防止了幻读的发生。
默认情况下，InnoDB工作在Repeatable Read的隔离级别下，并且以Next-Key Lock的方式对索引行进行加锁。当查询的索引具有唯一性（主键、唯一索引）时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降为行锁，仅仅锁住索引本身，而不是范围（除非锁定不存在的值）。若是普通索引，则会使用Next-Key Lock将记录和间隙一起锁定。

• 使用快照读的语句

select * from ...

• 使用当前读的语句

select * from ... lock in share mode
select * from ... for update
insert into table...
update table set ...
delete table where ...

总结

本文主要讲解了MySQL事务的ACID四大特性、undo log和redo log分别实现了原子性和持久性、log持久化的三种方式、数据库并发下的五类问题、四种隔离级别、RR隔离级别下select幻读通过MVCC解决、select … lock in share mode/select … for update/insert/update/delete的幻读是通过间隙锁来解决。