MySQL Forums
Forum List  »  InnoDB

Locking multiple rows in repeatable read mode
Posted by: Marko Vukovic
Date: March 31, 2013 04:09PM

If I understand correctly how repeatable read works, on the first select in a trasaction the database creates a snapshot of its current state and with few exceptions like "insert into ... select ..." the transaction does not see any changes hapenning outside of it.

In my situation I have to lock two or more arbitrary rows identified discretly by their primary key values. If I lock them independently like this:

select * from t where pk=3 for update;
select * from t where pk=8 for update;

another transaction may squezee between these two statements and do something the first transaction would not be aware of. I cannot process the records independently with a commit between them because this has to be dealt with in "all or nothing" manner. Is the only solution to lock both records at the same time like this:

select * from t where pk in (3,8) for update;

I am not particularly fond of that idea because I do not like building the statements of arbitrary lentght like this one dynamically. Also, would it honour the order in which it locks them so I can avoid a deadlock by sorting them?

Thank you for your response in advance.

Options: ReplyQuote

Written By
Locking multiple rows in repeatable read mode
March 31, 2013 04:09PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.