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).

Answer 2

@Yule answer was very helpful , it explained Why this happened and that adding database level constraint will prevent it.

The docs says, this will throw an ActiveRecord::RecordNotUnique exseption which i don't want the users to see ofcourse, so here is exactly how to apply the workaround suggested by @Yule:

1. Add an index on the attribute

   add_index(:tickets, :ticket_number, :unique => true)

2. Adjust the controller#create action to catch the db exception:

def create
 @ticket = Ticket.new(ticket_params)
 if @ticket.save
   flash[:success] = 'Ticket was successfully created.'
 else
   render action: 'new'
 end
 #catch the exception
 rescue ActiveRecord::RecordNotUnique
   flash[:danger] = 'The ticket number you entered is already taken !'
   render action: 'new'
end

Same should be done for controller#update action ofcourse