I have following scenerio:

(1) Master Project.mdb with uniq ID key (e.g 20000 record with key 1 to 2000)

(2) batch database holding sub set Keys from the Project.mdb (e,g, 1000 records keys from 2001 to 3000) and a status field set to "Process"



Multi users will query the batch database for the unique first available record for processing where status = "Processing"

e.g lets say you get id 2001

query: Select distinct top 1 from batchTable where status = "Processing"
application will set status = "locked"

it will query project.mdb for the ID retrived from batch database, process it and then set locked record to status "DONE"



My requirement is on each query from multiple user, batch database must give them unique record so each user will always process unique record from Project.mdb.



In real example the queries come so fast that select query gives same record to multiple user.



How can I lock batch database to honor only one select query at a time till it finishes update to locked status?



Thank you in advance



Hitesh