Database locking: ActiveRecord + Heroku

Question

I'm building a Sinatra based app for deployment on Heroku. You can imagine it like a standard URL shortener but where old shortcodes expire and become available for new URLs (I realise this is a silly concept but its easier to explain this way). I'm representing the shortcode in my database as an integer and redefining its reader to give a nice short and unique string from the integer.

As some rows will be deleted, I've written code that goes thru all the shortcode integers and picks the first free one to use just before_save. Unfortunately I can make my code create two rows with identical shortcode integers if I run two instances very quickly one after another, which is obviously no good! How should I implement a locking system so that I can quickly save my record with a unique shortcode integer?

Here's what I have so far:

Chars = ('a'..'z').to_a + ('A'..'Z').to_a + ('0'..'9').to_a
CharLength = Chars.length

class Shorts < ActiveRecord::Base
  before_save :gen_shortcode
  after_save  :done_shortcode

  def shortcode
    i = read_attribute(:shortcode).to_i

    return '0' if i == 0
    s = ''
    while i > 0
      s << Chars[i.modulo(CharLength)]
      i /= 62
    end
    s
  end

  private
  def gen_shortcode
    shortcode = 0
    self.class.find(:all,:order=>"shortcode ASC").each do |s|
      if s.read_attribute(:shortcode).to_i != shortcode
        # Begin locking?
        break
      end
      shortcode += 1
    end

    write_attribute(:shortcode,shortcode)
  end

  def done_shortcode
    # End Locking?
  end
end

Answer 1

This line:

self.class.find(:all,:order=>"shortcode ASC").each

will do a sequential search over your entire record collection. You'd have to lock the entire table so that, when one of your processes is scanning for the next integer, the others will wait for the first one to finish. This will be a performance killer. My suggestion, if possible, is for the process to be as follows:

1. Add a column that indicates when a record has expired (do you expire them by time of creation? last use?). Index this column.

2. When you need to find the next lowest usable number, do something like

   Shorts.find(:conditions => {:expired => true},:order => 'shortcode')

This will have the database doing the hard work of finding the lowest expired shortcode. Recall that, in the absence of the :all parameter, the find method will only return the first matching record.

Now, in order to prevent race conditions between processes, you can wrap this in a transaction and lock while doing the search:

Shorts.transaction do
    Shorts.find(:conditions => {:expired => true},:order => 'shortcode', :lock => true)
    #Do your thing here. Be quick about it, the row is locked while you work.
end #on ending the transaction the lock is released

Now when a second process starts looking for a free shortcode, it will not read the one that's locked (so presumably it will find the next one). This is because the :lock => true parameter gets an exclusive lock (both read/write).

Check this guide for more on locking with ActiveRecord.