ActiveRecord uniqueness checks are 'inherently prone to race conditions"

Using this validation method 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 race 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!

Something to watch out for as you scale or build out your Rails app. This is the kind of stuff that only bites you when you get bigger.

views
7 responses
man this is such bullshit. who writes this crap? rails sucks ;)
I for one, always believed that databases should take care of data integrity itself. So I add a unique constraint on the db, also use validates_uniqueness_of before adding the record. The documentation goes on to explain how to avoid this problem anyways (by the catching the exception, etc.). I'd like to see which other framework handles this properly.
This bites our ass too.

You can add an index to the column that enforces uniqueness at the DB level. We had to do this for some of our data that's shared by a lot of users. Also, this uniqueness validation slows waaaay the hell down as you add more users if you don't have an index.

Fun stuff. Rails is full of leaky abstractions, but so are all other frameworks. I call it the framework tax.

You're right about the uniqueness checking, but what other methods would you suggest other than not using mysql
Unique index sounds like the way to go.
2 visitors upvoted this post.