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.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|||Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??
Thanks|||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|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.|||"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.)
