Example:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t1; // to "create shapshot". For simplicity t1 contains 1 row 1 column which contains value 1.
// another transaction updates this row and change 1 to 2 and commits.
SELECT * FROM t1; // we see no changes. As expected in repeatable read.
SELECT * FROM t1 FOR UPDATE; // i see change row. Why?
The problem i cant find explanation to such behaviour. Why locking read ignore isolation level?
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html says:
SELECT ... FOR UPDATE
For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels. 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.)
In other words, a locking read can only lock the most recent committed version of a row.