Re: Locking by using FOR UPDATE

From: marianat_at_my-dejanews.com >>>
I'm working with Oracle 7.0. If an application locks a row and another one tries to lock a set of rows (by using SELECT FOR UPDATE, let's say) and suppose that the locked row belongs to the selected set... will the second application receive any data from the server while the first application still holds the lock over the row? <<<

A 'select for update' must lock all the rows before returning any row to the applications so if user1 has an uncommited change to one of the rows that meets the 'select for update' of user2 then user2 will wait or if nowait is specified receive the nowait error and no rows.

I do not know how to answer your exact problem but I know a method that allows us to run multiple processes doing the exact same SQL updates against the same table.

Since you want all unlocked rows back the nowait option is, pardon the pun, not an option. The only way I know to come close to what you want is to not use the 'select for update' statement, but open a select cursor and then if you need to do updates build a driving table with the rowid's and/or key columns. You then open the driving table as a cursor and update the original table possibly with the nowait option. You need to code the update to verify that the row is the correct row and still meets the selection criteria. This method allows multiple processes at the same rows, but sidesteps rows updated or being updated by another. Maybe it will give you an idea on how to solve your problem.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Tue Dec 29 1998 - 15:54:34 CST