3

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?

Improve this question
2
  • 1
    note that an added SELECT * FROM t1; after the FOR UPDATE one returns value 1 still. I can see why it works this way, but it is a little surprising. Commented May 5, 2021 at 1:24
  • it is expected behaviour for me Commented May 5, 2021 at 10:23

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

locks the most recent version, yes. the returning the data from that most recent version sounds like the surprise. mariadb.com/kb/en/set-transaction/#repeatable-read is a little more explicit that repeatable read only applies to non-locking statements.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.