MySQL的MVCC在各种隔离级别中发挥的作用

How does MySQL Avoid DirtyRead/Non-RepeatableRead/Phantom Read

Posted by S.L on March 10, 2019

本文通过一个例子测试MySQL的RC隔离级别下的「避免脏读」和「无法避免不可重复读」的问题,并解释MVCC在其中发生的作用

准备

设置全局和当前会话的隔离级别为RC:

set global transaction isolation level READ COMMITTED ;
set session transaction isolation level READ COMMITTED ; 

事务1: 修改一行数据,sleep一段时间后再回滚

START TRANSACTION;
 UPDATE users SET state=1 WHERE id=1;
 SELECT sleep(10);
ROLLBACK;

事务2: 直接读取当前行的数据

START TRANSACTION;
 SELECT * FROM users WHERE id=1;
COMMIT;

事务1先于事务2执行,但是发现在事务1休眠的时候,事务2很快就执行完毕并返回了结果。 结论: RC模式下读操作需要获取S锁,但是因为当前已经有了X锁,两个锁是互斥的,理论上S锁需要等待X锁释放才会获取,但是并没有发生这种情况,所以结论是读操作并没有使用S锁。 它是怎么做到的呢?

答案是多版本并发控制MVCC,可以认为它是行级锁的一个变种,但是它在很多情况下都避免了加锁操作,因此开销更低。实现了非堵塞的读操作,写操作也只需要锁定必要的行。 在InnoDb中默认的SELECT操作都是快照读,快照读不加锁,所以即使有其他事务在当前行的记录上加了X锁,也不会影响快照读,所以并发才这么高。

快速问答

RC为什么可以避免脏读?

因为未经提交的事务都是处于当前事务的read view中的,对于当前RC级别的事务来说是不可见的,所以读不到脏数据。

RC为什么避免不了不可重复读?

因为RC每次执行语句前都会重新建立一个新的read view,所以在第一个SELECT之后有新的事务提交了对某一行数据的更新操作,那么当前RC级别事务的第二个SELECT 操作是可以读到这个变更的,因为此时read view是空的。

RR为什么可以避免不可重复读?

因为RR是在事务开始的时候构建一次read view一直到事务提交之前不会重新构建,所以对于一个RR级别事务执行中也在执行更新的事务来说,后者在前者的read view中,所以无法看到它的数据。即使第二个事务提交了,对于第一个RR级别的事务来说,由于后者已经在它的read view中了,所以是看不到的。

幻读和脏读的区别?

关于幻读和不可重复读先看看来自Wikipedia的解释:

  • 不可重复读

    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 phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Simple examples:

  • User A runs the same query twice.
  • In between, User B runs a transaction and commits.
  • Non-repeatable read: The A row that user A has queried has a different value the second time.
  • Phantom read: All the rows in the query have the same value before and after, but different rows are being selected (because B has deleted or inserted some). Example: select sum(x) from table; will return a different result even if none of the affected rows themselves have been updated, if rows have been added or deleted.

RR如何避免幻读?

我的另一篇文章有详尽的讲解RR和幻读之间的故事:详细分析MySQL如何用REPEATABLE-READ解决幻读问题

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.

References

  • https://stackoverflow.com/questions/11043712/what-is-the-difference-between-non-repeatable-read-and-phantom-read

本文首次发布于 S.L’s Blog, 作者 @stuartlau , 转载请保留原文链接.