DB (MySQL) question

rubyonrails-talk
1

I have a bit code that looks all records in an array of unique keys, creates new records for all keys not found, and updates the records of the found records. There is a race condition, if another request tries the same thing at the same time with an overlapping array of keys, the inserts may fail because the other request did its inserts between the find and the inserts.

My current solution is to just serialize all these requests, by that doesn't scale very well. The only locking solution I can see is to lock the whole table. I suppose transactions could be used, but the thought of endless retries is not pleasant. Is there a way to do an update with any missing records created with default values? Or some other solution I'm not aware of?

TIA,   Jeffrey

2

did you try INSERT with IGNORE

3

Jeffrey L. Taylor wrote:

I have a bit code that looks all records in an array of unique keys, creates new records for all keys not found, and updates the records of the found records. There is a race condition, if another request tries the same thing at the same time with an overlapping array of keys, the inserts may fail because the other request did its inserts between the find and the inserts.

My current solution is to just serialize all these requests, by that doesn't scale very well. The only locking solution I can see is to lock the whole table. I suppose transactions could be used, but the thought of endless retries is not pleasant. Is there a way to do an update with any missing records created with default values? Or some other solution I'm not aware of?

You *really* want transactions here.

TIA,   Jeffrey

Best,