Wrong result Set when using NoLock

[bmm]

I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
"NoLock" on all selects.

One of my clients (OleDbConnection from C#) doesn't get the same Result Set
as the others. The result Set should have 31 rows but this client only gets
5!

When I remove all the "NoLocks" everything works fine. How can that be?

[Greg D. Moore \(Strider\)]

"bmm" <bm*************@comlog.dkwrote in message
news:47***********************@dtext01.news.tele.d k...

>I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
"NoLock" on all selects.

One of my clients (OleDbConnection from C#) doesn't get the same Result
Set as the others. The result Set should have 31 rows but this client only
gets 5!

Since you said "others" I'm a bit confused. If you had said ONE other got
31 and everyone else was getting 5 I'd say that makes perfect sense due to
how transactions work and how (nolock) works. (i.e. my guess would be you
have an open transaction someplace).

>
When I remove all the "NoLocks" everything works fine. How can that be?

In this case, off the top of my head, I can't quite figure out the scenario
that would give this behavior).

>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[Erland Sommarskog]

bmm (bm*************@comlog.dk) writes:

I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
"NoLock" on all selects.

One of my clients (OleDbConnection from C#) doesn't get the same Result
Set as the others. The result Set should have 31 rows but this client
only gets 5!

When I remove all the "NoLocks" everything works fine. How can that be?

Maybe you could clarify a few things.

When you say "client" is that "client" as in "customer" or as in "client
computer"? Does the client that only gets five rows run the same application
as those that get 31? If they run different applications, doethe other
application use a different API?

Is this behaviour constistent? That is, does it happen even if there is
no activity on the system, so that there are no locked rows?

Would it be possible for you to post the code of the procedure?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Najm]

On Oct 22, 3:52 am, "bmm" <[e-mail]:

I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
"NoLock" on all selects.

One of my clients (OleDbConnection from C#) doesn't get the same Result Set
as the others. The result Set should have 31 rows but this client only gets
5!

When I remove all the "NoLocks" everything works fine. How can that be?

Hi there,
I am posting few lines from SQL Sever magazine article written by
Itzik Ben-Gan:

"
With the NOLOCK hint (or setting the isolation level of the session
to READ UNCOMMITTED) you tell SQL Server that you don't
expect consistency, so there are no guarantees.
Bear in mind though that "inconsistent data" does not only mean that
you might see uncommitted changes that were later rolled back, or
data changes in an intermediate state of the transaction. It also
means
that in a simple query that scans all table/index data SQL Server may
lose the scan position, or you might end up getting the same row
twice.

"

You may find the details in the doc: InstantDoc #92888

You can replicate this behavior in SQL Server 2005/2000 using code
given in the above article and I believe it is public.

I hope it helps.

[bmm]

Hi again

Sory if my english is bad..

The situation is that I have a configuration table (CONF) which is accesed
often by severel programs (computers) on our network. Some of them are
WEB-applications, some are C# applications and some are manual used
query-analysers.

We noticed, that the C#-application sometimes doesn't get all the data from
the CONF-table when running the specific Stored procedure that reads data
from the CONF-table using NOLOCK. We couldn't get query-analyser to fail the
same way, when we used the same SP. (The SP also reads some other tables
beside the CONF-table. All the selects were using NOLOCK)

The problem was solved when we removed all the NOLOCKs from the SP.

I could understand if the SP returned too many rows in some cases, but not
too few....

/bjarni
"Erland Sommarskog" <es****@sommarskog.seskrev i en meddelelse
news:Xn**********************@127.0.0.1...

bmm (bm*************@comlog.dk) writes:

>I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
"NoLock" on all selects.

One of my clients (OleDbConnection from C#) doesn't get the same Result
Set as the others. The result Set should have 31 rows but this client
only gets 5!

When I remove all the "NoLocks" everything works fine. How can that be?

Maybe you could clarify a few things.

When you say "client" is that "client" as in "customer" or as in "client
computer"? Does the client that only gets five rows run the same
application
as those that get 31? If they run different applications, doethe other
application use a different API?

Is this behaviour constistent? That is, does it happen even if there is
no activity on the system, so that there are no locked rows?

Would it be possible for you to post the code of the procedure?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

bmm (bm*************@comlog.dk) writes:

The situation is that I have a configuration table (CONF) which is accesed
often by severel programs (computers) on our network. Some of them are
WEB-applications, some are C# applications and some are manual used
query-analysers.

We noticed, that the C#-application sometimes doesn't get all the data
from the CONF-table when running the specific Stored procedure that
reads data from the CONF-table using NOLOCK. We couldn't get
query-analyser to fail the same way, when we used the same SP. (The SP
also reads some other tables beside the CONF-table. All the selects were
using NOLOCK)

The problem was solved when we removed all the NOLOCKs from the SP.

I could understand if the SP returned too many rows in some cases, but not
too few....

Still not very much information to work from. Are these web application
also using OleDbConnection?

Is this configuration table frequently updated? Or why the use of NOLOCK?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx