Rails - May 2006 - validates_uniqueness_of and create atomicity

Hi folks,

I have a question regarding validates_uniqueness_of, and similar
before-filter-like events attempting to guarantee some DB state prior
to modification.
>From my reading of ActiveRecord, validate_uniqueness_of appears to
cause a SELECT on the underlying table attempting to ensure that a
record with ID(s) specified in the validates_uniqueness_of statement
is not already present, prior to the INSERT (or UPDATE).  However, in
between the SELECT and INSERT, an offending record might appear.

example race condition scenario:

A) dispatcherInstance1 is to perform a create on entity Foo, which has
a validates_uniqueness_of

B) dispatcherInstance2 is to perform a create on entity Foo with the
same parameters as in A

C) dispatcherInstance1 enters the validation code and performs its
SELECT, it decides everything is OK

D) dispatcherInstance2 enters the validation code and performs its
SELECT, it decides everything is OK

E) dispatcherInstance1 succeeds with its INSERT

F) dispatcherInstance2 either raises exception because DB implements
uniqueness property as well or inserts duplicate record


So I guess the question is what is the point of
validates_uniqueness_of if it doesn''t guarantee uniqueness?  I had
imagined it would do something "clever" like attempt the insert, trap
an exception if it occurs, and attempt to subsequently verify if the
exception is the result of a violation of the uniqueness condition
specified.  But clearly this is not what is happening.

Regards,
--
Bosko Milekic <bosko.milekic@gmail.com>

To further follow up on this: what I find myself doing now in order to
completely guarantee atomicity for the check-and-insert, is simply
bypass the check (i.e., not enforce validates_uniqueness_of) and catch
exceptions raised due to the insert failing (realizing that such
exceptions are probably the result of a duplicate key violation on the
unique index in my [PostgreSQL] DB).  This completely guarantees that
my application doesn''t barf up (with a wierd internal server error)
but fails "politely" should the admittedly rare race condition
scenario below actually occur.

--
Bosko Milekic <bosko.milekic@gmail.com>

On 5/10/06, Bosko Milekic <bosko.milekic@gmail.com>
wrote:
> Hi folks,
>
> I have a question regarding validates_uniqueness_of, and similar
> before-filter-like events attempting to guarantee some DB state prior
> to modification.
>
> From my reading of ActiveRecord, validate_uniqueness_of appears to
> cause a SELECT on the underlying table attempting to ensure that a
> record with ID(s) specified in the validates_uniqueness_of statement
> is not already present, prior to the INSERT (or UPDATE).  However, in
> between the SELECT and INSERT, an offending record might appear.
>
> example race condition scenario:
>
> A) dispatcherInstance1 is to perform a create on entity Foo, which has
> a validates_uniqueness_of
>
> B) dispatcherInstance2 is to perform a create on entity Foo with the
> same parameters as in A
>
> C) dispatcherInstance1 enters the validation code and performs its
> SELECT, it decides everything is OK
>
> D) dispatcherInstance2 enters the validation code and performs its
> SELECT, it decides everything is OK
>
> E) dispatcherInstance1 succeeds with its INSERT
>
> F) dispatcherInstance2 either raises exception because DB implements
> uniqueness property as well or inserts duplicate record
>
>
> So I guess the question is what is the point of
> validates_uniqueness_of if it doesn''t guarantee uniqueness?  I had
> imagined it would do something "clever" like attempt the insert,
trap
> an exception if it occurs, and attempt to subsequently verify if the
> exception is the result of a violation of the uniqueness condition
> specified.  But clearly this is not what is happening.
>
> Regards,
> --
> Bosko Milekic <bosko.milekic@gmail.com>

On May 10, 2006, at 8:46 AM, Bosko Milekic wrote:
> Hi folks,
>
> I have a question regarding validates_uniqueness_of, and similar
> before-filter-like events attempting to guarantee some DB state prior
> to modification.
>
>> From my reading of ActiveRecord, validate_uniqueness_of appears to
> cause a SELECT on the underlying table attempting to ensure that a
> record with ID(s) specified in the validates_uniqueness_of statement
> is not already present, prior to the INSERT (or UPDATE).  However, in
> between the SELECT and INSERT, an offending record might appear.
Yes, it should obtain a write lock on the entire table to ensure its
uniqueness check is valid. Clearly this is a poor idea.

