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

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
  • 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 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.


Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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