Is incrementing a field in MySQL atomic?

Question

I'm making a web site where I would like to increment a counter in a standard MyISAM table.

Simplified example:

UPDATE votes SET num = num + 1;

Will this cause problems if multiple connections are doing the same query, or will MySQL take care of it and lock the table or something to make sure that there are no conflicts?

Answer 1

The write is atomic but an increment also requires a read. So the question is: Are you sure the read is safe, in other words, are you sure another thread doing the increment will not end up with the same value to be incremented? I have doubts. The 100% correct way of doing this would be.

-- begin transaction here

select counter from myCounters where counter_id = 1 FOR UPDATE;

-- now the row is locked and nobody can read or modify its values

update myCounters set counter = ? where id = 1;

-- set ? to counter + 1 programmatically

commit; -- and unlock...