MySQL 四种事务隔离级别

一、事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态。也就是说事务是一个不可分割的整体。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如 A 向 B 转账，不可能 A 扣了钱，B 却没有收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
4、持久性（Durability）：事务完成后，事务对数据库的所有操作都被保存到数据库，不能回滚。

二、事务的并发问题

1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据就是脏数据。
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取过程中，对数据做了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读侧重于修改，幻读侧重于新增或删除。解决幻读需要锁表。

三、MySQL 事务隔离级别

mysql 默认的事务隔离级别为 repeatable-read

四、演示说明各个隔离级别的情况

1、读未提交（read-uncommitted）:

（1）打开客户端 A，并设置当前事务模式为 read uncommitted，查询表 goods 的初始值；

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新 goods;

（3）这时，虽然客户端 B 的事务还没提交，但是客户端 A 就可以查询到 B 已经更新的数据；

（4）一旦客户端 B 的事务因为某种原因回滚，所有的操作都会将被撤销，那客户端 A 查询到的数据其实就是脏数据；

（5）在客户端 A 执行更新语句 update goods set goods_stock = goods_stock - 10 where id =1，iphonex 的 goods_stock没有变成30，居然是40，出现了脏读。也就是说，在应用程序中，我们会用40-10=30，并不知道其他会话的回滚，要想解决这个问题，可以采用读已提交的隔离级别。

2、读已提交（read committed）

（1）打开一个客户端 A ，并设置当前事务级别为 read committed，查询表 goods的初始值；

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新 goods ；

（3）这时，客户端 B 的事务还没提交，客户端 A 不能查询到 B 已经更新的数据，解决了脏读的问题；

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

（5）客户端 A 执行上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题；

3、可重复读（repeatable read）

一切目的是：保证可重复读；A 事务开启时，若A 的读操作先于 事务B 的写操作，则为了可重复读，在 A 事务提交前，A事务 不会将 B 的最新值读过来；若A 的读操作晚于 B 的写操作，即事务的首次读，会读取数据的最新值。写操作为了保证数据的一致性，都是会基于最新数据操作的。

（1）打开一个客户端 A，并设置当前事务级别为 repeatable read，查询表 goods；

（2）在客户端 A 提交事务之前，打开另一个客户端 B，更新表 goods 并提交；

（3）客户端 A 再次执行查询操作，两次查询结果一致，没有出现不可重复读的问题；

（4）客户端 A 接着执行 update goods set goods_stock = goods_stock - 10 where id =1，iphonex 的 goods_stock没有变成50 - 10 = 40，iphonex 的 goods_stock的值用的是步骤（2）中提交的40来算的，所以结果是30，数据一致性没有被破坏。

（5）客户端 A 提交事务，查询表 goods；

（6）在客户端 B 开启事务，新增一条数据，其中 goods_stock字段值为40，并提交；

（7） 在客户端 A 计算 goods_stock 之和，值为30+50=80，没有把客户端 B 的值算进去，客户端 A 提交后再计算 goods_stock 之和，居然变成了120，这是因为把客户端 B 的40算进去了；

对于客户端 A，提交事务前后，统计的数据不一致，导致了幻读。站在开发者角度，数据的一致性并没有被破坏。但是在应用程序中，我们的代码可能会把80提交给用户，但是这个概率很小。想避免这种情况，除非锁住表，不让其新增或者删除，这就要讲到最后一个事务隔离级别-“串行化”。

4、串行化（serializable）

（1）打开客户端 A，设置当前事务级别为 serializable，查询表 goods；

（2）打开一个客户端B，并设置当前事务模式为 serializable，插入一条记录报错，表被锁了插入失败。mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

补充：

1、mysql中默认的事务隔离级别是 repeatable read，并不会锁住读取到的行；
2、事务隔离级别为 read committed 时，写数据只会锁住相应的行。即写操作锁住了相应的行，导致读操作读不到，故只有写操作提交了，其他事务才能读到最新数据；
3、事务隔离级别为 repeatable read 时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁、行锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表（但是可读）。
4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑隔离级别设为 Read Committed，它能够避免脏读，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或者乐观锁来控制。

5、间隙锁（Gap Lock）：

