mysql脏读解决方案_MySql数据库隔离等级与脏读问题

数据库事务四个特性(ACID)，原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。本文聚焦于隔离性(Isolation)。

1、数据库隔离等级

数据库有四档隔离等级：

隔离级别脏读非重复读Phantom read中文名称

READ_UNCOMMITTED

allowed

allowed

allowed

读未提交

READ_COMMITTED

prevented

allowed

allowed

读已提交

REPETABLE_READ

prevented

prevented

allowed

可重复读

SERIALIZABLE

prevented

prevented

prevented

序列化

常见数据库默认隔离等级：

DatabaseDefault isolation Level

Oracle

READ_COMMITTED

MySQL

REPETABLE_READ

Microsoft SQL Server

READ_COMMITTED

PostgreSQL

READ_COMMITTED

DB2

CURSOR STABILITY (a.k.a READ_COMMITTED)

(1)读未提交 READ UNCOMMITTED

读未提交是最低的隔离等级，一个事务能够读到其他事务尚未提交的修改，存在脏读问题。

(2)读已提交 READ COMMITTED

比读未提交高一个等级。一个事务未提交或者回滚时，对这个事务涉及的数据加上了读锁和写锁。写锁在这个事务结束时才释放；读锁在这个事务完成SELECT操作之后立即释放。由于释放读锁时，这个事务还未提交，此时如果其他事务来读取这个值，会导致不可重复读问题和幻读问题。

在“读已提交”隔离等级下，能够确保读到的数据，都是经过提交(COMMITTED)的数据，不可能是某种临时性的数据，所以避免了脏读问题。但不能够保证当重复读取这个数据时，这个数据不变，即不可重复读。

(3)可重复读 REPEATABLE READ

一个事务未提交或者回滚时，对这个事务涉及的数据加上了读锁和写锁。读锁和写锁在这个事务结束时才释放。这样就保证了可重复读，即在一个事务中，无论何时读取数据，数据都不变，除非这个事务本身改变了这个数据。

但是，当一个事务对所有涉及的数据加上了读锁和写锁之后，如果另一个事务此时插入了一条数据也满足第一个事务的筛选条件，这样就会导致最后结果不满足第一个事务，而是两个事务的混合结果，即幻读问题。

举个幻读问题的例子：

Transaction 1Transaction 2

/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;

/* Query 2 */ INSERT INTO users(id,name,age) VALUES ( 3, 'Bob', 27 ); COMMIT;

/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30; COMMIT;

(4)序列化 SERIALIZABLE

这是最高的隔离等级。在事务完成之前，对这个事务涉及的数据加上读锁和写锁，而且加上了范围锁(range-locks)，使得当这个事务未提交时，其他事务无法插入满足这个事务筛选条件的新数据。这样避免了幻读问题。

所谓序列化是指，在SERIALIZABLE这个隔离等级下，任何SQL事务的执行，都像是序列化执行的一样。所谓序列化执行是指，一个事务完成之后，才开始执行下一个事务。(实际上，在SERIALIZABLE隔离等级下，对同一张表的访问可以是并发的，只要这些并发的操作没有触发读锁和写锁或者触发范围锁)

2、脏读问题与解决

(1) 时序图如下：

说明：事务A和事务B同时操作同一条数据，如果事务A修改完这条数据之后，还未提交/回滚，事务B就读取了这条数据，最后事务A回滚了，事务B读取到的数据是脏数据。这就是脏读。

如果事务A最后一步是COMMIT(提交)，这就不是脏读问题了。因为事务B读取出来的数据与数据库中的数据一致。脏读问题只出现在事务A最后回滚的情况。

之前想到这里的时候就开始想不通了，为什么事务A最后只能是ROLLBACK，这里多说几句：

例子中，事务B读出来的数据是事务A开始之前的数据，这样能防止万一A回滚了，B读出来的就是脏数据。如果事务A最后提交了(COMMITTED)，此后B再次读了一下这个数据，会发现这个数据变了，这就造成非重复读的问题。

(2)脏读问题解决方案READ COMMITTED

解决脏读问题，需要开启mysql的READ COMMITTED隔离等级。

开启两个terminal，连接同一个数据库表，分别执行：

set global transaction isolation level READ COMMITTED ;

set session transaction isolation level READ COMMITTED ;

然后两个窗口分别执行如下语句：

START TRANSACTION;

UPDATE student SET age=111 WHERE id=2;

SELECT sleep(10);

ROLLBACK;

和

START TRANSACTION; SELECT * FROM student WHERE id=2; COMMIT;

事务A执行结果：

事务B执行结果：

分析：从两个窗口的执行结果可以看出，事务A还未执行完毕的时候(还没有执行ROLLBACK时)，事务B已经执行完毕。说明如同前文讲的，在READ COMMITTED隔离等级下，读锁并不是等事务commit/rollback之后才释放，而是执行完select for update之后立马释放的。

问题：既然事务A已经修改了数据，事务B是如何读取到事务A执行update之前的数据的？

答案：Innodb采用了MVCC多版本并发控制机制，Innodb运行过程中，存在redo_log 和 undo_log，undo_log记录数据修改前的值，用于rollback；redo_log记录修改行为，如果数据库发生异常，导致当前数据丢失，可以通过redo_log恢复。上边这个例子中，事务A生成了undo_log，事务B读取数据时，由于事务A尚未提交，因此事务B读取到的是undo_log中的数据。