ASP & SQL Server - Record Locking

Hello

I have a very quick question... can someone tell me what I need to add to my
select statements to prevent them from locking any records.

I have a busy website where information is viewed frequently and updated
infrequently. Most lists etc. are cached in arrays within the Application
scope, but for the things that are not, it would hopefully increase
performance if I read data without requiring or waiting to obtain a lock.
The significance of someone actually reading outdated information is very
slight, so even if they were reading dirty data it would not be such a big
deal.

I expect this scenario is common to a lot, if not most, websites out there
although select statements seem to obtain a lock by default. It is curious
as I thought the ADO default was adLockReadOnly that surely does not merit a
lock. I am opening my recordsets using this sort of thing...

Set objRs = objConn.Execute("SELECT FieldList FROM Table ...", , adCmdText)
If Not objRs.EOF Then
arrResults = objRs.GetRows
...
End If
objRs.Close
Set objRs = Nothing

I have read of a couple of lock hints but wondered if anyone could provide a
quick and authoritative answer.

Thanks

David

Re: ASP & SQL Server - Record Locking

Ok, I'm adding

WITH (NOLOCK)

Any updates/issues/concerns would be welcomed.


"David Morgan" wrote in
message news:%23zLkXyv3FHA.252@TK2MSFTNGP15.phx.gbl...
> Hello
>
> I have a very quick question... can someone tell me what I need to add to
my
> select statements to prevent them from locking any records.
>
> I have a busy website where information is viewed frequently and updated
> infrequently. Most lists etc. are cached in arrays within the Application
> scope, but for the things that are not, it would hopefully increase
> performance if I read data without requiring or waiting to obtain a lock.
> The significance of someone actually reading outdated information is very
> slight, so even if they were reading dirty data it would not be such a big
> deal.
>
> I expect this scenario is common to a lot, if not most, websites out there
> although select statements seem to obtain a lock by default. It is
curious
> as I thought the ADO default was adLockReadOnly that surely does not merit
a
> lock. I am opening my recordsets using this sort of thing...
>
> Set objRs = objConn.Execute("SELECT FieldList FROM Table ...", ,
adCmdText)
> If Not objRs.EOF Then
> arrResults = objRs.GetRows
> ...
> End If
> objRs.Close
> Set objRs = Nothing
>
> I have read of a couple of lock hints but wondered if anyone could provide
a
> quick and authoritative answer.
>
> Thanks
>
> David
>
>

Re: ASP & SQL Server - Record Locking

thats all you have to do sir

Michael Evanchik
www.michaelevanchik.com

"David Morgan" wrote:

> Ok, I'm adding
>
> WITH (NOLOCK)
>
> Any updates/issues/concerns would be welcomed.
>
>
> "David Morgan" wrote in
> message news:%23zLkXyv3FHA.252@TK2MSFTNGP15.phx.gbl...
> > Hello
> >
> > I have a very quick question... can someone tell me what I need to add to
> my
> > select statements to prevent them from locking any records.
> >
> > I have a busy website where information is viewed frequently and updated
> > infrequently. Most lists etc. are cached in arrays within the Application
> > scope, but for the things that are not, it would hopefully increase
> > performance if I read data without requiring or waiting to obtain a lock.
> > The significance of someone actually reading outdated information is very
> > slight, so even if they were reading dirty data it would not be such a big
> > deal.
> >
> > I expect this scenario is common to a lot, if not most, websites out there
> > although select statements seem to obtain a lock by default. It is
> curious
> > as I thought the ADO default was adLockReadOnly that surely does not merit
> a
> > lock. I am opening my recordsets using this sort of thing...
> >
> > Set objRs = objConn.Execute("SELECT FieldList FROM Table ...", ,
> adCmdText)
> > If Not objRs.EOF Then
> > arrResults = objRs.GetRows
> > ...
> > End If
> > objRs.Close
> > Set objRs = Nothing
> >
> > I have read of a couple of lock hints but wondered if anyone could provide
> a
> > quick and authoritative answer.
> >
> > Thanks
> >
> > David
> >
> >
>
>
>