Mutual exclusion?

I have the following sql statements:

select * from my_table where RecordInUse='0' and id='1'; update table my_table set RecordInUse='1' where id='1';

The first statement checks to see if the record is being used by someone else. If it is I don't want it and I try another record. If it isn't in use, I want to set the flag to tell other people that I'm using it and they can't. Here's the problem: if one person hits the select statement and sees that the record isn't in use, then another person hits the select statement before the first one does the update statement, both people will end up working with the same record. Any ideas on how to fix this? What to do differently? Received on Thu Nov 08 2001 - 16:01:04 CET