> So I guess the question is what is the point of
> validates_uniqueness_of if it doesn''t guarantee uniqueness?  I had
> imagined it would do something "clever" like attempt the insert,
trap
> an exception if it occurs, and attempt to subsequently verify if the
> exception is the result of a violation of the uniqueness condition
> specified.  But clearly this is not what is happening.
That sounds good. Would you care to pursue an implementation?

jeremy

On 5/10/06, Jeremy Kemper <jeremy@bitsweat.net> wrote:
[...]
> > So I guess the question is what is the point of
> > validates_uniqueness_of if it doesn''t guarantee uniqueness? 
I had
> > imagined it would do something "clever" like attempt the
insert, trap
> > an exception if it occurs, and attempt to subsequently verify if the
> > exception is the result of a violation of the uniqueness condition
> > specified.  But clearly this is not what is happening.
>
> That sounds good. Would you care to pursue an implementation?
>
> jeremy
Yes, I''ve thought about a possible implementation.  I''ll whip
up a
diff and propose it.  I decided to not touch the current
validates_uniqueness_of but implement it in the form of a
guarantee_uniqueness_of.  It''s a little tricky because it is difficult
to determine that the exception raised by the create_or_update in
ActiveRecord::Base''s save method is due to a unique index violation --
what I do is attempt a SELECT following the exception to verify that
the first one was indeed due to a unique record violation.  It works
but needs a little bit of cleaning up.

--
Bosko Milekic | bosko.milekic@gmail.com | bmilekic@FreeBSD.org

On 5/10/06, Bosko Milekic <bosko.milekic@gmail.com>
wrote:
> On 5/10/06, Jeremy Kemper <jeremy@bitsweat.net> wrote:
> [...]
> > > So I guess the question is what is the point of
> > > validates_uniqueness_of if it doesn''t guarantee
uniqueness?  I had
> > > imagined it would do something "clever" like attempt
the insert, trap
> > > an exception if it occurs, and attempt to subsequently verify if
the
> > > exception is the result of a violation of the uniqueness
condition
> > > specified.  But clearly this is not what is happening.
> >
> > That sounds good. Would you care to pursue an implementation?
> >
> > jeremy
Hrm, it''s been a while since we last discussed this.

I did not forget.  In fact, I have a local implementation in the form
of an unpublished plugin I named
"validates_uniqueness_of_unique_fields", but I''m not
perfectly happy
with it because it is too dependent on PostgreSQL being used as the db
connection adapter (unfortunately there is no way to detect in a
standard fashion whether the StatementInvalid exception raised by the
save is actually due to a duplicate key violation).  It works, though,
and I use it in conjunction with validates_uniqueness_of since I am
terribly paranoid (i''ve inline-included a code snippet below if you
are curious).

I continue to use this in conjunction with UNIQUE db constraints in
Postgres because the fact is that checking for uniqueness is not a
validation in the classic sense (it is only partly a validation) --
unlike other validations, a uniqueness guarantee must be checked
against not only client-provided data but the DB backing store, which
makes it significantly more involved than, say,
validates_confirmation_of, or any other validations for that matter.

<-- snippet -->
  module ValidatesUniquenessOfUniqueFields

    def self.included(base)
      base.extend ValidatesUniquenessOfUniqueFieldsMacro
    end

    module ValidatesUniquenessOfUniqueFieldsMacro
      def validates_uniqueness_of_unique_fields(msg = nil)
        unless
method_defined?(:__old_save_validates_uniqueness_of_unique_fields)
          alias_method :__old_save_validates_uniqueness_of_unique_fields, :save
          define_method :save do
            begin
              __old_save_validates_uniqueness_of_unique_fields
            rescue ActiveRecord::StatementInvalid
              error_msg = $!.to_s
              if error_msg.match(/duplicate/) and error_msg.match(/unique/)
                msg = "Record clashes with one or more unique fields"
if msg.nil?
                errors.add_to_base(msg)
                false
              else
                raise $!
              end
            end
          end
        end
      end
    end

  end
<-- snippet -->

-- 
Bosko Milekic <bosko.milekic@gmail.com>