Find_or_create race conditions

Question

I try to use ActiveRecord find_or_create_by_*column* , but I get errors from Postgres letting me know that it sometimes does not find the model and tries to insert it anyway. It is very important that this table is unique, so I added the attribute :unique => true to transfer it so that Postgres knows that I am serious about this.

And, a failure:

ActiveRecord::StatementInvalid: PGError: ERROR: duplicate key value violates unique constraint "index_marketo_leads_on_person_id" DETAIL: Key (person_id)=(9968932) already exists. : INSERT INTO "marketo_leads" ("mkt_person_id", "synced_at", "person_updated_at", "person_id") VALUES(NULL, NULL, '2011-05-06 12:57:02.447018', 9968932) RETURNING "id"

I have such models:

 class User < AR::Base has_one :marketo_lead before_save :update_marketo_lead def update_marketo_lead if marketo_lead if (User.marketo_columns & self.changes.keys).any? marketo_lead.touch(:person_updated_at) end elsif self.id marketo_lead = MarketoLead.find_or_create_by_person_id(:person_updated_at => Time.now, :person_id => self.id) end end end class MarketoLead belongs_to :user, :foreign_key => 'person_id' end 

The second model is used to link our user accounts to the Marketo email server and save the last time records when certain user fields were changed so that we can move the changed records in batch background tasks.

I can’t think of any reason for this callback, update_marketo_lead to fail, except for some kind of racing condition that I cannot imagine.

(please ignore the horror of the “user” using the primary key with the “man”) (using Rails 2.3.11, Postgres 9.0.3)

Answer 1

It is possible that when matching find_or_create, the person_id association was not found, so the creation logic was used, however, it is possible that between find_or_create and the actual user.save, another request was able to complete saving the transaction, and at that moment your database restriction caused this exception.

What I would recommend is to catch the StatementInvalid exception and retry the save (up to a finite number of times ...

 begin user.save! rescue ActiveRecord::StatementInvalid => error @save_retry_count = (@save_retry_count || 5) retry if( (@save_retry_count -= 1) > 0 ) raise error end 

Note. This should be done wherever you try to save the user. All callbacks and checks are performed as part of the save! deal

PS Im assuming your version of rails supports transactions :) In Rails 3, saving it is unnecessary to wrap it! in a transaction because it already uses one inside

Answer 2

I find this inside the sidekick job, which retries and repeatedly repeats the error and ultimately clears up. I'm not sure if this is a race condition from another request, or it will be very rare and will happen only once or twice, but not 11 times in a row, as I see it. The best explanation I found is in the blog post here . The bottom line is that postgres stores the internally stored value to increase the primary key, which is somehow messed up. This is true for me, because I am setting the primary key, and not just using the increased value, so this may have happened. The solution from the comments in the link above seems to be to call ActiveRecord::Base.connection.reset_pk_sequence!(table_name)

I still cannot verify this because I could not reproduce the problem, but my fix changed from Vladimir's solution above:

 begin user.save! rescue ActiveRecord::StatementInvalid => error @save_retry_count = (@save_retry_count || 1) ActiveRecord::Base.connection.reset_pk_sequence!(:user) retry if( (@save_retry_count -= 1) >= 0 ) raise error end 

So, if this does not fix it on the first try, I will see the error raised