Re: Locking Question (nowait, UPDATE etc)

> This just sounds completely wrong. Lets find out what are your needs. > I bet there is a very solid solution utilizing Oracle.

It sounds awful/wrong too, but I haven't found anything else that supports what we're trying to do, which is the following:

We have data in tables, some of which we need to export (through a java client). When the export is completed we need to mark the records in the table as export-success or, if the export fails, we need to mark the records as export-failed. The export may take several minutes. The where-clause that determines what data is exported is reasonably time-consuming. The length of time of the export is arbitrary and dependent on external factors.

The process works as follows:

1.) Java client retrieves records to export (using stored proc call that returns refcursor)
2.) Java client exports records
3.) Java client calls stored proc to mark records as exported-success or exported-failed (in same transaction as 1)

It is imperative that
(a) when we mark records as exported-success or exported-failed then we only
mark those records that we tried to export, i.e. the records retrieved in
(1)
(b) records that are in the process of being exported must not be modified
by other sessions
(c) other sessions attempting to update the records that are locked must
time-out within a few seconds

To achieve (a) we could either mark the records using a market column, or we could have the java-client keep track of the rowids of the records and pass those to the stored procedure that updates the records as export-success/export-failed

To achieve (b) we need to lock the records. Since we need to lock the records to achieve (b) we may as well use a marker-column

To achieve (c) we need to have these sessions time-out (nowait behavior) when doing udates.

(a) and (b) aren't the problem, but (c) is: Whenever a session attempts to
UPDATE or DELETE records that are locked by another session using, the session trying to update/delete will wait until the 1st (locking) session commits or rolls back. The only way for the updating/deleting session to detect that it will lock is to run a SELECT FOR UPDATE NOWAIT prior to performing the update/delete. This implies that it has to run the entire
(time-consuming) where-clause as part of the SELECT FOR UPDATE only to
subsequently delete/update those records (which requires the same complex where-clause to be executed again). We can not have all non-export updates to those records be slowed down just because we have to run SELECT FOR UPDATE every time.

If Oracle had a NOWAIT feature on the UPDATE or DELETE commands then this wouldn't be an issue, but it doesn't exist so our solution was to let sessions try to perform the UPDATE/DELETE and kill them if they're waiting on locked records.

Thanks,

 JEE Received on Fri Mar 28 2003 - 08:33:49 CST