title: SQL Server deadlock issue
body: <p>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?</p>
  
  <p>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)?</p>
  
  <p>EDIT 1: will this store procedure cause deadlock if executed by multiple threads at the same time?</p>
  
  <pre><code>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 &gt; 0    
   BEGIN    
        SELECT IdentityStr FROM FooTable WHERE ID = @ID 
   END  
  </code></pre>
  
  <p>thanks in advance,
  George</p>