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. key must 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.