Re: Which query is best?

<snip>

The UPDATE is intended for a single row only. I appreciate if you can further explain me about the other ... more optimistic ways you thought about whilst doing a single row UPDATE.
</snip>

There are a number of approaches that can be used. For instance, instead of pre-emptively locking the record prior to doing the work, assume that doing the work will nearly always be successful, and then check that the "controlling" record hasn't changed. In stateless web applications this approach is critical as you don't want to lock a record and then have the user close the browser leaving the lock to timeout.

Here's an example of how this might work:

1. Get a checksum of the "controlling" record
2. Do the work
3. Check that the record hasn't changed by comparing the current checksum with the one collected in step 1).

3a) If checksums are the same, update the row and commit

or

3b) If checksums are different, rollback the work, and display something to the effect that you should requery as the data has changed.

Here's a reference on how to get a row's checksum:

http://www.psoug.org/reference/owa_opt_lock.html

-- 
Rumpi Gravenstein
--
http://www.freelists.org/webpage/oracle-l