title: How to avoid Deadlock between Insert/Delete statements due to non clustered non unique indexes!
body: <p>Recently I have come across a deadlock scenario on OLTP box (Sql server 2005) of a client 
  and found that it's caused by two stored procedures being called by 2 different threads.</p>
  
  <p>1, Insert sp that inserts data in X table.</p>
  
  <pre><code>Insert Into X (col1 , col2  , col3  ) 
  Values ('value 1' , 'value 2'  , 'value 3'  )
  </code></pre>
  
  <p>2, Delete sp that deletes data from X table.</p>
  
  <pre><code>  DELETE X  
    FROM X T1 WITH (NOLOCK)   
    INNER JOIN Y T2 WITH (NOLOCK)
    ON T1.[col2] = T2.[col2]  
     WHERE t2.date &lt; 'date time value'  
  </code></pre>
  
  <p>X table has one unique, clustered primary key and two non clustered, non unique indexes. 
  I have analysed the deadlock by setting t1222 tace flag on and the output is summarized below; </p>
  
  <p>Insert sp acquired a IX lock on Non clustered index for Column 1.
      Delete sp is waiting for X lock on the same Non clustered index for column 1 during this time.</p>
  
  <p>Delete sp acquired a U lock on Non clustered index for Column 2.
      Insert sp is waiting for a IX lock on the same Non clustered index for Column 2 during this time.</p>
  
  <p>Any idea or suggestion to avoid deadlock would be really appreciated.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>output of trace flag t1222</p>
  
  <pre><code>deadlock-list  
  deadlock victim=process3c77d68  
  process-list  
  process id=process3c12c58 taskpriority=0 logused=1044 waitresource=PAGE: 17:8:7726 waittime=1250 ownerId=5169682909 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.443 XDES=0xfe64d78b0 lockMode=IX schedulerid=2 kpid=9544 status=suspended spid=219 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:34:03.457 lastbatchcompleted=2011-02-03T03:34:03.453 clientapp=.Net SqlClient Data Provider hostname=HQMTSRV026 hostpid=3308 loginname=EASUser isolationlevel=read committed (2) xactid=5169682909 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056  
  executionStack  
  frame procname=adhoc line=1 stmtend=296 sqlhandle=0x0200000084ce2a1d0e95a5623fa3a9c0981d422e33cab999  
  (@1 int&lt;c/&gt;@2 varchar(8000)&lt;c/&gt;@3 nvarchar(4000))INSERT INTO [VB_Audit_TransactionDetail]([ItemID]&lt;c/&gt;[TransactionID]&lt;c/&gt;[ItemValue]) values(@1&lt;c/&gt;@2&lt;c/&gt;@3)
  frame procname=adhoc line=1 stmtend=296 sqlhandle=0x02000000afcb1733f435fb93e13556600acf32bb32e10020
  Insert Into VB_Audit_TransactionDetail (ItemID  &lt;c/&gt; TransactionID  &lt;c/&gt; ItemValue  ) Values (4 &lt;c/&gt; '0255978c-f56e-477e-b361-8abe62433cff'  &lt;c/&gt; N'HQOLB006'  )  
  frame procname=EAS.dbo.SP_Insert line=13 stmtstart=482 stmtend=522 sqlhandle=0x03001100805efa5997d69400719600000100000000000000  
  exec (@CommandText)  
  inputbuf  
  Proc [Database Id = 17 Object Id = 1509580416]  
  process id=process3c77d68 taskpriority=0 logused=364 waitresource=PAGE: 17:6:334008 waittime=1234 ownerId=5169682116 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.053 XDES=0xa8e297cd0 lockMode=X schedulerid=12 kpid=10300 status=suspended spid=327 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:33:41.137 lastbatchcompleted=2011-02-03T03:33:41.133 clientapp=Microsoft SQL Server hostname=HQSSISSRV002 hostpid=7632 loginname=NBKDOM\SQLCSRVC isolationlevel=read committed (2) xactid=5169682116 currentdb=17 lockTimeout=4294967295 clientoption1=671350816 clientoption2=128056  
  executionStack  
  frame procname=EAS.dbo.PurgeAuditTransactionTables line=59 stmtstart=4202 stmtend=4728 sqlhandle=0x030011006354a2313d11ae00979a00000100000000000000  
  DELETE [dbo].[Audit_TransactionDetail]  
  FROM [dbo].[Audit_TransactionDetail] T1 WITH (NOLOCK)  
  INNER JOIN [dbo].[Audit_NBKTransaction] T2 WITH (NOLOCK)ON T1.[TransactionID] = T2.[TransactionID]  
  WHERE TransactionPostedDateTime &lt; @LastReplicationDateTime  
  frame procname=adhoc line=1 sqlhandle=0x0100110096968c0560c430ff190000000000000000000000  
  EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870'  
  inputbuf  
  EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870'  
  resource-list  
  pagelock fileid=8 pageid=7726 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock4f79500 mode=U associatedObjectId=886415243542528  
  owner-list  
  owner id=process3c77d68 mode=U  
  waiter-list  
  waiter id=process3c12c58 mode=IX requestType=wait  
  pagelock fileid=6 pageid=334008 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock846afca00 mode=IX associatedObjectId=604940266831872  
  owner-list  
  owner id=process3c12c58 mode=IX  
  waiter-list  
  waiter id=process3c77d68 mode=X requestType=wait  
  </code></pre>
  
  <hr>
  
  <p>One more important thing; the delete and insert statements always touches 2 different sets of data. </p>