Efficient Concurrency?

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.

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

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,

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

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,

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

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

Best,

Florian