Using validates_uniqueness_of in conjunction with ActiveRecord::Base#save does not guarantee the absence of duplicate record insertions, because uniqueness checks on the application level are inherently prone to racing conditions. For example, suppose that two users try to post a Comment at the same time, and a Comment‘s title must be unique. At the database-level, the actions performed by these users could be interleaved in the following manner:
User 1 | User 2 ------------------------------------+-------------------------------------- # User 1 checks whether there's | # already a comment with the title | # 'My Post'. This is not the case. | SELECT * FROM comments | WHERE title = 'My Post' | | | # User 2 does the same thing and also | # infers that his title is unique. | SELECT * FROM comments | WHERE title = 'My Post' | # User 1 inserts his comment. | INSERT INTO comments | (title, content) VALUES | ('My Post', 'hi!') | | | # User 2 does the same thing. | INSERT INTO comments | (title, content) VALUES | ('My Post', 'hello!') | | # ^^^^^^ | # Boom! We now have a duplicate | # title!This could even happen if you use transactions with the ‘serializable’ isolation level. There are several ways to get around this problem:
By locking the database table before validating, and unlocking it after saving. However, table locking is very expensive, and thus not recommended. By locking a lock file before validating, and unlocking it after saving. This does not work if you‘ve scaled your Rails application across multiple web servers (because they cannot share lock files, or cannot do that efficiently), and thus not recommended. Creating a unique index on the field, by using ActiveRecord::ConnectionAdapters::SchemaStatements#add_index. In the rare case that a racing condition occurs, the database will guarantee the field’s uniqueness.When the database catches such a duplicate insertion, ActiveRecord::Base#save will raise an ActiveRecord::StatementInvalid exception. You can either choose to let this error propagate (which will result in the default Rails exception page being shown), or you can catch it and restart the transaction (e.g. by telling the user that the title already exists, and asking him to re-enter the title). This technique is also known as optimistic concurrency control.
Active Record currently provides no way to distinguish unique index constraint errors from other types of database errors, so you will have to parse the (database-specific) exception message to detect such a case.
I’ve just contributed this documentation to docrails, so you’ll see it in Rails 2.2’s validates_uniqueness_of API documentation.
by Genís