Which one is correct?
With transaction 1 as Repeatable Read, other transactions can not update a row after it has been selected by transaction 1.
With transaction 1 as Repeatable Read, other transactions can update a row, but transaction 1 does not take into account those changes.
I before believed that (1) is right. But when I tested sample transactions on machine, (2) is correct. I am using MySQL version 5.1.2.re Community.
While many database systems will actually behave like your first version, for MySQL, version 2 is the expected behaviour, see the documentation about repeatable read:
This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. 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.
This means: if you select the same row, you get the same data (from the snapshot), even if the row has been deleted by another transaction. This is what the sql standard requires: it has to prevent "non-repeatable reads" (in addition to "dirty reads" of lower isolation levels), but every database system can implement it differently in details.
In MySQL, if you want to block another transaction from updating the rows in repeatable read, you need to lock them by e.g. using select ... for update. A simple select will not place a lock unless you are in serializable isolation mode.
If the database uses Multiversion concurrency control, then 2. is correct. If however it uses Two Phase locking, then 1. is correct.
