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

问题描述

我在 MySQL Server 5.5 上试过这个:

I tried this with MySQL Server 5.5:

1) 确保事务隔离级别是可重复读取的

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 提交之前.(这是这个实验的链接:http://www.databasejournal.com/features/mysql/article.php/3393161/MySQL-Transactions-Part-II---Transaction-Isolation-Levels.htm)

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屋！