MySQL Forums
Forum List  »  InnoDB

version locking details for update ... where ...
Posted by: Andrew Nuss
Date: September 08, 2017 12:53PM

I wish to understand how optimistic locking based on a version num for a given row should be done in mysql. Assume that all my transactions are read-committed.

begin trans

select field1, field2, vers_num from mytable
where id = :myid

... save vers_num to local variable, then ...

update mytable
set field1 = field1 + :delta1, field2 = field2 + :delta2, vers_num = vers_num+1
where id = :myid and vers_num = :saved_vers_num

... get numupdated
if (numupdated == 1)

end trans

Is this transaction safe in read_committed? I.e. if two simultaneous transactions are happening, and both selects get the same vers_num, will the second tranaction that hits the update block until the first transaction commits or rolls back, and then proceed, causing it to get a numupdated count of zero and rollback?

If so, what is the internal mysql locking mechanism that handles this. If not, it seems like I either have to use read_uncommitted in some use cases, or use FOR UPDATE somehow?

Also, does a covering index of the affected fields help in any way, or is the id sufficient to ensure that only one row is locked from the table?


Options: ReplyQuote

Written By
version locking details for update ... where ...
September 08, 2017 12:53PM

Sorry, only registered users may post in this forum.

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.