Tell me more ×
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.
up vote 0 down vote favorite

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

1 Answer

up vote 0 down vote

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

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.