FOR UPDATE OF and NOWAIT questions

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 - 08:20:53 CST