[LRUG] Transactions

[Andrew Stewart]

Afternoon all,

I have successfully confused myself with (mysql) database transactions – hopefully someone can enlighten me.

Here's the scenario: a book can be checked out from a library.  When that happens, the book's state is updated from available to out and a loan record is created recording who checked out the book.  The code looks like:

class Book < ActiveRecord::Base
  has_many :loans

  state_machine initial: 'available' do
    state 'available' do
      transition on: :transition_check_out, to: 'out'
    end
    state 'out' do
      # ...
    end
  end

  def check_out(person)
    ActiveRecord::Base.transaction do
      if transition_check_out  # (1)
        loans.create checked_out_by_id: person.id
      else
        # (2)
      end
      sleep 5  # (3)
    end
  end
end

Notes:

(1) Handled by the state_machine gem.  It's equivalent to (AFAIK):

        if state == 'available' && update_attributes state: 'out'

(2) I assume the book is already checked out (to this person) thanks to a double-click on the GUI.

(3) The sleep is to make the problem easier to reproduce.

The idea is that if there's a double-click on the check-out form in the GUI, the book won't end up with two loan records representing the same loan in real life.  However occasionally and reproducibly I can get two loan records like this:

- Process 1 executes and starts sleeping at (3).
- Process 2 starts a new transaction and cannot see the pending results of process 1's transaction.  It (process 2) thinks the book is available and so updates it to out and creates a loan.  Process 2 starts sleeping.
- Process 1 commits its transaction.
- Process 2 commits its transaction.  There are now two "duplicate" loans.

I've reached the lamentable stage where the more I think about this, the less I understand :p

Any help would be much appreciated.

BTW this follows up:
http://lists.lrug.org/htdig.cgi/chat-lrug.org/2011-November/006652.html

Thanks in advance,

Andy Stewart