定义：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引记录之后的空间。

间隙锁的出现主要集中在同一个事务中先 delete 后 insert 的情况下，当我们通过一个索引参数去删除一条记录的时候，如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这条记录，然后删除，然后释放锁；如果这条记录不存在，问题就来了，数据库会扫描索引，发现这个记录不存在，这个时候的 delete 语句获取到的就是一个间隙锁，然后数据库会扫描到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区别，锁住整个区间的数据，一个特别容易出现死锁的间隙诞生了。

（1）在默认的 repeatable read 隔离级别下，并且mysql 存储引擎是 InnoDB（支持事务） 时，客户端 A 开启事务，执行删除语句 delete from goods where id = 7；

（2）客户端 B 执行 insert into goods (id,goods_name,goods_stock) values (9,“aaaaaa”, 80)，发现产生了间隙锁。当执行删除语句是，由于没有 id = 7 的记录，于是会在id(4 - 10)区间内生成间隙锁，不允许此区间内的 insert 操作。

所以，为了避免间隙锁，需要遵循存在才删除原则，尽量避免删除不存在的记录。

6、MySQL 存储引擎和索引优化：

数据库存储引擎的选择：
MyISAM：不支持事务，表级锁（读操作是共享锁）。适用场景：无事务场景，只读场景。
InnoDB：事务级存储引擎，完美支持行级锁、事务ACID特性。MySQL 5.7以上的默认选择。

大事务：在一个事务中，处理了太多的数据或者太多的SQL，导致处理时间很长而产生锁表。

如何避免大事务？
1、避免一次处理太多的数据，可以采用分批处理。
2、移出不必要在事务中的select操作。

索引的选择：

1、BTREE索引

BTREE索引，底层是B+树的实现方式，B+树是根据主键ID的顺序进行分配的，其包含类似于二分搜索树的特性，B+树也是一种平衡树。B+树非叶节点中存放的关键码（主键ID）并不只是数据对象的地址指针，非叶节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码（主键ID）从小到大顺序链接。

（1）BTREE索引的使用场景：

场景1：

场景2：

使用场景：
1、全值匹配的查询，如oder_sn=‘9876432119900’；
2、匹配最左前缀的查询，在联合索引中，只要最左的索引列用到了，该索引就会生效。但是如果是后面的索引列用到了，最左列未使用，是不会生效的；
3、匹配列前缀查询，即模糊查询最前面不要使用‘%’，如order_sn=‘9876%’；
4、匹配范围的查询，如oder_sn>‘9876432119900’，order_sn<‘9876432119999’；
5、精确匹配左前列并范围匹配另外一列。

（2）BTREE索引的使用限制：

使用限制：
1、如果不是按照索引最左列开始查找的，则无法使用索引；
2、使用索引时不能跳过索引的列，假如3个列的联合索引，用到了1、3，未用的2，此时只有1是生效的，3是不生效的；
3、Not in 和 <> 操作无法使用索引；
4、如果查询中有某个列的范围查找，则其右边的所有列都无法使用索引。

2、Hash索引

Hash索引，底层是Hash表。具体Hash表的结构，可以参考我的另一篇文章 Java7/8 中的HashMap 和 ConcurrentHashMap。

（1）Hash索引的特点：

只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引。

Hash索引的限制：
1、Hash索引必须进行二次查找，因为Hash索引存储的是Hash码，并不是直接存储数据的指针地址，所以需要二次查询，但是由于数据库都是有缓存的，这样的性能损耗可以忽略；
2、Hash索引无法用于排序，因为它不像BTREE那样是顺序存储元素的；
3、Hash索引不支持部分索引查找也不支持范围查找，只支持精确查找；
4、Hash索引中的Hash码的计算可能存在Hash冲突，从而影响性能。

3、索引优化：

创建联合索引时，如何选择索引列的顺序？
1、经常会被使用到的列优先；
2、选择性高的列优先，即值的可能性比较多的列；
3、宽度小的列优选。

使用索引扫描来优化排序：
1、索引的列排序和Order by 子句的顺序完全一致；
2、索引中的所有列的方向（升序，降序）和Order by 子句完全一致；
3、Order by 中的字段全部在关联表的第一张表中。