MySQL事务和隔离级别

1、数据库事务 ACID 特性

数据库事务的 4 个特性：

原子性(Atomicity)

事务中的多个操作，不可分割，要么都成功，要么都失败，事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。

一致性(Consistency)

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。

隔离性(Isolation)

多个事务之间就像是串行执行一样，不相互影响，一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

持续性(Durability)

事务提交后被持久化到永久存储，也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

2、隔离性

其中隔离性分为了四种：

READ UNCOMMITTED

可以读取未提交的数据，未提交的数据称为脏数据，所以又称脏读。此时：幻读，不可重复读和脏读均允许；

READ COMMITTED

只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以 RC 隔离级别要求解决脏读；

REPEATABLE READ

同一个事务中多次执行同一个 select,读取到的数据没有发生改变；此时允许幻读，但不允许不可重复读和脏读，所以 RR 隔离级别要求解决不可重复读；事务 A 读取与搜索条件相匹配的若干行。事务 B 以插入或删除行等方式来修改事务 A 的结果集，然后再提交。事务 A 再读取时，却发现数据发生了变化，造成了幻读（MySQL 默认的隔离级别）。

SERIALIZABLE

Serializable 是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

3、几个概念

脏读：可以读取未提交的数据。RC 要求解决脏读；
不可重复读：同一个事务中多次执行同一个 select, 读取到的数据发生了改变(被其它事务 update 并且提交)；
可重复读：同一个事务中多次执行同一个 select, 读取到的数据没有发生改变(一般使用 MVCC 实现)；RR 各级级别要求达到可重复读的标准；
幻读：同一个事务中多次执行同一个 select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务 delete/insert 并且提交)。SERIALIZABLE 要求解决幻读问题；

读提交，在读取一条记录时会出现不可重复读；
可重复读，通过对事务里面的读写操作加锁解决了读提交的问题，但是对统计某个范围内的记录数量，还是会产生幻读。
避免不可重复读需要锁行，避免幻影读则需要锁表。

这里一定要区分 不可重复读和幻读：

不可重复读重点在于 update 和 delete：同样的条件的 select, 你读取过的数据, 再次读取出来发现值不一样了
幻读的重点在于新增或者删除：同样的条件的 select, 第 1 次和第 2 次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大：对于前者, 在 RC 下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR 隔离下使用 MVCC 实现可重复读；对于后者, 要锁住满足条件的记录及所有这些记录之间的 gap，也就是需要 gap lock。

数据库锁

对于解决可重复读需要锁行；对于解决幻影读则需要锁表。目前主要使用悲观锁和乐观锁来实现这两种隔离级别。可重复读，第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据；幻影读，读用读锁，写用写锁，读锁和写锁互斥，可有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
1）悲观锁
对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。
悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。
在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据，来保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
2）乐观锁
乐观锁机制采取了更加宽松的加锁机制，在一定程度上解决了这个问题。
乐观锁大多是基于数据版本（Version）记录机制实现，为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。要说明的是，MVCC 的实现没有固定的规范，每个数据库都会有不同的实现方式（这里讨论的是 InnoDB 的 MVCC）。

目前 MySQL、ORACLE、PostgreSQL 等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的 MVCC（多版本并发控制）来避免这两种问题。

隔离级别

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

4、实例

除了 MySQL 默认采用 RR 隔离级别之外，其它几大数据库都是采用 RC 隔离级别。

但是他们的实现也是极其不一样的。Oracle 仅仅实现了 RC 和 SERIALIZABLE 隔离级别。默认采用 RC 隔离级别，解决了脏读。但是允许不可重复读和幻读。其 SERIALIZABLE 则解决了脏读、不可重复读、幻读。

MySQL 的实现：MySQL 默认采用 RR 隔离级别，SQL 标准是要求 RR 解决不可重复读的问题，但是因为 MySQL 采用了gap lock，所以实际上 MySQL 的 RR 隔离级别也解决了幻读的问题。
那么 MySQL 的 SERIALIZABLE 是怎么回事呢？其实 MySQL 的 SERIALIZABLE 采用了经典的实现方式，对读和写都加锁。

create table `test_trx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT  CHARSET=utf8;

insert into test_trx(value) value(1),(2),(3),(4),(5);

下面以例子来说明上面上面的情况

1) 脏读

未提交隔离级别, 事务 1 中更新的 value,未提交,事务 2 中可以看到、造成脏读

2) 虚读(不可重复读)

读提交隔离级别,事务 2 在同一个事务中,读取事务 1 提交后的 update 更改

3) 幻读

看下大部分 mysql 所说的幻读现象

事务 2,查询记录里面没有记录 7,插入 7 的时候提示主键重复,幻读.

RR 事务隔离级别号称可以解决幻读的问题（通过当前读加锁来实现）

5、gap 锁（当前读）解决幻读问题

mysql 说对数据加锁不管共享锁还是互斥锁就能解决幻读的问题

select * from test_trx where id between 1 and 7 lock in share mode;

select * from test_trx where id between 1 and 7 for update;

通过 gap 锁+当前读解决幻读问题,但是 lock in share mode 和 for update 不是标准的 sql 语法.

6、RR 没有解决的幻读

场景：我们知道 grap 锁能够将右边的记录进行加锁，因此我要统计表记录的数量，我只需要对最大记录加锁就行了