Re: Update a ROW while maintaining exclusive LOCK ?

Originally posted by Chris
> 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

If you just want to monitor progress, you could use an autonomous transaction to log progress into another table. Or you could use DBMS_APPLICATION_INFO. It would be wrong (as well as impossible) for any other process to see an updated status on mytable before you commit, because that would violate integrity.

--
Posted via http://dbforums.com