MySQL Race Conditions

Question

Does this cause a race condition with MySQL (InnoDB):

1. Start Transaction.

2. Try to get record.

3. If record doesn't exist, return.

4. If record exists, delete it and add a log entry saying that is was deleted.

5. End Transaction (commit/rollback).

Is it possible for another process to start just before the delete step in 2b, detect the presence of the record and then have both processes enter item delete entries into the log?

Are there any precautions that I need to take?

Thanks.

Answer 1

Yes, it's possible for another transaction to check the table after you've read it.

Worse yet, because of how transactions work, even after you delete the row, any new transactions that start will see the row because you haven't yet committed the delete.

SELECT ... FOR UPDATE is one way to prevent it.

LOCK TABLE tablename is another.

Unfortunately, since you're using an ORM, I couldn't say whether it has the ability to do either of these.

Answer 2

Start Transaction.

Delete record /* using the very same criteria you use for 'try to get record' */

if response indicates record was indeed deleted, add a log entry.

End Transaction (commit/rollback).

No more race condition.

Answer 3

Journeyman Programmer, I believe, has the correct solution. Since you've indicated you are using a broken ORM tool (one that will not allow you to query for update) I'd suggest that you move your INSERT into the log table into a trigger on the delete operation so you will avoid the duplicate entry.

Answer 4

Use 'select for update' at step 2. Only one process will be able to get a lock on the row thus avoiding the scenario you described.