SPID Locks

Posted on 2006-06-29
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 500 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 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.


SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
push and Pull replication 31 48
Connect remotely to SQL Server DB without TCP/IP enabled? 2 37
Trigger C# code inside the SQL Server 6 36
SQL profiler 3 19
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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