My app sends out activation keys from a list of unassigned keys to customers. An appropriate model action in the activation key model would look something like this: named_scope :available, :conditions => ''available IS NULL'' def fetch_available_key key = ActivationKey.available.first key.assigned = DateTime.now key.save return key end What''s the best way to deal with concurrent requests in this situation? My deployment server uses passenger, the database is MySQL 5.x. Thanks, Florian
Florian Dejako wrote:> My app sends out activation keys from a list of unassigned keys to > customers. An appropriate model action in the activation key model > would look something like this: > > named_scope :available, :conditions => ''available IS NULL'' > > def fetch_available_key > key = ActivationKey.available.first > key.assigned = DateTime.now > key.save > return key > end > > What''s the best way to deal with concurrent requests in this > situation? My deployment server uses passenger, the database is MySQL > 5.x.I''ve never quite done this, but from the docs it seems that you probably want pessimistic locking. Make sure that you''re using a mySQL storage engine that supports the appropriate features; better yet, try switching to PostgreSQL.> > Thanks, > > FlorianBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Sep 21, 3:57 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas- s.net> wrote:> Florian Dejako wrote: > > My app sends out activation keys from a list of unassigned keys to > > customers. An appropriate model action in the activation key model > > would look something like this: > > > named_scope :available, :conditions => ''available IS NULL'' > > > def fetch_available_key > > key = ActivationKey.available.first > > key.assigned = DateTime.now > > key.save > > return key > > end > > > What''s the best way to deal with concurrent requests in this > > situation? My deployment server uses passenger, the database is MySQL > > 5.x. > > I''ve never quite done this, but from the docs it seems that you probably > want pessimistic locking. Make sure that you''re using a mySQL storage > engine that supports the appropriate features; better yet, try switching > to PostgreSQL. > >would have thought that Active Record''s optimistic locking would do the job - if upon saving you get a StaleObjectError you know that you need to try again. Fred> > > Thanks, > > > Florian > > Best, > -- > Marnen Laibow-Koserhttp://www.marnen.org > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > -- > Posted viahttp://www.ruby-forum.com/.
Frederick Cheung wrote:> On Sep 21, 3:57�pm, Marnen Laibow-Koser <rails-mailing-l...@andreas- > s.net> wrote: >> > � key.save >> to PostgreSQL. >> >> > would have thought that Active Record''s optimistic locking would do > the job - if upon saving you get a StaleObjectError you know that you > need to try again.Perhaps it would; I''ve never used ActiveRecord locking at all, so I''ll defer to you here. However, I tend to think that locking should generally be implemented at the database level, not the application level, which is why I suggested pessimistic here. Am I missing something?> > FredBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Sep 21, 4:09 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas- s.net> wrote:> Frederick Cheung wrote: > > On Sep 21, 3:57 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas- > > s.net> wrote: > >> > key.save > >> to PostgreSQL. > > > would have thought that Active Record''s optimistic locking would do > > the job - if upon saving you get a StaleObjectError you know that you > > need to try again. > > Perhaps it would; I''ve never used ActiveRecord locking at all, so I''ll > defer to you here. However, I tend to think that locking should > generally be implemented at the database level, not the application > level, which is why I suggested pessimistic here. Am I missing > something?Well with optimistic locking it is ultimately the database enforcing stuff - after all it is the only thing that can. If my memory is correct the docs for optimistic locking have a decent explanation of how it works. Fred> > > > > Fred > > Best, > -- > Marnen Laibow-Koserhttp://www.marnen.org > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > -- > Posted viahttp://www.ruby-forum.com/.
Frederick Cheung wrote:> On Sep 21, 4:09�pm, Marnen Laibow-Koser <rails-mailing-l...@andreas- > s.net> wrote: >> Perhaps it would; I''ve never used ActiveRecord locking at all, so I''ll >> defer to you here. �However, I tend to think that locking should >> generally be implemented at the database level, not the application >> level, which is why I suggested pessimistic here. �Am I missing >> something? > > Well with optimistic locking it is ultimately the database enforcing > stuff - after all it is the only thing that can. If my memory is > correct the docs for optimistic locking have a decent explanation of > how it works.If I understand the docs correctly, optimistic locking is *not* enforced by the database -- rather, it''s up to the app to properly handle checking and updating the lock_version field. (The value is *stored* in the DB, but the DB knows nothing about using it for lock enforcement.) Pessimistic locking, by contrast, uses the DB''s native locking mechanisms. This makes it far more airtight than optimistic, I think. Anyway, optimistic locking seems like reinventing the square wheel. Why bother?> > FredBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On 21 Sep 2009, at 17:48, Marnen Laibow-Koser wrote:> > Frederick Cheung wrote: >> On Sep 21, 4:09�pm, Marnen Laibow-Koser <rails-mailing- >> l...@andreas- >> s.net> wrote: >>> Perhaps it would; I''ve never used ActiveRecord locking at all, so >>> I''ll >>> defer to you here. �However, I tend to think that locking should >>> generally be implemented at the database level, not the application >>> level, which is why I suggested pessimistic here. �Am I missing >>> something? >> >> Well with optimistic locking it is ultimately the database enforcing >> stuff - after all it is the only thing that can. If my memory is >> correct the docs for optimistic locking have a decent explanation of >> how it works. > > If I understand the docs correctly, optimistic locking is *not* > enforced > by the database -- rather, it''s up to the app to properly handle > checking and updating the lock_version field. (The value is > *stored* in > the DB, but the DB knows nothing about using it for lock enforcement.)when you do a save with optimistic locking it appends a " where lock_version=expected_version" to the update query, so yes it is up to the app to append that, but the ultimate decision of ''has lock_version changed'' will fall to the db.> > Pessimistic locking, by contrast, uses the DB''s native locking > mechanisms. This makes it far more airtight than optimistic, I think. > Anyway, optimistic locking seems like reinventing the square wheel. > Why > bother?Because in a case where you expect the lock to almost always succeed it''s faster. Fred> >> >> Fred > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > -- > Posted via http://www.ruby-forum.com/. > > >