SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 07:05 PM
| ||||
| ||||
 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.      |
|
02-29-2008, 07:05 PM
| |||
| |||
| Re: Killing the process automatically 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 |
|
02-29-2008, 07:05 PM
| |||
| |||
| Re: Killing the process automatically 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 |
|
02-29-2008, 07:06 PM
| |||
| |||
| Re: Killing the process automatically laststubborn (arafatsalih@gmail.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, esquel@sommarskog.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 |
|
02-29-2008, 07:06 PM
| ||||
| ||||
| Re: Killing the process automatically "D0MZE" <domze.sa@gmail.com> wrote in message news:1144891406.165135.214850@i40g2000cwc.googlegr oups.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.) > |
Linear Mode
