SQL Server deadlock issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. I am wondering whether dead lock issue is only caused by cross dependencies (e.g. task A has lock on L1 but waits on lock on L2, and at the same time, task B has lock on L2 but waits on lock on L1)? Are there any other reasons and scenarios which will cause deadlock?

Are there any other way which will causes dead lock -- e.g. timeout (a S/I/D/U statement do not return for a very long time, and deadlock error will be returned) or can not acquire lock for a long time but not caused by cross-dependencies (e.g. task C needs to get lock on table T, but another task D acquire the lock on table T without releasing the lock, which causes task C not be able to get lock on table T for a long time)?

EDIT 1: will this store procedure cause deadlock if executed by multiple threads at the same time?

create PROCEDURE [dbo].[FooProc]    
(  
 @Param1 int 
 ,@Param2 int  
 ,@Param3 int  
)    
AS    

DELETE FooTable WHERE  Param1 = @Param1     

INSERT INTO FooTable    
 (  
 Param1  
 ,Param2  
 ,Param3  
  )    
 VALUES    
 (  
 @Param1  
 ,@Param2  
 ,@Param3  
  )    

DECLARE @ID bigint    
 SET @ID = ISNULL(@@Identity,-1)    
 IF @ID > 0    
 BEGIN    
      SELECT IdentityStr FROM FooTable WHERE ID = @ID 
 END  

thanks in advance, George

Answer 1

Simply not releasing a lock for a long time is not a deadlock.

A deadlock is a situation where you can never go forward. It's caused by 2 (or more) processes that are waiting for others to finish but all those involved are holding a lock that is preventing the other(s) from continuing.

The only way out of a deadlock is to kill processes to free the locks as it doesn't matter how long you wait, it can not complete on it's own.

Answer 2

Deadlocks require a cycle where resources are locked by processes that are waiting on locks held by other processes to release the locks. Any number of processes can participate in a deadlock, and the normal method for detecting deadlocks is to take a graph of the dependencies on the locks and search for cycles in that graph.

You need to have that cycle for a deadlock to exist. Anything else is just a process held up waiting for a lock to be released. A quick way to see what processes are being blocked by others is sp_who2.

If you want to troubleshoot deadlocks, the best way is to run a trace, picking up 'deadlock graph' events. This will allow you to see what's going on by telling you what queries are holding the locks.

Answer 3

Also there are conversion deadlocks: both processes A and B have shared locks on resource C. Both want to get exclusive locks on C.

Even if two processes compete on only one resource, they still can embrace in a deadlock. The following scripts reproduce such a scenario. In one tab, run this:

CREATE TABLE dbo.Test ( i INT ) ;
GO
INSERT  INTO dbo.Test
        ( i )
VALUES  ( 1 ) ;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN TRAN
SELECT  i
FROM    dbo.Test ;

--UPDATE dbo.Test SET i=2 ;

After this script has completed, we have an outstanding transaction holding a shared lock. In another tab, let us have that another connection have a shared lock on the same resource:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN TRAN
SELECT  i
FROM    dbo.Test ;

--UPDATE dbo.Test SET i=2 ;

This script completes and renders a result set, just like the first script did. Now let us highlight and execute the commented update commands in both tabs. To perform an update, each connection needs an exclusive lock. Neither connection can acquire that exclusive lock, because the other one is holding a shared lock. Although both connections are competing on only one resource, they have embraced in a conversion deadlock:

Msg 1205, Level 13, State 56, Line 1 Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Also note that more than two connections may embrace in a deadlock.