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

Question

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!

Answer 1

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!