DB (MySQL) question

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

did you try INSERT with IGNORE

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,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]