0

I have two parallel sessions/transactions, where one reads with "for update" and other with "consistent read(default)" . I want to know whether select query in session-2 blocks until session-1 is committed or executes?

session-1

START TRANSACTION ISOLAION LEVEL REPEATABLE READ;

select * from t1 for update;

session-2

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

select * from t1;

In the link here https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

mentions that other transactions are blocked from reading in certain isolation levels. Which isolations are they?

What is that meant "Old versions of a record cannot be blocked"?

 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.)
Pushparaj
  • 1,039
  • 1
  • 6
  • 26
  • *"I want to know whether select query in session-2 blocks until session-1 is committed or executes?"* For a 100% guarantee you want to need to use `START TRANSACTION ISOLAION LEVEL SERIALIZABLE` to prevent things like **dirty** and **phantom** reads from happening. – Raymond Nijland Nov 15 '19 at 10:53
  • Consider reading [this](https://en.wikipedia.org/wiki/Isolation_(database_systems)) then you have a general idea how ANSI/iSO SQL standard more or less defined transaction isolation levels.. Then reading that MySQL documentation about isolation levels also makes a bit more sense.. – Raymond Nijland Nov 15 '19 at 10:56

0 Answers0