[LRUG] Transactions

[Frederick Cheung]

On 13 Dec 2012, at 12:14, Andrew Stewart <boss at airbladesoftware.com> wrote:
> 
> (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
> 

Transactions on their own don't solve this (at least not without an isolation level of SERIALIZABLE which is very bad with regards to concurrency).. One simple thing you could do would be to add a unique index on checked_out_by_id, book_id on loans, If you get two attempts to check a book out the loan creation fails and the transaction rolls back.

You probably want some degree of locking on books, to guard against 2 people checking out the same book at the same time. Optimistic locking is easy to add, but you do need to be aware that in theory any save can fail with a StaleRecordError. Depending on the type of operation you are doing, appropriate recovery from StaleRecordError can just be reload and try again: perhaps the state has changed, perhaps it wasn't (maybe someone updated the meta data for the book rather than starting a loan).

Pessimistic locking is also an option - inside your transaction call lock! on the book. The lock lasts until the end of the transaction. Pessimistic locking often carries a larger performance penalty than optimistic, although the balance depends on how likely a conflict is - the rarer these are, the more advantageous optimistic locking is

Fred


> 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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4399 bytes
Desc: not available
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20121213/d5c4d472/attachment-0004.bin>