MySQL事务详解

事务的概念（Transaction）

事务是指作为单个逻辑工作单元执行的一系列操作，要么都执行成功，要么都执行失败。事务处理可以确保除只有本事务单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的一个逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。
事务主要用于处理操作量大，复杂度高的数据，比如A给B转钱，A的账户扣除金额，B的账户增加金额，这一过程的中途不能发生一部分执行（A的账户扣除金额），一部分未执行（B的账户未增加金额），因此需要把上述的过程封装成一个事务。

事务的特性ACID

原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。如在一张表的某个字段是唯一的，如果一个事务对该字段进行了修改，但是事务提交或者回滚导致该字段非唯一，这就破坏了一致性要求。
隔离性（Isolation）：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。一个事务在提交之前所做的操作是否为其他事物可见，即事务相互之间存在什么影响，是可以通过设置隔离级别来控制的。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障，数据库也能将数据恢复。

事务的实现方式

原子性、一致性、持久性通过数据库的redo log和undo log来完成，redo log称为重做日志，用来保证事务的原子性和持久性，undo log称为回滚日志，用来保证事务的一致性。事务的隔离性通过锁机制来实现。

事物并发引发的问题

注：下面提到的更新操作都是讲count值加1
第一类丢失更新：这个问题是由回滚原因造成的，因此也叫做回滚丢失。如A事物和B事物同时更新count值，都读取到count值为100，A事物提交更新操作，count的是变为101，B事物因为某种原因执行失败而执行回滚操作，把count的值还原为100，这样A事物更新的值就丢失了。
第二类丢失更新：这个问题是因为更新的值被其他事物给覆盖了，也叫做覆盖丢失。如A事物和B事物同时更新count值，都读取到count值为100，A事物先更新成功并提交，count值变为101，这时，B事物更新提交，count变为101，由于事物一还是从100开加1，这样A事物的更新就丢失了。
以上两类统称丢失更新。
脏读：事务对一个事务读取了另一个事务修改了但是未提交的数据。如A事物更新count值从100变为101，B事物读取到了101，而A事物因为某种原因执行失败而执行回滚操作，那B事物读取的数据就是错误的。这种错误只会发生在读未提交的隔离级别下，但是目前几乎没有数据库的默认隔离级别是读未提交，所有这种错误就不会发生。
不可重复读：一个事务在提交前，重复读取之前读取过的数据，但是得到不同的结果，因此其他事务对该数据进行了修改并提交。操作这个现象的原因是其他事物对该数据做了更新操作。如A事物第一次读取到count值为100，还未提交，B事物将count修改为101，A事物再次读取count值则为101。脏读和不可重复读的区别是脏读读取的是为提交的数据，不可重复读读取的是已提交的数据。
幻读：幻读是指一个事物在前后两次执行同一个范围的查询看到不同数目的记录。

事物引起了这么多的问题，并不是每一种问题都需要完全避免，因为还要考虑到具体业务和数据库负载等。由于这些问题都是因为多个事物之间相互影响造成的，这说明如果把两个事物之间隔离起来就会降低这种相互影响。这时候，通过调整数据库事务隔离级别就出现了，事务的隔离机制的实现手段之一并且也是大多数情况下采取的方法就是利用锁。

事物隔离级别

读未提交（Read Uncommitted）：即使一个事务的更新语句没有提交,但是别的事务可以读到这个改变。会导致以上几种异常情况都可能出现，极易出错，没有安全性可言，基本不会使用。
读已提交（Read Committed）：一个事务只能看到其他事务的已经提交的更新，看不到未提交的更新，这是大多数数据库的默认隔离级别，如Oracle,Sqlserver。
可重复读（Repeatable Read）：一个事务中进行两次或多次相同的数据库读操作，得到的结果是一样的。这是Mysql数据库的默认隔离级别。
串行化（Serializable）：事务执行的时候不允许别的事务并发执行，而是完全串行化的读，只要存在读就禁止写，但可以同时读，消除了幻读。这是事务隔离的最高级别，虽然最安全，但是效率太低，一般不会用。

(√表示在该模式下依旧会发生对应的问题，×表示在该模式下不会发生对应的问题)

幻读

这里重点讲解下幻读

准备

表结构如下：

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);

事务A如下：

begin;
select * from t where d=5 for update;
commit;

事务隔离级别：可重复读

试想一下，上面那个事务是锁住id为5的记录还是锁住整个表的每条记录，或者还有其他的锁方式？

场景1

假设是第一种情况：只锁住id为5的记录
看下面这种场景：

由于事务A只锁住id为5的记录，所以事务B和事务C插入的数据，在事务A是使用锁定读能看到其他事务插入的数据（非锁定读看不见新插入的数据），因此这么一看幻读是没有问题的，但是再来看下面这种场景。

场景2

事务A的语义是想把所有d=5的记录都加上行锁，此时只锁住了id为5的记录，因此事务B对id=0的记录的修改是可以执行的，事务C插入id=1的记录后，又修改id=1的记录也是可以执行的，因为这些记录并没有被锁上，但是其实已经破坏的事务A的语义。其次，还破坏了数据的一致性。
对上图做下改动：

经过上图的三个事务之后，在T1时刻，id=5的记录变为（5,5,100），在T6时刻才提交；在T2时刻，id=0的记录变为（0,5,5），在T4时刻，表中提交了记录（1,5,5）。
现在来分析binlog中的内容（事务提交的时候会触发写binlog）：

