Posted over 6 years ago. Visible to the public. Repeats.

Understanding race conditions with duplicate unique keys in Rails/MySQL

validates_uniqueness_of is not sufficient to ensure the uniqueness of a value. The reason for this is that in production, multiple worker processes can cause race conditions:

  1. Two concurrent requests try to create a user with the same name (and we want user names to be unique)
  2. The requests are accepted on the server by two worker processes who will now process them in parallel
  3. Both requests scan the users table and see that the name is available
  4. Both requests pass validation and create a user with the seemingly available name
  5. You have created two (invalid) user records with the same name.

The first thing to do here is to ensure the integrity of your data. When using validates_uniqueness_of you should always have a unique key constraint on the column in your MySQL. E.g. when you are validating the uniqueness of usernames, the users#screen_name column should have a unique MySQL index.

Using unique key constraints give you a hard guarantee for the uniqueness of your values. The race condition as detailed above can no longer occur. The following will happen instead:

  1. Two concurrent requests try to create a user with the same name (and we want user names to be unique)
  2. The requests are accepted on the server by two worker processes who will now process them in parallel
  3. Both requests scan the users table and see that the name is available
  4. Worker #1 passes validation and sucessfully creates a user with the available name
  5. Worker #2 passes validation and tries to create a user with the seemingly available name. However, the MySQL adapter will report a constraint violation by raising ActiveRecord::RecordNotUnique. ActiveRecord rolls back the transaction, nothing will be saved. The user will probably see a Rails error box.

Improving the user experience

In 99% of all cases adding a uniqueness key is an adequate solution, and in any case the integrity of your data is guaranteed. There are however cases where you want to improve the user behavior (Rails error box) or reduce the number of exceptions e-mailed to your / collected by AirBrake:

High-traffic forms with a high probability of value collision
Wrap your entire save call in a Mutex, so the second form submission will report a violated uniqueness validations instead of a Rails error box.
Users who double-click buttons, thus spawning two requests all the time (Note that confusion what to single-click/double-click is extremely common among inexperienced users)
Use a Mutex as detailed above. Also embed a hidden token into your forms so you can detect a double form submission and redirect to the previously created record.

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Henning Koch
Last edit:
over 3 years ago
by Henning Koch
Keywords:
concurrency, index, semaphore
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more