database - How do I avoid a race condition in my Rails app?

Question

I have a really simple Rails application that allows users to register their attendance on a set of courses. The ActiveRecord models are as follows:

class Course < ActiveRecord::Base
  has_many :scheduled_runs
  ...
end

class ScheduledRun < ActiveRecord::Base
  belongs_to :course
  has_many :attendances
  has_many :attendees, :through => :attendances
  ...
end

class Attendance < ActiveRecord::Base
  belongs_to :user
  belongs_to :scheduled_run, :counter_cache => true
  ...
end

class User < ActiveRecord::Base
  has_many :attendances
  has_many :registered_courses, :through => :attendances, :source => :scheduled_run
end

A ScheduledRun instance has a finite number of places available, and once the limit is reached, no more attendances can be accepted.

def full?
  attendances_count == capacity
end

attendances_count is a counter cache column holding the number of attendance associations created for a particular ScheduledRun record.

My problem is that I don't fully know the correct way to ensure that a race condition doesn't occur when 1 or more people attempt to register for the last available place on a course at the same time.

My Attendance controller looks like this:

class AttendancesController < ApplicationController
  before_filter :load_scheduled_run
  before_filter :load_user, :only => :create

  def new
    @user = User.new
  end

  def create
    unless @user.valid?
      render :action => 'new'
    end

    @attendance = @user.attendances.build(:scheduled_run_id => params[:scheduled_run_id])

    if @attendance.save
      flash[:notice] = "Successfully created attendance."
      redirect_to root_url
    else
      render :action => 'new'
    end

  end

  protected
  def load_scheduled_run
    @run = ScheduledRun.find(params[:scheduled_run_id])
  end

  def load_user
    @user = User.create_new_or_load_existing(params[:user])
  end

end

As you can see, it doesn't take into account where the ScheduledRun instance has already reached capacity.

Any help on this would be greatly appreciated.

Update

I'm not certain if this is the right way to perform optimistic locking in this case, but here's what I did:

I added two columns to the ScheduledRuns table -

t.integer :attendances_count, :default => 0
t.integer :lock_version, :default => 0

I also added a method to ScheduledRun model:

  def attend(user)
    attendance = self.attendances.build(:user_id => user.id)
    attendance.save
  rescue ActiveRecord::StaleObjectError
    self.reload!
    retry unless full? 
  end

When the Attendance model is saved, ActiveRecord goes ahead and updates the counter cache column on the ScheduledRun model. Here's the log output showing where this happens -

ScheduledRun Load (0.2ms)   SELECT * FROM `scheduled_runs` WHERE (`scheduled_runs`.`id` = 113338481) ORDER BY date DESC

Attendance Create (0.2ms)   INSERT INTO `attendances` (`created_at`, `scheduled_run_id`, `updated_at`, `user_id`) VALUES('2010-06-15 10:16:43', 113338481, '2010-06-15 10:16:43', 350162832)

ScheduledRun Update (0.2ms)   UPDATE `scheduled_runs` SET `lock_version` = COALESCE(`lock_version`, 0) + 1, `attendances_count` = COALESCE(`attendances_count`, 0) + 1 WHERE (`id` = 113338481)

If a subsequent update occurs to the ScheduledRun model before the new Attendance model is saved, this should trigger the StaleObjectError exception. At which point, the whole thing is retried again, if capacity hasn't already been reached.

Update #2

Following on from @kenn's response here is the updated attend method on the SheduledRun object:

# creates a new attendee on a course
def attend(user)
  ScheduledRun.transaction do
    begin
      attendance = self.attendances.build(:user_id => user.id)
      self.touch # force parent object to update its lock version
      attendance.save # as child object creation in hm association skips locking mechanism
    rescue ActiveRecord::StaleObjectError
      self.reload!
      retry unless full?
    end
  end 
end

Answer 1

Optimistic locking is the way to go, but as you might have noticed already, your code will never raise ActiveRecord::StaleObjectError, since child object creation in has_many association skips the locking mechanism. Take a look at the following SQL:

UPDATE `scheduled_runs` SET `lock_version` = COALESCE(`lock_version`, 0) + 1, `attendances_count` = COALESCE(`attendances_count`, 0) + 1 WHERE (`id` = 113338481)

When you update attributes in the parent object, you usually see the following SQL instead:

UPDATE `scheduled_runs` SET `updated_at` = '2010-07-23 10:44:19', `lock_version` = 2 WHERE id = 113338481 AND `lock_version` = 1

The above statement shows how optimistic locking is implemented: Notice the lock_version = 1 in WHERE clause. When race condition happens, concurrent processes try to run this exact query, but only the first one succeeds, because the first one atomically updates the lock_version to 2, and subsequent processes will fail to find the record and raise ActiveRecord::StaleObjectError, since the same record doesn't have lock_version = 1 any longer.

So, in your case, a possible workaround is to touch the parent right before you create/destroy a child object, like so:

def attend(user)
  self.touch # Assuming you have updated_at column
  attendance = self.attendances.create(:user_id => user.id)
rescue ActiveRecord::StaleObjectError
  #...do something...
end

It's not meant to strictly avoid race conditions, but practically it should work in most cases.