We help IT Professionals succeed at work.

How 2 resolve deadlock in SQL Server

saratcm
saratcm used Ask the Experts™
on
Hi All,
How 2 identify deadlock in SQL Server?
How 2 resolve deadlock in SQL Server?
How 2 find which process has to be stopped in case if 2 process creating a deadlock?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How 2 identify deadlock in SQL Server?
SQL Server will do that for you. ;-)
How 2 resolve deadlock in SQL Server?
SQL Server will do that for you, too, by aborting one of the SQL statements. ;-)

How 2 find which process has to be stopped in case if 2 process creating a deadlock?
Don't worry about it, SQL Server will do that for you, too. ;-)
Essentially, the only way you will get deadlocks is if two (or more) statemenst are both trying to access the same data and one of tham has locked it.  In that case, the one that has not locked the data will be cancelled.  Now, that can be a situation where a row, data-page, table, index row, or index page is locked, depending on the default or "hinted" lockingof one or both statements.
Whenever SQL Server (or, for that matter, most other more robust database engines) determines that tehre is a locking situation, it may wait a little bit at first but, if the locking is persistent, it will cancel whichever query is most easily cancelled (e.g. a SELECT vs an UPDATE).  (You can get an idea as to which queries are having problems if you use a tool like PerfMon or Confio's Ignite.)
 

Author

Commented:
Thanks Diver
What in case if none of the processes involved in deadlock have Xclusive lock on the resource?
for which process sql server will assign the resource?
Saratcm,
It kind of depends on a) what you mean by having an "Xclusive lock on the resource" and what the various processes are trying to do.  However, if there is a "deadlock" condition, then, by definition, at least one of the queries is being prevented from doing what it needs to do.  
Most commonly, SQL Server will tend to favor whoever got the first lock that is causing the lock-out and will abort the other processes.  However, that being said, the SQL Server engine has some sort of weighting heuristic/algorythm that is used to make the decision and it will, effectively, select which ever seems to be "appropriate" and abort that one.
This is why one learns to:
  • Create covering indexes (so that the index has the data necessary for the query);
  • Minimize the time during which rows/pages/tables are locked;
  • NOT run bulk loads or massive transactions/reports during the "normal business hours";
  • Carefully control how one is accessing data in tables so that one doesn't inadvertantly open a table in edit mode and lock the whole thing.

Author

Commented:
Thanks!!!

Author

Commented:
So Diver,
we don't need to do anything when deadlock occurs?
No need to run the SQL Server trace?
No need to kill some process or do nthing?
saratcm,
we don't need to do anything when deadlock occurs?
Well, in one sense, no, you don't.  SQL Server will "resolve" the deadlock(s) for you.  
However, in another sense, you do need to do something.  What you need to do is not so much targetted at dealing with a deadlock that has occurred but, rather, at dealing with future deadlocks.  You need to analyze the data you have regarding the deadlock (and, if you don't have any more than the error message, you need to get more ;-) in order to figure out the circumstances under which the deadlock occurred.
If you were doing someting abnormal, e.g. importing an abnormally large amount of data or something like that, you need to be aware that similar actions under similar circumsatnaces in the future could produce similar deadlocks.
If the database was being used in a "normal" manner, then you need to start looking for why the deadlocks occurred and trying to optimize your database and/or queries/indexes, etc., in order to address the problem because, as time goes on, it will only get worse.

Author

Commented:
Thanks Diver,
But how much time will the deadlock exists before the SQL Server decides to kill one of the process to release the deadlock.
bcoz, according to you to analyse the data causing the deadlock, we should be able to find the processes
SQL Server is not very patient with deadlocks.  By the time you know one has happened, SQL Server will have "resolved" it by picking one of the processes to sacrifice, so to speak.
The analysis will have to be done using tools like PerfMon, Trace, or Ignite.  The bare essentials wll appear in event logs but that is really not all that much help.  
Tacking down the cause of deadlocks, especially if they are not happening on a regular basis at a regular time, closely resembles Crimes Scene Investigations. ;-)
 

Author

Commented:
Thank U so much Mr.Diver!!