Re: Mutual exclusion?

Change the first query to

select * from my_table where RecordInUse='0' and id='1' for update of RecordInUse nowait;

If the row is locked you will get the following error message

ORA-00054: resource busy and acquire with NOWAIT specified

"nobody" <yodaa222_at_NOSPAMhome.com> wrote in message news:Q6xG7.7302$Vf4.3580973_at_news1.rdc1.sfba.home.com...
> 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:55:20 CET