I'm running into a strange problem with transactions. I am seeing
rare duplicate entries in the DB that should be prevented by my
validations. Here is some sample code:
class Review < ActiveRecord::Base
has_one :member
has_one :business
attr_accessor :sleep_for
def validate
validate_uniqueness_combo_of_member_and_business
sleep(sleep_for) if self.sleep_for
end
end
r = Review.new(:member_id => 1, :business_id => 4)
r.save!
=> true
I would expect the first save to lock the table and the second to fail
after 5 seconds. Instead they both succeed and I end up with bad data
in the db. It also fails if I wrap both save! calls in
Review.transaction blocks.
I could not find docs on whether validate() is within the transaction,
but I would expect it to be. Any ideas on what is wrong here?
Validations about uniqueness of things are prone to race conditions.
If you need a cast iron guarantee that can only come from the database
itself (ie unique index etc...)
The app allows an "anonymous" user to have multiple reviews of a
business, so a DB level constraint probably isn't appropriate. But
regardless, my question is about what I can/can't expect from AR with
respect to transactions/validations. If validations aren't within the
transaction they're not useful. Since I don't believe the Rails folks
are stupid, I'm trying to figure out what I'm doing wrong here.
r = Review.new(:member_id => 1, :business_id => 4)
This line does absolutely nothing at the database level. All you have
done here is make a new Ruby object.
r.sleep_for = 5
r.save!
This row create a database transaction, writes the data from the Ruby
object's attributes and ends the transaction.
=> true
#console 2 (during the 5 sec sleep)
r = Review.new(:member_id => 1, :business_id => 4)
Again the database know nothing about the creation of the Ruby object.
r.save!
Since this executes before you have saved the first record, and
validations for both records have already occurred your uniqueness
validation WILL fail and allow a duplicate to be recorded in the
database.
=> true
I would expect the first save to lock the table and the second to fail
after 5 seconds. Instead they both succeed and I end up with bad data
in the db. It also fails if I wrap both save! calls in
Review.transaction blocks.
Locking the table in this scenario would be death to scalability. What
most people do in this case is to use the validates_uniqueness_of for
convenience, but also ensure data integrity by adding the proper unique
index to the database.
A fail-and-recover scheme is what is needed here. As much as table
locking sounds like the way to go, it just isn't because it's way to
expensive.
I also understand your issue with anonymous users (especially since you
yourself have chosen to be an anonymous coward on this forum -- kidding
a little there hehe). But, that's just something you'll have to weigh
for yourself.
As Frederick has already mentioned: race conditions are a tough problem
to solve and are best done at the database layer. They are a lot more
difficult to solve at the model object layer, which is the layer where
validations are performed). This, however, does not render validations
worthless. AFAIK uniqueness validations are the only ones that suffer
from this issue.
Validations about uniqueness of things are prone to race conditions.
If you need a cast iron guarantee that can only come from the
database
itself (ie unique index etc...)
The app allows an "anonymous" user to have multiple reviews of a
business, so a DB level constraint probably isn't appropriate. But
regardless, my question is about what I can/can't expect from AR with
respect to transactions/validations. If validations aren't within the
transaction they're not useful. Since I don't believe the Rails folks
are stupid, I'm trying to figure out what I'm doing wrong here.
It should be easy to work out whether they run in a transaction or not
by looking at the log files. Most validations are just about the one
object and so couldn't care less. Even if they are it wouldn't help
your uniqueness validations (unless you're actually locking rows)