MySQL事务详解

一、什么是事务

• 事务是应用程序中一系列严密的操作，用来保证数据库的完整性。
• MySQL中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• mysql默认自动提交事务。

二、事务的执行流程

1. 查询操作先从Buffer Pool中查询数据，若存在则直接输出，不存在则读取磁盘中的数据并放入Buffer Pool；
2. 在操作任何数据之前，会先将数据的旧值写入undo log日志文件中，以便执行事务过程中出现异常后好回滚到事务执行之前的数据；
3. 然后再操作Buffer Pool（内存）中的数据并设置成脏页，同时将操作的数据写入到Redo Log Buffer；
4. 将Redo Log Buffer中的数据写入到os cache系统内核缓存中；
5. 系统内核调用fsync()将os buffer中的数据写入到redolog文件中，并设置状态为prepare；
6. redolog文件写入完成后，由server层将执行的操作性修改，包括数据库结构和表数据的变更，但不包括select语句，写入到binlog文件中；
7. binlog写入成功后，设置状态为commit；
8. 最后完成事务提交。

三、脏页什么时候会被刷入磁盘

一般来说，当事务提交后，MySQL首先更新的是 Buffer Pool 中数据所在的页，然后将该页设置为脏页，但是磁盘中还是原数据。因此，脏页需要被刷入磁盘，以保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。数据刷盘的时机有以下几种：

• 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
• Buffer Pool 空间不足时，会先将脏页刷新到磁盘；
• MySQL 认为空闲时，后台线程回定期将适量的脏页刷入到磁盘；
• MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

四、事务的常用语法

开启事务： start transaction;
提交事务： commit;
回滚事务： rollback;
查看当前事务隔离级别：
SHOW VARIABLES LIKE ‘transaction_isolation’;或者SELECT @@transaction_isolation
设置本次会话的事务隔离级别，该设置不会影响其他会话，并且设置会随着当前会话的结束而结束：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
设置全局会话的事务隔离级别，该设置不会影响当前已经连接的会话，设置完毕后，新打开的会话，将使用新设置的事务隔离级别：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
设置一次操作的事务隔离级别，该设置会随着下一次事务的提交而失效：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
查看当前事务自动提交模式： SHOW VARIABLES LIKE ‘autocommit’;
设置当前事务自动提交开关：SET autocommit = 0|1|ON|OFF;（值为0或者OFF表示关闭自动提交，值为1或者ON表示开启自动提交）

五、事务的四个特性

事务具有四个特性：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持久性（ Durability ）。简称为 ACID 。

• 原子性：一个事务必须视为不可分割的最小工作单位。从开启事务，直到提交该事务，其间不管进行了多少次数据库的增、删、改、查的操作都视为一个整体，要么都执行，要么都不执行。主要依赖undo log来实现原子性。
• 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。主要是指数据处于一种语义上的有意义且正确的状态，保证在一个事务中的多次操作的数据中间状态对其他事务不可见的。主要是通过原子性、隔离性、持久性保证数据库状态的一致性。
• 隔离性：主要是指多个事务之间相互独立，互不干扰。事务隔离分为不同级别，包括未提交读（Read uncommitted）、已提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。主要依赖的是MVCC或者锁来实现的隔离性。
• 持久性：指一个事务一旦成功提交，它对数据库中的数据的改变是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。主要依赖的是redo log来保证持久性。

这里原子性跟一致性需要注意的是：原子性关注状态，要么全部成功，要么全部失败，不存在部分成功的状态。而一致性关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见。

六、事务的四个隔离级别

InnoDB默认是可重复读（RR）级别的。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

1. 脏读：指事务A读取了事务B未提交的更新数据，然后事务B进行回滚操作，那么A读取到的数据是脏数据。
2. 不可重复读： 指事务A多次读取同一数据时，事务B刚好更新了这个数据并提交了，导致事务A多次读取同一数据时，获取到的值不一致。
3. 幻读： 指事务A多次读取数据时，事务B刚好删除或者新增了一条数据并提交，导致事务A多次获取数据时，获取的记录条数不一致。

不可重复读的和幻读的主要区别在于，不可重复读时针对某一条记录的值的修改，而幻读主要时针对该表的记录新增或者删除操作。解决不可重复读的问题只需锁住满足条件的行，解决幻读则需要锁表。

七、隔离级别举例说明

1、未提交读

