Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

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 thomashunter.name/blog/… you will find that ON DUPLICATE KEY update does not delete the existing row like replace into (needs to be confirmed though) –  ssmusoke Dec 17 '12 at 10:27
add comment

It looks like you need SELECT ... FOR UPDATE

Pseudo code:

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

Your Answer

 
discard

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.