SEO

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?

"bmm" <bmmsletdetteher@comlog.dk> wrote in message
news:471c56d4$0$15891$edfadb0f@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

bmm (bmmsletdetteher@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, esquel@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

On Oct 22, 3:52 am, "bmm" <bmmsletdette...@comlog.dk> wrote:
> 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.

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" <esquel@sommarskog.se> skrev i en meddelelse
news:Xns99D1F1D3D751BYazorman@127.0.0.1...
> bmm (bmmsletdetteher@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, esquel@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

bmm (bmmsletdetteher@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, esquel@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