Rails - Sep 2011 - ActiveRecord concurrency issue getting next sequence value and inserting record

Have an app using an old version of Rails (2.3.2). There are a handful
of servers running the application (and other applications) in
passenger. When a model is added at the same time on two servers,
sometimes one model that was just added by a user on the first server
causes another comment being added by a user on the second server to
fail.

Whenever a new model is added, it first gets the id for the object to
add by selecting the next sequence number, however it isn''t doing this
and the insert as a transaction, nor is it having the database
autogenerate ids. Assuming the model is called Foobar, then this is
what it looks like it is doing:

  SQL (10.7ms)   select foobars_seq.nextval id from dual
  Foobar Create (40.2ms)   INSERT INTO foobars (..., id, ...)
VALUES(..., 13981, ...)
  Writable Large Object (75.4ms)   SELECT somefield FROM foobars WHERE
id = 13981 FOR UPDATE
Redirected to http://localhost:3000/...
Completed in 544ms (DB: 410) | 302 Found [http://localhost/...]

I have read about a few solutions to this including either using
version numbers on the models or having it raise an exception and then
doing a retry if it didn''t work.

Is there a way instead to easily have it wrap the selection of the
next sequence value and the insert into a SQL transaction?

Or even better would be to have the DB autopopulate the id via the
sequence. We''re using Oracle, and I found the following where Niranjan
Sarade monkeypatches OracleEnhanced Adapter''s next_sequence_value
method to allow set_sequence_name ''autogenerated''. I
don''t mind
monkeypatching if needed, but I''d like to use something that is less
likely to run into issues if we upgrade Rails soon, since we have
other developers that might come in and help with the application
later:
http://niranjansarade.blogspot.com/2011/03/avoid-oracle-sequence-during.html

This seems like it has to be something thought about and solved many
times over. What would be the recommended way to handle this sort of
thing in this situation?

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Was just reminded that when select foobars_seq.nextval id from dual is
called it should increment sequence number, and it does this even
within transaction without rollback of the sequence number, so in
theory it should not be reusing the same id gotten from the sequence
since there should be no way to get the same number from the sequence.
So, nevermind- it must be some other issue and will have to setup a
test to replicate it now, which should be fun.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.