title: Stored procedure SQL Server 2005 transaction deadlock problem
body: <p>I have a problem while running sql stored procedure (MSSQL 2005). Here is the code of it:</p>
  
  <pre><code>CREATE PROCEDURE [GetItemColors]
      @FetchCount int
  AS
  BEGIN
  SELECT 
      TOP (@FetchCount) item.Id as ItemId, item.Published as Published, attributeValue.String as Color
      FROM tblItem item, tblAttribute attributeValue, tblAttributeDefinition attributeDef
      WHERE 
          item.Id = attributeValue.fkItemId 
          AND item.Deleted = 0
          AND item.PendingPublish = 0 
          AND attributeValue.fkAttributeDefinitionId = attributeDef.Id
          AND attributeDef.[Name] = 'Color'
      ORDER BY item.Published DESC
  END;
  </code></pre>
  
  <p>Some times the execution of the procedure ends with exception: </p>
  
  <pre><code>SqlException: Transaction (Process ID 66) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  </code></pre>
  
  <p>Does anyone know how to rewrite the procedure to avoid the exception?</p>
  
  <p>Is there anyway of 'pre-locking' tables within the stored procedure invocation so other code can't interrupt the execution?</p>
  
  <p>Thanks in advance for you help!</p>
  
  <hr>
  
  <p>edit 1: Estimated execution plan attached</p>
  
  <p><img src="https://i.stack.imgur.com/yM5Md.png" alt="estimated execution plan"></p>
  
  <hr>
  
  <p>edit 2: I <strong>can't</strong> get Deadlock Graph as the exception happens couple times per day in the live environment only. I'm not allowed to run profiler in live environment. I didn't manage to reproduce the deadlock in development environment.</p>
  
  <hr>
  
  <p>edit 3: I didn't managed to get the deadlock winner.</p>