[LRUG] Fwd: Transactions

[Roland Swingler]

And... forward to the list

---------- Forwarded message ----------
From: Roland Swingler <roland.swingler at gmail.com>
Date: Thu, Dec 13, 2012 at 12:20 PM
Subject: Re: [LRUG] Transactions
To: Andrew Stewart <boss at airbladesoftware.com>


Have a look at the optimistic locking in rails - see this class:
http://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html

basically, you include a lock_version column on the table, which is
just an incrementing number. When the second transaction tries to
commit, instead of working it will raise an ActiveRecord::StaleObject
error, which you can then deal with (probably by reloading the record
and trying again).

There is also pessimistic locking, which locks the rows at the
database level iirc:
http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html
but I've not used that, and depending on how busy your system gets may
lead to contention for the locks.

Hope that helps.

R

On Thu, Dec 13, 2012 at 12:14 PM, Andrew Stewart
<boss at airbladesoftware.com> wrote:
> 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
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org