Solved

SPID Locks

Posted on 2006-06-29
8
532 Views
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.

Thanks  
0
Comment
Question by:JenebyM
  • 3
  • 3
  • 2
8 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17015596
JenebyM,
> 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.

0
 
LVL 18

Expert Comment

by:ShogunWade
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.
0
 

Author Comment

by:JenebyM
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.

USE KENDB
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DBCC USEROPTIONS
GO

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17015776
0
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.

 
LVL 18

Expert Comment

by:ShogunWade
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.
0
 

Author Comment

by:JenebyM
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.
0
 
LVL 18

Expert Comment

by:ShogunWade
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 .
0
 

Author Comment

by:JenebyM
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.

 
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now