Locking Azure SQL record only when accessed by a specific call

Question

I have a table in Azure SQL with multiple records. The data in this table is exposed via an API layer (based on Entity framework) which implements multiple read/write methods. This API layer is encapsulated within a worker role supporting multiple instances. I have an application (a desktop app, not a web role) which can run simultaneously at different client machines. This application makes 2 specific calls - one read and another write using the aforementioned API. Both calls will access the same record from the Azure SQL table. I am looking to implement a locking mechanism that will prevent multiple clients from trying to read the same record using my application. However this record level read lock should only apply when that one specific read call is made (out of many exposed by the API). While I have figured out how to lock a record, I am not sure how to implement this lock specific to one particular call. This is further complicated when we consider that the worker role implementing the API has multiple instances running.

Some ideas I have considered so far:

1. Stored Procedure: Entity Framework - Read Lock on Record. This runs into the problem of locking the row only for a specific read call.
2. Critical section lock in the read call in the API, that my application uses. However this will not help when multiple instances of the worker is running, since the critical section lock will only be at the instance level.
3. Using an additional IsProcessing column that will be marked as true when the first read is made. This can be used in conjunction with idea-1 and/or idea-2.
4. Refactor that read call I am using in the API to implement row level locking. Since my application is the only one using this particular read call, this could be a possible solution, but then how would I enable the locking only for this call, since other reads from other API calls should still succeed... There are also logistics matters due to which this idea is an absolute last resort that I really don't want to implement if possible.

Any pointers are appreciated.

UPDATE:

Using a mutex in the specific read call my application uses seems to be a possible solution.

Answer 1

I won't question why you are trying to do this, but I stay away from this type of design due to the level of complexity, and usually implement optimistic concurrency instead.

To answer your question directly, I would probably implement Windows Azure Caching, possibly with an expiration window if that makes sense for your design. You could place the Primary Key (PK) value in the cache and verify whether the value is in the cache or not in the API before returning the record. If it is, you don't return the record; it it's not, you allow the read and place the PK in the cache.