Re: SELECT .. FOR UPDATE

In article <356d89b6.0_at_per-nts1>,
  "Linda Harte" <Linda.Harte_at_peregrine.ie> wrote:
>
> We're developing a multi-user client server application
> using Oracle7.3 as the database and Aion DS to
> develop our client end.
>
> We have an application which locks a row on a table
> by executing a SELECT .. FOR UPDATE statement.
> The column KEY_ID is the primary key on this table.
>
> There is then further SQL processing on 4 other
> tables in our database. 3 of these tables are joined
> to this table using KEY_ID as a foreign key so there
> is no problems updating rows on these tables.
>
> The problem occurs when updating the 4th table.
> 2 users may attempt to update the same row in
> this table at the same time which will cause one
> user to be left waiting until the other user commits.
>
> On solution is for the first user to commit changes
> after updating table 4 but then the lock has been
> released. The only way I can see to lock this row
> again is to execute a SELECT .. FOR UPDATE
> statement again. This appears to me to be
> an unreliable way to do this as another user
> may be able to lock this row and where would
> that leave us.

I don't understand the problem. Why would you want to continue to hold the lock on the table after doing the commit?

If you want to lock multiple tables, then do a select for update ... nowait on ALL the rows (on ALL tables) that you need to update as a group, and if the lock fails on any one of the selects, then rollback, unlocking everything. Then if the second user tries the same process while the first is holding the lock, all will be rolled back for the second user, who can then retry.

Steve Cosner

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri May 29 1998 - 19:29:21 CEST