locking a resource from a pool while allowing others to order other resources from the pool of resource

I'm building a website for ordering resources. I have a pool of resources (say 10 resources) and when a user wants to order a resource, I check which is free and assigning it to the user. I'm using MYSQL innoDB table and locking the row using

update ResourceTable SET SOMEFIELD='1'

but if the first transaction didn't finish yet and now another user is searching (using

select * FROM ResourceTable WHERE SOMEFIELD!='1' LOCK IN SHARE MODE

, the us开发者_运维知识库er is waiting for the 1st transaction to finish (although there are other free resources). If I remove the LOCK IN SHARE MODE, I will get a resource that is supposed to be locked.

How can I allow multiple users to order resources without waits?

(I though of somehow selecting rows that are not locked -> not exist in MYSQL)

The solution may be to lock the entire table before changing the value of SOMEFIELD. Updating this value is a very quick operation, so even if a few incoming requests to your site need to access the resource pool near-simultaneously, and one request is in the process of changing SOMEFIELD, the others have to wait only a fraction of a second for it to release the lock on the table.