Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SPID Locks

Posted on 2006-06-29
Medium Priority
Last Modified: 2010-08-05
My application ceases to respond when an spid XX (blocked) occurs on SQL Server 2000 sp4 .(XX being the number assigned to the user/query) My dot.NET application does not require table or row locks when any "Select" statement is used. Can I resolve this in the application code.

1.  Will the use of "NO LOCK" in queries resolve the 'blocking'/"blocked" problem at SQL level.

2.  Is there a way of setting up SQL to ensure there are no table locks for all queries from the application.

I have 250 users on the systems currently, hence this is urgent and valued at 500. We have just updated to sp4. Had no problems with  previous SQL 2000 installation.

Question by:JenebyM
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
LVL 75

Accepted Solution

Aneesh Retnakaran earned 1500 total points
ID: 17015596
> 1.  Will the use of "NO LOCK" in queries resolve the 'blocking'/"blocked"  problem at SQL level.

NOLOCK is same as ANSI defined isolation level 'read uncommitted', It means that a transaction running under this isolation level can read data modified by transactions that have not yet committed.  A query that runs under NOLOCK or read uncommitted isolation level, it will not get blocked by concurrent updates.

LVL 18

Expert Comment

ID: 17015682
concur with above.      If when you establish your connectiions you issue SET TRANSACTION ISOLATION READ UNCOMMITTED it will avoid blocking,   but you need to be careful about dirty yreads  (See BOL for details).  Additionalliy you may want to look at what commands are issued to the instance via profiler and see it you can optimise performance,  this the faste the response, the less impact locking has.

BEWARE - If you open a ADO.NET transation and dont specify the isolation level it will set the isolation levelback to read committed.

Author Comment

ID: 17015756
The above addresses part #1 in my question to some extent. Before I attempt the resolution however can you comment on part 2 of the question.

This is because I would like to avoid changes application source code, i.e to then specify isolation level on the ado.net transaction instances on the entrie souce code.

Will the following permanently achieve the same effect of avoiding blocking if I run this script on the database.


Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17015776
LVL 18

Expert Comment

ID: 17015783
No it wont.    At least Not if you have a

.BeginTransation() call in your client.      If you dont stipulate the issolatin level in such a command it is hard coded to set the isolation level back to READ COMMITTED.

Author Comment

ID: 17015885

I assume that if the isolation level of ALL .BeginTransaction() call is set to "readuncommitted" in the dot.NET apps then the SQL Server isolation level will remain readuncommitted.

Is this assumption correct.
LVL 18

Expert Comment

ID: 17038857
Sorry,  Ive been away for a few days....

"I assume that if the isolation level of ALL .BeginTransaction() call is set to "readuncommitted" in the dot.NET apps then the SQL Server isolation level will remain readuncommitted."
Yes You're correct .

Author Comment

ID: 17039635
We now have a much more serious situation.i.e Speed/Performance.

The machine which had the dot.Net apps and SQL Server 2000 crashed last week. We have a new machine that is of a higher specification than the previous machine but the performance is so bad that the application is unusable.

The things I have checked on the new machine include:
updating to Win 2000 sp4
updating SQL Server to sp4
updating MDAC_TYPE to 2.8
updating to .netfx 1.1
Web.Config & machine.config files
Database table re-indexing (2gb database)
and more

Any obvious things I should look for when a dot.NET web apps cannot connect to SQL Server 2000 at a resonable speed.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question