SELECT .. FOR UPDATE

From: Linda Harte <Linda.Harte_at_peregrine.ie>
Date: Thu, 28 May 1998 17:00:19 +0100
Message-ID: <356d89b6.0_at_per-nts1>

[Quoted] 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 would appreciate any suggestions any one out there has on this.

Thanks
Linda Received on Thu May 28 1998 - 18:00:19 CEST