mysql - Rails validate uniqueness fails on concurrent inputs

Question

I have a Ticket model with basic uniqueness validation on a string attribute ticket_number

validates :ticket_number, uniqueness: true

Here is the input code for the ticket_number

<%= f.number_field :ticket_number, :value => @ticket.ticket_number || (Ticket.exists? ? Ticket.maximum(:ticket_number).next) : 1 , :class => 'form-control' %>

The validation seems to work as expected, however when we deployed the app and probably more than one users were entering tickets at the same time, which shouldn't cause an issue (right?) we found duplicated records (same ticket number) in the database(MySQL).

It only happened once in a total of 600+ tickets so far but why and how to prevent that from happening ?

Answer 1

This is very uncommon and you're probably very unlucky that it has, it is possible.

Read: https://github.com/rails/rails/blob/master/activerecord/lib/active_record/validations/uniqueness.rb#L165

Consider the following: User A submits form

• User A submits form
• Rails checks database for existing ID for User A- none found
• User B submits form
• Rails checks database for existing ID for User B- none found
• Rails Saves user A record
• Rails saves user B record

All this has to happen within milliseconds but it is technically possible.

I'd suggest adding a constraint at the database level (primary key).