Introducing (NOLOCK) into production code for Selects

12 Nov 2005 2:15 AM

jkotuby

I have a large application that is multi-user and quite transactional,
driven by a VB
front end. It uses mostly stored procedures for retrieving data. I was
running into locking
contention on tables with 300,000 to 2 million records that are read and
updated by all users.

We allow the users to see the top 1000 rows from a table in "browse" mode in
VB (as a disconnected recordset), from which they can select a single record
to edit and update (also disconnected during editing).

I have read in Kalens book that we should use a lock hint for our Selects
(NOLOCK). But the only example given is like this:

SELECT <field list> FROM <table> (NOLOCK) WHERE .........

This is followed by the explanation that any Lock Hint needs to be wrapped
in a BEGIN TRAN/COMMIT.
Or IMPLICIT_TRANSACTIONS must be set on.

My application has thousands of lines of code, and the implications of
IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a production
app.

Can a simple Select issue a NOLOCK without being wrapped in an explicit
transaction?
I am trying to find an easy way to modify hundreds of stored procs that
retrieve data just for browsing without creating a Shared Lock. Most of the
parameterized Procs do some decision making before issuing a single Select.
For example (abbreviated to save space)

If @Account_Type = 'P'
    Select <some fields> from <view>
Else

    Select <other fields> from <view>

Which begs another question, can NOLOCK be used when selecting from a view?
As in:

If @Account_Type = 'P'
    Select <some fields> from <view> (NOLOCK)
Else

    Select <other fields> from <view> (NOLOCK)

Thanks for your input.

12 Nov 2005 6:59 AM

JXStern

On Fri, 11 Nov 2005 21:15:11 -0500, "jkotuby" <jkot***@snet.net>
wrote:
>Can a simple Select issue a NOLOCK without being wrapped in an explicit
>transaction?

Yes.

What Kalen says about lock hints in transactions (I don't have her
book here) may hold for locks, but doesn't apply to nolocks!

I've seen tons of production code done the way you want.

Not sure I approve of it, but it does what it does.

J.

12 Nov 2005 11:31 AM

Brian Selzer

As I stated in an earlier post, NOLOCK will make your queries return
incorrect results at lightning speed. You must weigh the risk of returning
wrong answers against the performance benefits. Don't use it if the results
will be used in an INSERT or UPDATE.

Show quote

"jkotuby" <jkot***@snet.net> wrote in message
news:OaQvt7y5FHA.1276@TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
>
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
>
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
>
> SELECT <field list> FROM <table> (NOLOCK) WHERE .........
>
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
>
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
>
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
>
> If @Account_Type = 'P'
>    Select <some fields> from <view>
> Else
>
>    Select <other fields> from <view>
>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
>
> If @Account_Type = 'P'
>    Select <some fields> from <view> (NOLOCK)
> Else
>
>    Select <other fields> from <view> (NOLOCK)
>
>
> Thanks for your input.
>
>

12 Nov 2005 3:14 PM

Andrew J. Kelly

Locking hints in general are not required to be wrapped in a transaction.
Some hints may require a transaction to get the desired overall result.
These would be things that need to hold the lock for the duration of or
across several statements. But NOLOCK is not one of them. The correct way
to use it would be to include the previously optional WITH as shown:

SELECT * FROM Table WITH (NOLOCK)

Just be aware that using NOLOCK will potentially give you dirty reads. If
that is OK for your application then fine but be aware of what implications
it may have. Reads in general are compatible with there reads. So if you
are being blocked a lot you may have transactions open for too long a period
of time and see what you can do to reduce that. A lack of proper indexes
will increase the time it takes for a DML operation along with the number of
rows affected.

--
Andrew J. Kelly SQL MVP

Show quote

"jkotuby" <jkot***@snet.net> wrote in message
news:OaQvt7y5FHA.1276@TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
>
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
>
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
>
> SELECT <field list> FROM <table> (NOLOCK) WHERE .........
>
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
>
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
>
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
>
> If @Account_Type = 'P'
>    Select <some fields> from <view>
> Else
>
>    Select <other fields> from <view>
>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
>
> If @Account_Type = 'P'
>    Select <some fields> from <view> (NOLOCK)
> Else
>
>    Select <other fields> from <view> (NOLOCK)
>
>
> Thanks for your input.
>
>