MySQL repeatable read guarantee regarding newest data

user_1078927 0
Viewed 42

A documentation for MySQL 5.6 regarding REPEATABLE READ isolation level states:

This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

Does reverse (somewhat) guarantee also holds? I.e. does any record which was not read yet, will be the newest version of itself? What I'm actually trying to understand, is whether in MySQL there is a possibility to have a race condition in data fetched between starting a transaction and aquiring a lock (SELECT ... FOR UPDATE)?

Example:

Transaction 1                           Transaction 2
begin
                                        begin
                                        select id from shops where id = 1
                                        update shops where name = 'something new'
select id, name from shops where id = 1
(waiting for lock)
                                        ...
                                        commit
???

Is there a guarantee which tells me that in the end, in ??? place, transaction 1 will surely see name equal being to something? Source in documentation would be much appreciated (for now I've just checked manually with two db sessions that it "seems to work").

mysql
1 Answers

From the 5.6 documentation:

Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

This is not the most straightforward way to put it, but that fragment says that the old versions cannot be locked, so if anything has changed, the lock is always aquired on the newest version possible (effectively not being a "repeatable read").

user_10789270
Related