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

I will buy in with my two cents worth here from what I know of the use of nolock  

Firstly it is part of a standard SQL Server approach to solving a performance issue that can arise and to help SQL server to run multiuser, yes that right multiuser  

Due to the inadequacies of the sQL Server locking paradigm many applications need to run transactions as read uncommmited. This is due to the way SQL server locks when running a select. This delivers a problem in that if say posting is running then the session of a user running a select on the same tables needs to run select with a nolock or else the select will likely lock the table and cause the posting process to fail  

Which now begs the question is SQL Server a real database and how the hell do you effectively scale it because there whole strategy seems to be built on a number of fudges?  

My 0.02c  

Cheers  

Peter McLarty
Technical Consultant
Service Delivery

Address:

67 High Street, Toowong QLD 4066

Phone:

+61733777542

Fax:

+61 7 3377 7301

Mobile:

+61410620391

Email:

Peter_McLarty_at_TechnologyOneCorp.com

Web:

www.TechnologyOneCorp.com  

EVOLVE 2007 - Connect. Learn. Empower.
Register today for the TechnologyOne Evolve 2007 User Conference 13 - 16 February 2007
Gold Coast Convention & Exhibition Centre, Queensland, Australia www.TechnologyOneCorp.com/Evolve  

.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Tuesday, 15 August 2006 3:50 AM
To: norman.dunbar_at_environment-agency.gov.uk Cc: paul.baumgartel_at_credit-suisse.com; oracle-l_at_freelists.org; ineyman_at_perceptron.com
Subject: 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