MySQL repeatable read and lost update/phantom reads

Question

I tried this with MySQL Server 5.5:

1) ensured that transaction isolation level is repeatable_read

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

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

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

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

The value lost one of its updates and was incremented only by 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.

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

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)

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

Drupelet

Have you tried serializable transactions? dev.mysql.com/doc/refman/5.1/en/… – Wheeling

Yes, and it looks like MySQL serializable uses shared read locks and exclusive write locks, and hence gives a deadlock in the above case. So I'm really confused, because here's what 'JP with Hibernate' book says: "A system operating in repeatable read isolation mode permits neither unrepeatable reads nor dirty reads. Phantom reads may occur. Reading transactions block writing transactions (but not other reading transactions), and writing transactions block all other transactions." (page 456) – Drupelet

Answer 1

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;

Stypsis

thx! Could you pl. cite a reference for this so I can accept it as an answer? – Drupelet

cs.umb.edu/~poneil/iso.pdf states that lost updates are not possible in Repeatable Read. You can find the summary on the last page and the discussion on that specific anomaly somewhere in the middle (just search for "lost update"). I can't give you more references, I hope it is enough for now. – Stypsis

Just to be sure: Lost updates are in fact possible when using InnoDB with Repeatable Read due to their non-conform implementation. Is that right? – Junkie

@Junkie well, no, not really. It depends a bit what you mean with "lost update"; it will not be lost, just overwritten which is fine behaviour for a database; the difference is if you look at your data before updating it, you have to use "for update" to really be sure that it is not changed before you do. That behaviour is not called "lost update". – Stypsis

@Stypsis Thanks for the clarification. I'm still having a bad feeling about this. Does relational algebra know this "for update"-construct? It was my understanding that a schedule like R_1(value), R_2(value), W_2(value), W_1(value) represents a lost update since transaction 1 practically reverts the changes made by transaction 2. – Junkie