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.

Thanks,

Florian

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Frederick C. 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?

Fred

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

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.

Fred

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.

Fred

On 21 Sep 2009, at 17:48, Marnen Laibow-Koser wrote:

something?
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

Frederick C. 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?

Fred

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]