Whats the use of SELECT .. FOR UPDATE when using Repeatable Read isolation?

When you read a record under Repeatable Read, you get a read-lock, but other transactions can also get a read lock, which might prevent you from making an update later. Using FOR UPDATE informs any other transactions which request a read lock that they should wait until you're finished updating the record.

Maybe something wrong.

When you read a record under Repeatable Read without using FOR UPDATE, Mysql using Consistent Nonlocking Reads for the read. It doesn't create any lock on it.

Using FOR UPDATE will create a write lock.

---

Repeatable Read without using FOR UPDATE: read data from the snapshot established by the first read in that transaction.

Using FOR UPDATE: read the fresh snapshot. It can read the up to date data that are committed. It behaves like "READ COMMITTED" even if you are using Repeatable Read isolation level.

---

Besides,if you create a transaction A and using FOR UPDATE on one row. Like this.

BEGIN;
select * from hero where id=3 for update ;

Then you create another transaction B and do a simple read.

BEGIN;
select * from hero where id=3 ;

transaction B uses Nonlocking-Read and will not check if write lock exist on the row. It wouldn't block.