Efficient Concurrency?

rubyonrails-talk
1

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

2

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

3

Frederick Cheung 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?

Fred

Best,

4

Frederick Cheung 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

5

Frederick Cheung 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?

Fred

Best,

6

Frederick Cheung 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

7

Thanks for the responses and discussion, everybody! It seems like lock_version should do the trick in my case.

Best,

Florian