Update a ROW while maintaining exclusive LOCK ?

Hopefully someone might be able to educate me as to a way to accomplish this:

Suppose I have a program that uses this select statment to obtain a lock on a row that it wishes to process.

SELECT
  KEYVALUE,
  STATUS
FROM
  MYTABLE
WHERE
  KEYVALUE = :USERKEY
FOR UPDATE NOWAIT ; Now - at various stages of execution, my program needs to update the status of this row so that progress can be monitored by other users. How can I update the row, making the new status visible to all other users, without losing my lock?

The only way I know to do this would be:

UPDATE
  MYTABLE
SET
  STATUS = :NEWSTATUS ; COMMIT ; And now repeat the SELECT ... FOR UPDATE NOWAIT statement.

This is not good, because there is the possibility (be it however slim) that someone else could lock the record between my UPDATE and re-SELECT statements.

Any ideas or suggestions would be much appreciated.

Thanks,
Chris Received on Fri Jun 20 2003 - 17:24:37 CEST