Ensure unique column value at application level

Question

One of the models in a Rails 3.1 application I'm working on has a "code" attribute that is generated automatically when the record is created and that must be unique. The application should check the database to see if the generated code exists and, if it does, it should generate a new code and repeat the process.

I can ensure the field's uniqueness at the database level with add_index :credits, :code, :unique => true (which I am doing) and also in the model with validates_uniqueness_of, but both of these will simply return an error if the generated code exists. I need to just try again in the case of a duplicate. The generated codes are sufficiently long that duplicates are unlikely but I need to be 100% certain.

This code generation is handled transparently to the end user and so they should never see an error. Once the code is generated, what's the best way to check if it exists and to repeat the process until a unique value is found?

Answer 1

Here's a quick example, there is still technically a race condition here, though unless your seeing hundreds or thousands of creates per second it really shouldnt be a worry, worst case is your user gets a uniquness error if two creates are run in such a way that they both execute the find and return nil with the same Url

class Credit < ActiveRecord::Base
  before_validation :create_code, :if => 'self.new_record?'
  validates :code, :uniqueness => true

  def create_code
    self.code = code_generator
    self.code = code_generator until Credit.find_by_code(code).nil?
  end
end

If you absolutely needed to remove the race condition case where two creates are running in tandem and both trigger the find with the same code and return nil you could wrap the find with a table lock which requires DB specific SQL, or you could create a table that has a row used for locking on via pessimistic locking, but I wouldn't go that far unless your expecting hundreds of creates per second and you absolutely require that the user never ever sees an error, it's doable, just kind of overkill in most cases.