你不得不懂的mysql隔离级别底层

mysql作为最常用的关系型数据库，无论是在应用还是在面试中都是必须掌握的技能。

要印在脑子里面的东西

DDL:数据定义，它用来定义数据库对象，包括库，表，列，通过ddl我们可以创建，删除，修改数据库和表结构；

DML:数据操作语言，增加删除修改数据表中的记录；

DCL:数据控制语言，定义访问权限和安全级别；

DQL:数据查询语言，用它来查询想要的记录。

SQL执行顺序:

1. from;

2. join

3. on

4. where;

5. group by;

6. avg,sum.... 使用聚集函数进行计算;

7. having;

8. select;

9. distinct;

10. order by;

11. limit;

---

今天来讨论mysql中的事物隔离级别

1事物概念

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个属性,通常简称为事务的ACID属性：

原子性：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

一致性：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。

隔离性：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

持久性：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

事务的启动方式

1. 显示启动 set autocommit=1 begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

2. set autocommit=0 自动提交关掉,意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

3. 用commit work and chain代替 commit可以提交一个事务，并且开启另一个新的事务。

2事物带来的问题

我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。

这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。接下来，我们会深入讲解这些机制，让大家彻底理解数据库内部的执行原理。

脏写

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其他事务所做的更新。

脏读

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。

例：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

例：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

例：事务A读取到了事务B提交的新增数据，不符合隔离性

不可重复读与幻读有什么区别？

不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的「数据不一样」。（因为中间有其他事务提交了修改）

幻读的重点在于新增或者删除：在同一事务中，同样的条件，第一次和第二次读出来的「记录数不一样」。（因为中间有其他事务提交了插入/删除）

3事物的隔离级别

在 MySQL 中，事务支持是在引擎层实现的。你现在知道，MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一

InnoDB实现了四个标准的隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

常看当前数据库的事务隔离级别:

show variables like 'tx_isolation';

设置事务隔离级别：

set tx_isolation='REPEATABLE-READ';

查询mysql的长事务（大于60秒的事务）: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

Mysql默认的事务隔离级别是可重复读.

事务隔离级别

在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。

标准的事务隔离级别包括：

• 读未提交:一个事务还没提交时，它做的变更就能被别的事务看到

• 读提交:一个事务提交之后，它做的变更才会被其他事务看到

• 可重复读:一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读 隔离级别下，未提交变更对其他事务也是不可见的

• 串行化:顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

隔离级别是如何实现的呢？

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准；

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图；

在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的；

在“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；

在“串行化”隔离级别下直接用加锁的方式来避免并行访问。

在 MySQL 中，不同时刻启动的事务会有不同的一致性视图read-view，并且每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，这就意味着同一条数据在数据库中维护了多个版本，就是数据库的多版本并发控制（MVCC），我们后续详细讨论MVCC机制。

从图中可以看到每种隔离级别可以解决的问题，我们可以看出可重复隔离级别下幻读是没有解决的，而且即便是加行锁也解决不了问题，我们上面说了幻读问题说的是新增删除造成的问题，而无论是可重复读隔离级别还是行锁操作的对象都是当前行，所以幻读问题需要其他的方式解决。

注意：长事务会造成回滚日志不断增大，会有空间占用剧增的风险，尽量不要使用长事务。

幻读的解决

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了，间隙锁是开区间。 间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。 间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

简单总结

解决上述问题其实就是依赖于mysql的MVCC机制和锁机制，我们后续分别讨论。