• 在T2时刻，执行了两条sql，id=0的记录变为（0,0,5），再变为（0,5,5）
• 在T4时刻，执行了两天sql，插入（1,1,5），然后修改为（1,5,5）
• 在T6时刻，提交了事务A，把所有d=5的记录的d值修改为100
  这样，如果用binlog做数据备份恢复的时候，最终会导致上述三条记录变为（0,5,100） (1,5,100) 和 (5,5,100)，这就造成了严重的数据不一致问题。

场景3

分析场景2，就是因为事务A加锁的时候，只对id=5的那条记录上锁了，因此我们假设select * from t where d=5 for update;是对所有记录加锁：
看下图：

首先事务A锁住表的所有记录行，修改d=5的记录的d值为100，事务B尝试更新id=0的记录，被阻塞，事务C插入id=1的记录，由于id=1的记录是新插入的，因此没有被上锁，因此：

• 事务C提交（自动提交），在数据库中新增记录（1,5,5）
• 事务A提交，更新d=5的记录的d值为100，即（5,5,100）
• 事务B解除阻塞后提交（自动提交），更新id=0的记录为（0,5,5）

数据库最终的结果是（0,5,5），（1,5,5），（5,5,100）

这是数据库最终的结果，现在来分析binlog：

• 事务C提交，执行两条sql，新增（1,1,5）记录后改为（1,5,5）
• 事务A提交，执行了一条更新sql，得到（1,5,100）和（5,5,100）
• 事务B提交，将id=0的记录修改为（0,5,5）

最终，用binlog备份恢复的时候，id=0的记录为（0,5,5），id=1和id=5的记录分别为（1,5,100），（5,5,100）。

对于id=0和id=5的记录保证了一致性，但是对于新插入的id=1的记录还是没办法保证一致性。

因此，幻读破坏了数据一致性保证

如何解决幻读

InnoDB引入了间隙锁：就是锁住两个记录的间隙，不让其他事务再插入数据
关于锁的详细介绍：《最详细的MySQL锁（悲观锁 乐观锁 共享锁 排它锁 记录锁 间隙锁 临键锁等）讲解》

事务查看

• 查看MySQL是否自动提交事务 0表示手动提交事务 1表示自动提交事务

SELECT @@AUTOCOMMIT; 

• 设置事务提交方式

SET AUTOCOMMIT = 0;

• 查看事务隔离级别

select @@tx_isolation;

MySQL事务隔离级别实例

以mysql为例讲解各种隔离级别下的情况，测试表为user（id,name,gender,passwd,email）
隔离级别：read-uncommitted
脏读测试流程：
A设置隔离级别为read-uncommitted（注意这里未声明都是session级别，而非全局的），开启事务，查询id=1的记录
B设置隔离级别为read-uncommitted,开启事务，修改id=1的记录，但不提交
A再次查询id=1的记录，和第一次查询的比较一下
B事务回滚，A事务回滚。
A

B

结论：A读到了B没有提交的内容，隔离级别为read-uncommitted的时候出现脏读。
第一类更新丢失测试流程
A设置隔离级别为read-uncommitted，开启事务，查询id=1的记录
B设置隔离级别为read-uncommitted，开启事务,查询id=1的记录
A修改id=1的记录
B修改id=1的记录
A提交
B回滚
A在查询一次id=1的记录，看看自己的修改是否成功
结论：结果不如我所想的，A的更新成功了，为什么呢？A执行update语句的时候对该条记录加锁了，B这时候根本无法修改直至超时，也就是至少在mysql中在read-uncommitted隔离级别下验证第一类丢失更新，据了解有的数据库好像可以设置不加锁，如果能够不加锁的话则可以实现。
A

B

不可重复读测试流程
同脏读
第二类丢失更新流程
A开启事务，查询order_id=1的记录
B开启事务，查询order_id=1的记录
A把查出来的count加1后更新
B把查出来的count加1更新
A提交，B也提交
A

B

结论：A的更新丢失，我们希望的结果是3，而实际结果是2，跟java的多线程很像对不对，read-uncommitted隔离模式下会出现第二类丢失更新。
幻读测试流程
A开启事务，查询user表所有数据
B开启事务，新增一条记录
A再次查询user表所有记录，和第一次作比对
A回滚，B回滚
A

B

结论：A两次查询全表数据结果不同，read-uncommitted隔离模式下会出现幻读。
隔离级别：read-committed
脏读测试截图
A

B

结论：A没有读到B没有提交的内容，隔离级别为read-committed的时候不会出现脏读。
隔离级别：repeatable-read
不可重复读测试截图
A

B

结论：A两次读取id=1的数据内容相同，repeatable-read隔离模式下不会出现不可重复读。
隔离级别：Serializable
幻读测试截图
A

B

结论：因为A事务未提交之前，B事务插入操作无法获得锁而超时，Serializable隔离模式下不会出现幻读。

附录

为了保证事务中的隔离性，InnoDB引擎通过隔离级别实现，而四种隔离级别本质上是通过锁机制实现的。
《InnoDB 中四种事务隔离级别是如何实现的？》
《最详细的MySQL锁（悲观锁 乐观锁 共享锁 排它锁等）讲解》