[LRUG] Enforcing uniqueness

[Andrew Stewart]

On 11 Nov 2011, at 15:22, Andrew Stewart wrote:
> I cannot figure out how to ensure a book can't end up with two current loans in the database.

Thanks, everyone, for all the suggestions.

I like the idea of a unique partial index but unfortunately I'm still using an inferior database (MySQL)!

I think a before save callback alone isn't quite enough.  Despite being transactional, I don't think the default isolation level of MySQL will prevent duplicate loans.  And I don't want to tinker with the database's isolation level.

I'm not keen on juggling extra models or tables: too much to go wrong.

Adding a flag to the loan model to permit a unique index is mildly hacky but straightforward.

In fact that last idea reminded me that I need to give book a state (as in state machine): as well as being available or on loan, a book can also be lost or donated.

Now if book has a state column, I think I can use Rails' pessimistic locking[1] (select ... for update) to manage the state change like this:

    Book.transaction do
      book = Book.find(params[:id]).lock!
      if book.state == :available
        book.loans.create params[:loan]
        book.state = :on_loan
      end
    end

[1] http://railsapi.com/doc/rails-v3.0.8rc1/classes/ActiveRecord/Locking/Pessimistic.html

Does that sound sensible?

Thanks again,

Andy
-------
http://airbladesoftware.com