version locking details for update ... where ...

[Andrew Nuss]

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)
commmit
else
rollback

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?

Andy