How To Avoid Race Condition In Mysql

I've got a potential race condition in an application I'm developing, which I'd like to account for and avoid in my querying. To summarise the application flow... Create a new row

Solution 1:

You're going to want to lock the prize record. So add some availability flag on the prizes table (perhaps with a default value) if you're not going to use something like a winner_id. Something like this:

SELECT id FROM prizes WHERE ... AND available =1FORUPDATE

Then set the availability if you do assign the prize:

UPDATE prizes SET available =0WHERE id = ...

You'll need to wrap this inside a transaction of course.

Make sure that every time you check to see if the prize is available, you add AND available = 1 FOR UPDATE to the query because a SELECT without the FOR UPDATE is not going to wait for a lock.