Solved

SPID Locks

Posted on 2006-06-29
8
531 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

14 Experts available now in Live!

Get 1:1 Help Now