Transient Mysql::Error: Duplicate entry on a high-traffic site - any ideas?

In my hoptoad logs I will periodically see

Mysql::Error: Duplicate entry 'XXXX' for key 'YYY'

This happens for most of my models, about 6 in all, and I will see this error once every few hours on a site doing about 5k requests/minute according to newrelic.

I am doing an ActiveRecord.find_or_initialize_by in each of these cases. It's possible, but unlikely, that this is from a client in the field doing two simultaneous posts of the same data, because these are mobile clients and the codepath doesn't really lend itself to that (ie, this isn't a client clicking a submit button twice quickly).

Is there开发者_运维技巧 a known issue with find_or_initialize_by? Is it possible my mysql instance (Amazon RDS) is every so often just flaking out (though I would expect it to raise an exception in that case vs. returning no data)...

Moreover, is there a better way to be inserting records? If the record exists, I generally am only updating its updated_at field.

Thanks!

This is most likely occurring because "check validity then insert" isn't an atomic operation. There's no guarantee that someone else can't insert a row with the same value for a unique column in between the validity check and the insert.

The official docs mention this, but only in passing, and they really don't explain it very well. Rails Warts has a much better page on the problem.

And as it sounds like you're backing the uniqueness condition up with a unique index in your DB, you're already doing what you can to prevent it on the DB side. And I'm not sure if find_or_initialize_by / ON DUPLICATE KEY UPDATE is a good idea or not - it depends on what your users are editing, and the security consequences of them editing something they didn't necessarily intend to.

Hope this helps!

I have this site sinograms.com. I 'm screen scraping news sites to categorize Chinese Characters through frequency and I've found the same stuff your coming up with.

I've gone through many stages. The current one up is just the last test and it only has a few million indexes. They duplicates seem to come up quickly; i know this because I only find duplicates of Chinese Characters that are very common or indexed frequently in other words.