sql - How to avoid race condition in MySQL

Question

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...

1. Create a new row in the entries table:

   INSERT INTO entries ( name, email ) VALUES ( 'Foo Bar', '[email protected]' );

2. Find out if Mr Bar is a winner by checking a time-sensitive prizes table:

   SELECT id FROM prizes WHERE various_time_conditions = 'met' AND id NOT IN ( SELECT prize_id FROM entries );

3. If he's a winner, update his entry row accordingly:

   UPDATE entries SET prize_id = [prize id] WHERE id = [entry id];

As each prize can only be given out once, I need to eliminate any possibility of a race condition where another process can query the prizes table and update the entry table between steps 2 and 3 above.

I've been doing some research and have found a load of information about transactions (all my tables use InnoDB) and using MySQL's SELECT ... FOR UPDATE syntax but I'm confused as to which is the most suitable solution for me.

Answer 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 = 1 FOR UPDATE

Then set the availability if you do assign the prize:

UPDATE prizes SET available = 0 WHERE 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.