# DB (MySQL) question

**URL:** <https://www.ruby-forum.com/t/db-mysql-question/182735>\
**Category:** Rails\
**Created:** [February 2, 2010, 2:26am UTC](https://www.ruby-forum.com/t/db-mysql-question/182735 "2010-02-02T02:26:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [February 2, 2010, 2:26am UTC](https://www.ruby-forum.com/t/db-mysql-question/182735/1 "2010-02-02T02:26:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [February 2, 2010, 6:29am UTC](https://www.ruby-forum.com/t/db-mysql-question/182735/2 "2010-02-02T06:29:31Z")

</div>

did you try INSERT with IGNORE

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [February 2, 2010, 7:30am UTC](https://www.ruby-forum.com/t/db-mysql-question/182735/3 "2010-02-02T07:30:05Z")

</div>

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid