> #1 Since your cursor is FOR UPDATE NOWAIT, it's an all or nothing deal
> on the row level processing. If it cannot grab all the rows specified
> in the query, it simply throws the exception and that's that. If you
> want to process all the rows that have no row level lock (from another
> session), eliminate the FOR UPDATE NOWAIT and put the NOWAIT into your
> row level processing UPDATE statement.
>
> #2 Wrap you new and improved UPDATE statement in its own
> begin..exception...end where you catch your exception, instead of the
> outermost block as you do now.
Thanks for your response, Cleveridea. The "Other Code" should
definitely be improved. Unfortunately, it is an application that was
developed rather hastily by someone else that allows users to make
changes to their information. Management does not want to shut it down
for some maintenance that we need to do, let alone bring it down to
improve upon it. The maintenace that we have to do is modify certain
fields in people's data. My idea was use the FOR UPDATE NOWAIT when I
do a loop through each record in the table, that way if someone had
their own record locked while they were making a change, it would
generate the wait error for me, then I could insert that user's id
into a table of "users that could not be modified" then the loop would
continue and try to modify the next record. Otherwise, without the
NOWAIT, our update would wait for everyone's change to be committed,
and I suppose that would mean when no one is in the system which might
be an hour or a week.
Received on Tue Sep 11 2007 - 08:00:03 CDT