（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），并开启事务，查询当前表中数据；

（2）在客户端A的事务提交之前，打开另一个客户端B，更新小明的年纪为25岁；
（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据；假如这时A提交事务，A最终拿到的小明的年纪就是25岁，但是B接着并没有提交而是回滚操作，那么B刚才的更新的数据也会回滚到18，但是A已经把拿走了B刚才更新的25这值在使用了，这样A就产生了脏读。

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，这时客户端B查询的数据也成功回滚到开启事务之前的18岁：
（5）这时客户端A查询一次，数据又变回去了。

（6）客户端A再将小明的年纪进行+2操作；

（7） 客户端B表重新开始事务后，对user表记录进行修改，修改被挂起，直至超时；这是因为事务A在对该行数据进行更新操作的时候会给这个行数据加上排它锁（写锁），如果事务A不提交的话，就不会释放该锁，导致其他事务无法对该行数据重新添加任何锁，也就无法进行修改操作。

但是对另一条数据的修改成功，说明事务A的更新操作只是给当前操作的行加上了排它锁（写锁）。

select语句查询是没问题的，那是因为普通查询没有任何锁机制,查询不会受到任何锁的限制。但是不能通过for update和lock in share mode的方式查询数据，因为for update查询会加排它锁，而lock in share mode方式会加共享锁。

2、已提交读

（1）打开一个客户端A，并设置当前事务模式为read committed，查询到小明的age=18；

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表小明的age=25岁并查询出当前事务中小明的年纪也变成25了；
（3）这时，客户端B的事务还没提交，但是客户端A再次查询时小明的age任然是18，并没有获取到B更新的值，说明已经解决了脏读问题；

（4）然后客户端B的事务提交

5）客户端A再执行与上一步相同的查询，结果获取到刚B更新的数据，但是在事务A中两次查询的结果就不一致了，说明只有在事务提交后，才会对另一个事务产生影响，即产生了不可重复读的问题；

同样在A事务中对数据进行修改，然后再开启B事务，对同一条数据进行修改操作时，也会被挂起，直至超时，但是B事务对该条数据的select查询，以及其他的数据的修改操作依然时成功的，说明该级别下依然会给行数据加上排它锁（写锁）。

3、可重复读

（1）打开一个客户端A，并设置当前事务模式为repeatable read，开启事务，查询表的初始值;

（2）开启B事务，将小明的age-5，先不提交并查询出当前事务中小明age的值为20。

（3）再到A事务中进行查询小明信息的操作，获取到的age然后是25；

（4）再将B事务提交，然后回到A事务中再次查看小明的age值任为25，两次查询结果一致，说明已经防止了不可重复读的问题；

（5）再次开启B事务，然后执行添加一条记录的操作，并提交。

（6）回到A事务再次查询表中数据，发现并没有获取到B事务刚添加的数据，按道理来说这里应该会产生幻读，也就是这次查询应该能查出刚小红这条数据，但是并没有，那是不是就说明REPEATABLE READ这个级别可以防止幻读呢！其实并不是这样的，没有查出来是因为这只是个简单的查询，属于快照读，读取的数据相当于从历史数据中获取的，而当你使用当前读时，数据就会去拿那的事务所更新的最新数据来处理。

（6）这时，在事务A中再进行将小明age-5的操作，并查询操作后的age值为15，按道理事务A上次的查询结果age=25，再减5，值应为20才对，但是由于事务B已经对小明的age执行过减5的操作了并已经提交了，因为A事务上次查询属于快照读，查询的是历史数据，并不是B事务更新后的数据，但是在A事务对该数据进行修改操作的时候就会拿B事务更新后的值去操作的，也保证了数据的一致型。然后再使用当前读来查询下当前表的数据

事务A中通过当前读就查出了B事务新增的数据，这就导致了在A事务中的多次查询中，两次或者说多次的查询结果的记录数不一样，感觉产生了幻觉一样，这就是所谓的幻读。在RR级别下，快照读是通过MVCC(多版本控制)和undo log来实现防止幻读，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现防止幻读。

4、串行化

串行化简单来说就是当你在某个事务中需要对表中的数据进行增删改的时候，如果还有别的事务没有提交，那么整张表都会被锁导致你这个事务没有办法进行修改操作，但是你在这个事务中是可以执行查询操作的，也就是说SERIERLIZED级别会对整张表加上共享锁，导致并发性极低。