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?