CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordconcurrency
coorasse
coorasse

Reputation: 5528

ActiveRecord pessimistic locking a record for reservation system

I have a reservation system where I need to lock a record so that two users cannot book it at the same time.

The model Seat has three statuses: available, reserved and booked.

While being reserved the user has 5 minutes to complete the booking.

I can do this with a pessimistic locking. I add a lock_version column to the table and do

def reservation
  @seat = Seat.available.order(created_at: :desc).first
  if @seat
    @seat.reserved!
    redirect_to 'continue to confirm booking'
  else    
    redirect_back with message "no seats available"
  end
  rescue ActiveRecord::StaleObjectError
    retry
  end
end

Since in this system the possibility of conflicts is very likely, I'd like to use a pessimistic locking intead of an optimistic one, but I didn't succeed.

This is the code I tried (note the addition of lock):

def reservation
  @seat = Seat.available.order(created_at: :desc).lock.first # executes a SELECT FOR UPDATE
  if @seat
    @seat.reserved!
    redirect_to 'continue to confirm booking'
  else    
    redirect_back with message "no seats available"
  end  
end

The problem is that two records are still being selected at the same time even though the query is

SELECT * from seats where status = 'available' order by created_at desc limit 1 for update.

I am not sure how to implement an optimistic locking for such case.

Upvotes: 0

Views: 176

Answers (1)

coorasse
coorasse

Reputation: 5528

After some hours of research I found out what was the issue. Looking again at the optimistic code, the issue is that there's no transaction defined.

Adding a transaction fixes the problem. Here is the corrected code:

def reservation
  Seat.transaction do
    @seat = Seat.available.order(created_at: :desc).lock.first
    if @seat
      @seat.reserved!
      redirect_to 'continue to confirm booking'
    else    
      redirect_back with message "no seats available"
    end  
  end
end

now the select for update and update of the record itself are within the same transaction.

Upvotes: 2

Related Questions