Re: FOR UPDATE OF and NOWAIT questions

I prefer the NOWAIT clause. Put it in to a loop using DBMS_LOCK.SLEEP(0.2), or something like that, and try five times before erroring.

I do not believe your DBAs are correct ... unless the intention is to hang a user indefinitely.

Daniel Morgan

Jeff Smith wrote:

> I have a question about using "FOR UPDATE" and "NOWAIT" clauses to update
> rows.
>
> Working on a large development team, some differences of opinion have
> occurred and I am looking for additional input from this newsgroup which has
> assisted me in the past.
>
> Usually when I am updating a table from within a stored program unit
> (function/procedure), I declare a cursor with a "for update of col1,
> col2,..." and use the NOWAIT clause. When I am ready to update the row, I
> open the cursor, if it opens, I know I have locked the row and I perform the
> update. I immediately close the cursor, commit the transaction and go on my
> merry way. If I fall to an exception when attempting to open the cursor, I
> report the error accordingly, which is usually because the row is locked,
> and the user can attempt to save again.
>
> The reason I have done it this way is that I want to guarantee that when I
> actually issue the UPDATE, it is not going to sit there and wait until
> Oracle can do the update. If the row is locked, Oracle will retry and sit
> there until it succeeds or fails without the NOWAIT option. I know the
> length Oracle will wait is configurable, but this is a web application and
> rapid response is critical.
>
> In some code reviews, some other developers and a dba have indicated that
> using a for update and nowait clauses are undesirable. They have indicated
> this practice prevents other users from accessing the rows. The preferred
> method is to simply UPDATE and let Oracle handle the locking itself. Having
> every one "peck" at the database instead of intentionally locking the rows.
>
> I don't doubt that this will work. In trying not to be closed minded, I have
> to say that I may be an old dog attempting new tricks, but I have always
> been very diliberate when updating data and I have a hard time accepting
> their recommendation. I will do so, but I prefer additional input from
> outside this development environment. This particular database is shared by
> numerous applications in numerous languages and tools, which in my mind
> raises the risks of some other application having rows locked... intentially
> or not intentially.
>
> Oracle documentation boasts the FOR UPDATE (row-level locking) and the
> NOWAIT clauses. It is because they are there in the docs that I started
> using this approach years ago. I will also say, trying not to be arrogant,
> but my database code works well and there are rarely any surprises.
>
> Does anyone have opinions, factual and otherwise they would care to
> contribute to these two approaches of updating tables.
>
> Regards,
> Jeff Smith
Received on Fri Jan 25 2002 - 03:58:42 CST