473,850 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Lock Problem and application is slowed down

Dear Memebers,

I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

If you respond me ASAP I really appreciate it

Regards

LS

Nov 3 '06 #1
3 1876
laststubborn (ar*********@gm ail.com) writes:
I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?
This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.

So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?

Which version of SQL Server do you have?

In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 4 '06 #2
Hi Erland ,

Sorry for the late respond. Eventhough we solved the problem and the
problem was coming from the application, I would like to know my
necessary steps to take the action on SQL Server along with your
suggestions.

Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000
Thanks

LS

Erland Sommarskog wrote:
laststubborn (ar*********@gm ail.com) writes:
I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.

So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?

Which version of SQL Server do you have?

In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 29 '06 #3
laststubborn (ar*********@gm ail.com) writes:
Sorry for the late respond. Eventhough we solved the problem and the
problem was coming from the application, I would like to know my
necessary steps to take the action on SQL Server along with your
suggestions.

Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000
I'm afraid that I don't have much to add than teh suggestion to use
aba_lockinfo to get an overview of who is locking whom, and from this
try to understand why.

One situation that I should have mentioned is that if your application
has set up a command timeout (which is 30 seconds by default in many
APIs) and cancels the batch after this time, the application should
always submit a

IF @@trancount 0 ROLLBACK TRANSACTION

since a timeout expired does not rollback any transactions, and not rolling
back in this situations can lead to locks piling up.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 29 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
4384
by: Christoph Zeltner | last post by:
hi, i am writing a thesis on the concurrency control in db2 udb and oracle. I found in the documentary of db2, that a resource holding a (U)pdate-Lock can still be requested by a read-lock (e.g. S). If the U-Lock wants to convert to a write-lock, does the transaction have to wait for the transaction with the S-lock to finish ? Can it starve ? Is this the symmetric RUX-Protocoll ? Questions, so many questions ...
0
1042
by: romicva | last post by:
I've recently upgraded from 8.1.4 to 8.2.3. System slowed down noticebly. Explain shows that indexes are not used. I did reorgs on indexes and tables, collected stats on key fields with detail sample for all indexes... Any help would be greatly appretiated.
7
2707
by: jjmraz | last post by:
Hi, How can I lock down the desktop stopping end user from getting to desktop when inside my app until they quit app? Also how can I start another app from my app? Thanks, JJ
13
2357
by: Andrew Morton | last post by:
I am caching some data in VB.NET using System.Web.Caching, is it possible to lock the cache so that other sessions attempting to access the same cache wait when it is being updated? I have the cache using a sliding timeout and a dependency on the text file its data is extracted from. If it's relevant, based on current statistics, I do not expect more than about 400 people to be accessing the web site at the same time. I've seen it appears...
59
7540
by: Rico | last post by:
Hello, I have an application that I'm converting to Access 2003 and SQL Server 2005 Express. The application uses extensive use of DAO and the SEEK method on indexes. I'm having an issue when the recordset opens a table. When I write Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly) I get an error. I believe it's invalid operation or invalid parameter, I'm
5
2247
by: hmy | last post by:
Hi, I need to lock my application if it has been idle for 15 minutes. This is a windows application in c#. I have put a timer in the main form and implemented the IMessageFilter interface. In the PreFilterMessage method, I check for the key down and mouse move and mouse down messages. I am able to detect the inactivity if this application is not the active window. I problem is I can't detect the idle time when the application is the active...
11
7991
by: fritzcwdev | last post by:
I have a class as follows: public class OperationFeedback { DateTime _startTime; public DateTime StartTime { get {
5
3193
by: prakashwadhwani | last post by:
The Delete Event/Proc & "Save_Fields_In_Form_Header" Event/Proc in my form were working perfectly. However, after I added a call to the "Save_Fields_In_Form_Header" Event/Proc in the "Current" Event of my form, and I now try to Delete a record, I get ... Run-Time Error 3218 - Could not Update; Currently Locked. My Access application then effectively freezes forcing me to shut Access down & re-start. The record DOES get deleted...
3
30875
by: Marco | last post by:
Hi all, with db2 snapshot I know the lock waits number but I can not see the tables with locks an I can not see queries that produces locks. How can I know those details? Thanks Marco Strullato
0
9741
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11011
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10666
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9503
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7899
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7071
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5929
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4546
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3178
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.