ActiveRecord concurrency issue getting next sequence value and inserting record

rubyonrails-talk
1

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:

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?

2

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.