473,700 Members | 2,593 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Killing the process automatically

Hi everybody,

We have a very large database and high transaction volume. Time to time
these transactions are locking each other and decrease the performance
of the database. Is there any way that I can automate the killing
process when blocking and deadlock time is exceeded in certain time
elipsade? Can somebody help me on this please?

Regards
asa.

Apr 12 '06 #1
6 4626
laststubborn wrote:
Hi everybody,

We have a very large database and high transaction volume. Time to time
these transactions are locking each other and decrease the performance
of the database. Is there any way that I can automate the killing
process when blocking and deadlock time is exceeded in certain time
elipsade? Can somebody help me on this please?


If SQL Server detects a deadlock it will kill one of the two involved TX
automatically. But you should really change your app to prevent these
deadlocks.

You probably cannot do much about normal locking as this is expected
behavior other than probably optimizing your SQL to make it faster.

HTH

robert
Apr 12 '06 #2
Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??

Thanks

Apr 12 '06 #3
laststubborn wrote:
Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??


read the docs (BOL)

Customizing the Lock Time-out
When Microsoft® SQL Server™ 2000 cannot grant a lock to a transaction on
a resource because another transaction already owns a conflicting lock
on that resource, the first transaction becomes blocked waiting on that
resource. If this causes a deadlock, SQL Server terminates one of the
participating transactions (with no time-out involved). If there is no
deadlock, the transaction requesting the lock is blocked until the other
transaction releases the lock. By default, there is no mandatory
time-out period, and no way to test if a resource is locked before
locking it, except to attempt to access the data (and potentially get
blocked indefinitely).
robert
Apr 12 '06 #4
laststubborn (ar*********@gm ail.com) writes:
Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??


A deadlock does not take five minutes to sort out. It seems that you
have a misconception of what a deadlock is. A deadlock is when two
processes are blocking each other, so none of them can continue. This
is something that SQL Server detects automatically. It usually takes a
couple of seconds.

But one long-running process can block other processes (than in their
turn can block other processes etc) without any deadlock to occur.

I would advice against any automatic killing, as supposedly some processes
are more important than others. It's better to analyse what those blockers
are up to, and if the queries can be improved, or indexes added to
speed up these queries.
--
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
Apr 12 '06 #5
Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.

Apr 13 '06 #6

"D0MZE" <do******@gmail .com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.com.. .
Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.
Not quite.

For a select it basically means to ignore locks on rows.

This can mean you can get phantom rows, not get rows you should etc. i.e.
you'll get an inconsistent view of the table at the time.

This MAY be acceptable in some circumstances, but in others would be
completely verbotin. (imagine an ATM that did a look up on cache available
with a (NOLOCK) while your bank is deleting your last check. You'd falsely
be told you have more money available than you actually do and could
overdraw the account.)


Apr 13 '06 #7

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

Similar topics

10
9875
by: Jacek Pop³awski | last post by:
Hello. I am going to write python script which will read python command from socket, run it and return some values back to socket. My problem is, that I need some timeout. I need to say for example: os.system("someapplication.exe") and kill it, if it waits longer than let's say 100 seconds
6
1670
by: Dakkar | last post by:
How can i control my program when someone kill it's process from the taskmanager my dispose script is working well if i close the program manually but if i kill it from taskmanager its not working how can i fix it my dispose script is like this protected override void Dispose(bool Disposing) { if (Disposing)
0
1147
by: Shruti A via .NET 247 | last post by:
hello group I have recently started working on .Net platform. I am facing one problem in killing and starting process from my aspx page. I am able to kill and start the same process from vb.net but failed to do it from asp.net the exception that i am getting is :: "Couldn't get process information from remote machine." although i am doing it from the same machine from where i did it successfully using vb.net. don't know the route...
3
2005
by: kunal.kewalramani | last post by:
I'm opening an Excel file using ASP.NET, but Excel process remains it is not killed, I tried killing it by using Quit() that is also not working, if anybody have any solution for this please help me out. ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
10
8483
by: shiry | last post by:
Hi, I need to do some important cleanup before my console application exists. I used the console ctrl event. This is working well and it fires for all cases, including the CTRL_CLOSE_EVENT (if I close the application from the X botton). But on the contrary to what the MSDN indicates (ms-help://MS.VSCC.2003/MS.MSDNQTR.2003OCT.1033/dllproc/base/handlerroutine.htm), ending my process from the task manager did not fire the CTRL_CLOSE_EVENT....
8
4731
by: Rain | last post by:
Does anyone know how i can kill a process using C#? im developing an asp.net web application using C# and the asp.net process doesnt die even if the application has already exited. So i need to kill the process someone.. does anyone know the code for this? thank you so much in advance!!!!
2
6229
by: tony.newsgrps | last post by:
Hi there, I'm trying to understand the impact of killing a process that owns a system mutex (used to ensure there is only 1 instance of my program running) Here is my code pretty much: try { mutex=new System.Threading.Mutex( true, mutexName, out createdNew)
4
4602
by: Thomas Nelson | last post by:
Hi, I'd like to start a program, run it for a while, then terminate it. I can do this on linux, but I'm new to working with windows. Here's my script: from subprocess import Popen from time import sleep import win32api war3game = Popen() sleep(30) print "slept for 30"
6
1906
by: Roger Heathcote | last post by:
sjdevnull@yahoo.com wrote: <snip> Fair point, but for sub processes that need to be in close contact with the original app, or very small functions that you'd like 100s or 1000s of it seems like a kludge having to spawn whole new processes build in socket communications and kill via explicit OS calls. I can't see that approach scaling particularly well but I guess there's no choice. Does anyone think it likely that the threading...
0
8713
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9203
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...
1
8957
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8912
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6556
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
5897
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
4396
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4650
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2379
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.