Monday, October 27, 2008

How to find the cause of deadlock in SQL Server?

If you are a database administrator or technical support person for a large application, you may have come across this problem. Sometimes users say that their application is hanging while doing some usual operations, like running report, fetching details, etc. The cause is due to dead lock in some table of SQL Server.

 

If a user at some end is generating Purchase Order and another user at other end is running a Purchase Order Report which fetches Purchase Order details, then this is a likely case of dead lock. These two processes most probably use the same tables. If a user is inserting in Purchase Order table, then he will lock the table stopping others to select data from there until the insertion completes. Usually the insertion takes in no time, so in small process, dead lock is rare case. But, in large applications there are processes which take hours to run and complete. So, if the process locks the tables and another user runs select on that table, then the later user is kept hold.

 

There may be some cases when a user process A (say) is waiting for another process B (say) to complete while B is waiting for A to complete. Then, this is a dead lock. None of the process completes and it makes system hang. This is also likely case when the application does not handle it properly. If A is locking some table1 and waiting to use table2, while B is locking table2 and waiting for table1 to release, it is a case of deadlock. You can find out who is dead locking the system by logging to ISQLW ( SQL Server Query Analyzer) and running sp_who and SP_WHO2 commands.

 

Example : sp_who2 'active'

 

This gives the spid, status, login, hostname, BlkBy (blocked by), DBname (Database Name),  etc. If BlkBy field is filled by some value, the value is the SPID of blocking process. Then find out row in output whose id is the blocking id. From the hostname and login you will be able to find out who is the blocking user. You can then call the user to ask what he is doing and tell him to run the process sometime after.

 

Output of sp_who2.

SPID

Status

Login

HostName

BlkBy

DBName

Command

51

sleeping                     

SURAJSHRESTHA\Suraj Shrestha

SURAJSHRESTHA

  . 

master

AWAITING COMMAND

53

sleeping                     

NT AUTHORITY\SYSTEM

SURAJSHRESTHA

  . 

msdb

AWAITING COMMAND

54

RUNNABLE                     

Tech

Techcreeze

  . 

TESTDB

UPDATE         

55

sleeping                     

sa

SURAJSHRESTHA

54 

TESTDB

AWAITING COMMAND

 

In above case, process 55 is blocked by process 54 run by login tech from computer with name Techcreeze.

 

1 comments:

bettercodes October 28, 2008 at 12:15 AM  

After you find oout the blocking process, you can terminate the process by using kill command.

Kill 54

where 54 is a SPID of a process.