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.