Using Sql Server As Resource Locking Mechanism

Given a table of logical resource identifiers (one per row), what is the best way for an arbitrary number of database clients to perform the following operations: Claim access to

Solution 1:

You are basically describing a classical queue based workflow, and you should consider using a real queue.

For the sake of discussion, here is how you achieve what you wish:

• claim specific resource: SELECT ... FROM resources WITH (UPDLOCK, ROWLOCK) WHERE key = @key. Will block if resource is already claimed. Use lock timeouts to return exception if resource already claimed. keymust be indexed and unique.
• next available resource: SELECT ... FROM resources WITH (UPDLOCK, ROWLOCK, READPAST) ORDER BY <accessorder>. You must define a order by to express the preference of resources (oldest, highest priority etc)
• release a claimed resource: COMMIT your transaction.

The gist of the problem is using the right lock hints, and this kind of problem does require explicit lock hints to solve. UPDLOCK will act as a 'claim' lock. ROWLOCK creates the right granularity preventing the server from 'optimizing' to a page lock. READPAST allows you to skip claimed resources. Placing UPDLOCK on the rows will lock the row and allow you to update it later, but will prevent other operations like ordinary read-committed SELECTs that will block on the locked row. The idea is though that your are going to UPDATE the row anyway, which will place an unavoidable X lock. If you want to keep the table more available you can use app locks instead, but is significantly harder to pull off correctly. You will need to request an app lock on a string descriptor o the resource, like the key value, or a CHECKSUM of the key or it's %%LOCKRES%% value. App locks allow you to separate the scope of the 'claim' from a transaction by requesting the app lock at the 'session' scope, but then you have to release the claim manually ('transaction' scoped app locks are released at commit time). Heads up though, there are a thousand ways to shoot yourself in the foot with app locks.

Solution 2:

SQL Server has a built in stored procedure called sp_getapplock. The documentation describes it as

Places a lock on an application resource.

Clients can compete for the named lock (you give it the name you want) and once the lock is held, perform the required action. It the client crashes, the lock is automatically released. To programmatically release the lock, you can call sp_releaseapplock

A posible solution using applocks

• If the claimant is null, try and grab the applock with the same name
• If the applock is obtained, update the row with your claiment id.
• Once the row is claimed, release the applock as no other client will try and claim it now it is already claimed
• When finished, update the claiment to null