Re: Is it possible to use nolock type of hint in query

On 8/14/06, Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk> wrote:
>
>
> Dave Ensor's book about Oracle Design has a good chapter on this very
> subject, which basically boils down to 'lock late and lock for a short
> time only'. SELECT ... FOR UPDATE is a 'lock early and lock for as long
> as possible' technique.
>
>

I don't see how that invalidates the use of SELECT FOR UPDATE.

The key is when to use it.

Query data at the beginning of the transaction.

This obviously must be stored somewhere. PLSQL tables come to mind. It depends on the size of the transaction I guess.

Let the user modify data as needed.

When the user is ready to commit the transaction:

• Now do the SELECT FOR UPDATE
• If the rows can't be locked, the transaction obviously is void Poor user must start over.
• If the rows are locked, has the data in the rows to be involved in the transaction changed since the beginning of the users transaction?
• If data has changed, someone else has modified rows that you wish to modify, and you must determine how to handle that.
• If nothing has changed, commit the data.

This leaves the row(s) locked just long enough for the app to determine if it is safe to proceed.

I believe that Oracle Forms does something like this.

The problem seems to be (as with many things involving databases) the misuse of the SELECT FOR UDPATE statement.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l