前言

聊到InnoDB的锁，经常会听到幻读，脏读，不可重复读等字段。这几个字段的含义，从语义上理解总是让人容易产生误解。本文从定义和实际例子出发，解释一下这些名词的概念和试用场景

基础知识

提到这些幻读，脏读，前提就需要了解锁和mysql隔离级别的一些基本知识，本文不再进行赘述锁的概念 Mysql的隔离级别

脏读

先来一个脏读(Dirty Read)的mysql 官方解释

An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.

This kind of operation does not adhere to the ACID principle of database design. It is considered very risky, because the data could be rolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.

划重点：data that was updated by another transaction but not yet committed
举例子：
解释例子：
- 隔离级别为 Read UnCommited,(为什么不用RC或者RR？因为RC和RR不会出现脏读)
- 事务B select 了t表中的所有数据，发现只有一个a=1
- 事务A insert在t表中insert了一条a=2 ,但是未提交
- 事务B select 了t表中的所有数据，发现有a=1和a=2两条数据诶，这个就脏读了，违反了数据库了ACID原则（那么请问，违反了哪个？？？？？），违反了隔离性（Isolation）

脏读解决方案

很简单，用RC或者RR或者Serializable隔离级别就解决了~ 那么原理是什么呢?? 后续再慢慢道来

不可重复读

不可重复读（non-repeatable reads），这个我们上wiki的解释：

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

参考链接

划重点： a row is retrieved twice and the values within the row differ between reads。翻译一下就是同一行数据，在一个事务内，重复执行两次，发现结果居然不一样
举例子（我们直接取wiki百科上的例子）:

不可重复读解决方案

RR隔离级别中的 Next-Key Lock（InnoDB）

幻读

Mysql界有名的Phantom Problem，网上很多幻读的解释是：一个事务内进行两次相同操作居然得到了不一样的结果，这个定义特别容易和脏读进行混淆。我们用Mysql官方的定义来进行解读和翻译: 来源

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

还是列一个很多地方都看到的例子：

事务A在同一个事务内部，同样的sql语句，出现了不同的结果，这里注意一个跟脏读的差别，幻读中，B事务已经commit，这个很好理解。这里注意一个跟不可重复读的差别~ ，是不是发现好像没差别？我们来看幻读的另一个解释：来自于wiki百科

A phantom read occurs when, in the course of a transaction, new rows are added or removed by another transaction to the records being read.

划重点:new rows are added or removed by another transaction
幻读解决方案： next key lock

幻读和不可重复读的区别