locking - mysql insert race condition

Question

How do you stop race conditions in MySQL? the problem at hand is caused by a simple algorithm:

1. select a row from table
2. if it doesn't exist, insert it

and then either you get a duplicate row, or if you prevent it via unique/primary keys, an error.

Now normally I'd think transactions help here, but because the row doesn't exist, the transaction don't actually help (or am I missing something?).

LOCK TABLE sounds like an overkill, especially if the table is updated multiple times per second.

The only other solution I can think of is GET_LOCK() for every different id, but isn't there a better way? Are there no scalability issues here as well? And also, doing it for every table sounds a bit unnatural, as it sounds like a very common problem in high-concurrency databases to me.

Answer 1

what you want is LOCK TABLES

or if that seems excessive how about INSERT IGNORE with a check that the row was actually inserted.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead.