MySQL可重复读和丢失的更新/幻像读取 [英] MySQL repeatable read and lost update/phantom reads

问题描述

我在MySQL Server 5.5上尝试过:

I tried this with MySQL Server 5.5:

1)确保事务隔离级别为repeatable_read

1) ensured that transaction isolation level is repeatable_read

2)启动shell-1，在其中启动一个事务，然后通过select读取一个值

2) started shell-1, started a transaction in it, then read a value through select

3)启动shell-2，在其中启动一个事务，然后通过select读取相同的值

3) started shell-2, started a transaction in it, then read the same value through select

4)在shell-1中，将值更新为value + 1并提交

4) in shell-1, updated the value to value + 1 and committed

5)在shell-2中，将值更新为value + 1并提交

5) in shell-2, updated the value to value + 1 and committed

该值丢失了其更新之一，仅增加了1.

The value lost one of its updates and was incremented only by 1.

现在，据我所知，RR使用共享的读锁和排他的写锁，这意味着在上面的#4和#5中，事务应该是死锁的，但这并没有发生.

Now, as I understand it, RR uses shared read locks and exclusive write locks, which means that in #4 and #5 above, the transactions should have dead-locked, but that did not happen.

所以我对RR的理解是错误的，或者MySQL以不同的方式实现了RR.那是什么?

So either my understanding of RR is faulty, or MySQL implements RR in a different manner. So what is it?

通过类似的实验，还确认了RR事务(t1)不会看到另一个RR事务(t2)插入同一表中的行，即使它在t2提交后仍对该表进行了另一次选择，并且在t1提交之前. (以下是此实验的链接:

through a similar experiment, also confirmed that an RR transaction (t1) does not see rows inserted into the same table by another RR transaction (t2), if it does another select on that table even after t2 has committed and before t1 committing. (Here's the link to this experiment: http://www.databasejournal.com/features/mysql/article.php/3393161/MySQL-Transactions-Part-II---Transaction-Isolation-Levels.htm)

这是否意味着MySQL的RR也处理幻像读取?

Does it mean that MySQL's RR takes care of phantom reads also?

推荐答案

MySQL确实不符合Repeatable Read.您可以通过使用可序列化的隔离级别或在选择后放置FOR UPDATE来强制执行此操作(请参见下面的示例).然后将实现所需的行为. 关于幻像读取，MySQL实际上比必要的要严格...

MySQL does not conform to Repeatable Read really. You can force it to do by using isolation level serializable or by putting an FOR UPDATE after your selects (look at the example below). Then the desired behaviour will be achieved. Regarding phantom reads, MySQL is actually stricter than necessary...

SELECT value FROM table WHERE id = 7 FOR UPDATE;

这篇关于MySQL可重复读和丢失的更新/幻像读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！