I tried this with MySQL Server 5.5:
我尝试使用MySQL Server 5.5:
1) ensured that transaction isolation level is repeatable_read
1)确保事务隔离级别是repeatable_read
2) started shell-1, started a transaction in it, then read a value through select
2)启动shell-1,在其中启动一个事务,然后通过select读取一个值
3) started shell-2, started a transaction in it, then read the same value through select
3)启动shell-2,在其中启动一个事务,然后通过select读取相同的值
4) in shell-1, updated the value to value + 1 and committed
4)在shell-1中,将值更新为值+ 1并提交
5) in shell-2, updated the value to value + 1 and committed
5)在shell-2中,将值更新为值+ 1并提交
The value lost one of its updates and was incremented only by 1.
该值丢失了其中一个更新,并且仅增加1。
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使用共享读锁和独占写锁,这意味着在上面的#4和#5中,事务应该已经死锁,但是没有发生。
So either my understanding of RR is faulty, or MySQL implements RR in a different manner. So what is it?
所以要么我对RR的理解有问题,要么MySQL以不同的方式实现RR。那是什么?
EDIT: 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)
编辑:通过类似的实验,还确认RR事务(t1)没有看到行被另一个RR事务(t2)插入到同一个表中,如果它在该表上做了另一个选择,即使在t2提交之后和t1提交之前。 (以下是此实验的链接:http://www.databasejournal.com/features/mysql/article.php/3393161/MySQL-Transactions-Part-II---Transaction-Isolation-Levels.htm)
Does it mean that MySQL's RR takes care of phantom reads also?
这是否意味着MySQL的RR也会处理幻像读取?
1 个解决方案
#1
7
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...
MySQL真的不符合Repeatable Read。您可以通过使用可序列化的隔离级别或在选择后放置FOR UPDATE来强制执行此操作(请参阅下面的示例)。然后将实现期望的行为。关于幻像读取,MySQL实际上比必要更严格......
SELECT value FROM table WHERE id = 7 FOR UPDATE;
#1
7
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...
MySQL真的不符合Repeatable Read。您可以通过使用可序列化的隔离级别或在选择后放置FOR UPDATE来强制执行此操作(请参阅下面的示例)。然后将实现期望的行为。关于幻像读取,MySQL实际上比必要更严格......
SELECT value FROM table WHERE id = 7 FOR UPDATE;