Deadlock

Problem

I am getting this error very frequently.

Runtime error '-2147467259(80004005)'

Transaction (Process Id 81) was deadlock on lock resources with another process and has been chosen as the deadlock victim.Rerun the transaction.

Solution

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

• Transaction A acquires a share lock on row 1.
  
  
• Transaction B acquires a share lock on row 2.
  
  
• Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
  
  
• Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.
  
  

Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. This allows the other task to complete its transaction. The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

You need to find out what these processes are to minimize/eliminate the chance they will dead lock. To do this you can run a profile trace (see:http://msdn.microsoft.com/en-us/library/ms188246.aspx) or you can use the sp_blocker_pss08 stored procedure to gather the information you need - you can go here for that:http://support.microsoft.com/kb/271509. You might also want to run a profile trace at the same time the sp_blocker_pss08 is running to gather what stored procedures (and the parameters they are passed) and sql batches are running during that time as well.

check out these tips:

http://www.mssqltips.com/tip.asp?tip=1036

http://www.mssqltips.com/tip.asp?tip=1210

http://www.mssqltips.com/tip.asp?tip=1222

http://www.mssqltips.com/tip.asp?tip=2130

http://www.mssqltips.com/tip.asp?tip=1234