Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) statements. What I fear is a possible concurrent access to data causing only INSERTs and no UPDATEs. Is LOCK Table WRITE the only solution?

share|improve this question
If i understand correctly you want to avoid race conditions on InnoDB? – Raymond Nijland Nov 12 '13 at 19:00

If this table has no other foreign keys attached to it you can try INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO (this one deletes an existing row and inserts a new one)

This would save you having to select first then insert or update, however it requires that you have a unique key to base the checks on

share|improve this answer
Thanks ssmusoke! The solution you adviced is perfect for what I described, but I just noticed that my situation is a bit more complicated. I have a table that has a PK and the field checked by the SELECT. Based on the result of the select i have to - create a new row on this table and on the referenced tables OR - create only rows in the referenced tables. Sorry for giving a misleading description – Danilo Dec 17 '12 at 9:50
If you are using an ORM (object relational mapper) in your application this will be done automatically. If you read here… you will find that ON DUPLICATE KEY update does not delete the existing row like replace into (needs to be confirmed though) – Stephen Senkomago Musoke Dec 17 '12 at 10:27

It looks like you need SELECT ... FOR UPDATE

Pseudo code:

SELECT some_column INTO @value FROM table_name WHERE ... FOR UPDATE;
IF @value = 'some value' THEN
  INSERT INTO table_name VALUES(...);
INSERT INTO referenced_table VALUES(...);
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.