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.

Answer 2

It seems to me you should have a unique index on your id column, so a repeated insert would trigger an error instead of being blindingly accepted again.

That can be done by defining the id as a primary key or using a unique index by itself.

I think the first question you need to ask is why do you have many threads doing the exact SAME work? Why would they have to insert the exact same row?

After that being answered, I think that just ignoring the errors will be the most performant solution, but measure both approaches (GET_LOCK v/s ignore errors) and see for yourself.

There is no other way that I know of. Why do you want to avoid errors? You still have to code for the case when another type of error occurs.

As staticsan says transactions do help but, as they usually are implied, if two inserts are ran by different threads, they will both be inside an implied transactions and see consistent views of the database.