title: SQL Server database: INSERT query blocked with wait type LCK_M_IX
body: <p>Our Java application cleans up the data from log table through a cron job. Here is the query which is executed for clean up:</p>
  
  <pre><code>DELETE FROM AU_TRANSACTIONDATA 
  WHERE AU_ACTIVITYENDTIME != 0 
    AND AU_ACTIVITYENDTIME &lt;= 1464570001151
  </code></pre>
  
  <p>We have an index on <code>AU_ACTIVITYENDTIME</code> column of this table:</p>
  
  <pre><code>CREATE INDEX [IX_AU_TRANSDATA_ENDTIME] 
      ON [AU_TRANSACTIONDATA]([AU_ACTIVITYENDTIME]) ON [PRIMARY];
  </code></pre>
  
  <p>Our application dumps transaction data (generated on execution of APIs in our application) into this table. Here is the INSERT query:</p>
  
  <pre><code>INSERT INTO AU_TRANSACTIONDATA (AU_TRANSACTIONID, AU_TRANSACTIONNAME, AU_TRANSACTIONDOMAINID, AU_ACTIVITYNAME, AU_ACTIVITYID, AU_ACTIVITYPID, AU_ACTIVITYTYPE, AU_ACTIVITYSTARTTIME, AU_ACTIVITYENDTIME, AU_ACTIVITYSTATUS, AU_CORRECTDATA, AU_ERRORDATA, AU_USERID, AU_GROUPID, AU_NODENAME, AU_TRANSACTIONDESCRIPTION, AU_SEQUENCEID, AU_TRANSSEQUENCEID) 
  VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17)
  </code></pre>
  
  <p>We are facing issue when log clean up (<code>DELETE</code> query) and data processing (<code>INSERT</code> query) happens simultaneously. We had around 1.5 million records for clean up, so delete query took some time to clean up the log records. But during that time, processing was blocked and no <code>INSERT</code> queries went through. </p>
  
  <p>Here are the logs:</p>
  
  <pre><code>SPID at Head of Blocking Chain:
  
  SPID [ecid]: 3524 [0]
  
  Blocked by SPID: 0
  
  Client Machine: xxxxx
  
  Client Process ID: 123
  
  Application: jTDS
  
  Login Name: xxxx
  
  Last Batch: 5/30/2016 9:06:56 PM
  
  Wait Type:
  
  Wait Resource:
  
  Wait Time: 00:00:00
  
  Database:
  
  Command Text:
  
  DELETE FROM AU_TRANSACTIONDATA 
  WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME &lt;= 1464570001151
  ----------------------------------------------------------------------
  
  Blocked SPID:
  
  SPID [ecid]: 211 [0]
  
  Client Machine: xxxxx
  
  Client Process ID: 123
  
  Application: jTDS
  
  Login Name: xxxxx
  
  Last Batch: 5/30/2016 9:06:56 PM
  
  Wait Type: LCK_M_IX
  
  Wait Resource: AU_TRANSACTIONDATA.IX_AU_TRANSDATA_ENDTIME
  
  Wait Time: 00:00:24
  
  Database: xxxx
  
  Command Text:
  
  INSERT INTO AU_TRANSACTIONDATA (AU_TRANSACTIONID, AU_TRANSACTIONNAME, AU_TRANSACTIONDOMAINID, AU_ACTIVITYNAME, AU_ACTIVITYID, AU_ACTIVITYPID, AU_ACTIVITYTYPE, AU_ACTIVITYSTARTTIME, AU_ACTIVITYENDTIME, AU_ACTIVITYSTATUS, AU_CORRECTDATA, AU_ERRORDATA, AU_USERID, AU_GROUPID, AU_NODENAME, AU_TRANSACTIONDESCRIPTION, AU_SEQUENCEID, AU_TRANSSEQUENCEID) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 )
  </code></pre>
  
  <p>Logs shows that INSERT statements are waiting for resource AU_TRANSACTIONDATA.IX_AU_TRANSDATA_ENDTIME (which denotes an index) with wait type as LCK_M_IX. In some logs, we have seen wait resource to be AU_TRANSACTIONDATA which is table itself.</p>
  
  <p>Can you please explain the following:</p>
  
  <ol>
  <li>Why INSERT queries are getting blocked when <code>DELETE</code> query for log clean up is executing?</li>
  <li>What is meant by <code>LCK_M_IX</code> wait type and how it can be resolved?</li>
  <li>Is <code>DELETE</code> query locking the whole table or places exclusive (X) lock on index?</li>
  </ol>
  
  <p>I am not familiar with wait and locking strategies in SQL Server, so any help in this regard shall be appreciated.</p>
  
  <p><strong>EDIT</strong>: We already tried to delete the data in chunks i.e. 10000 rows at a time but it didn't help the cause. Here is the new DELETE query:</p>
  
  <pre><code>SET ROWCOUNT 10000
  delete_more:
       DELETE FROM AU_TRANSACTIONDATA 
       WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME &lt;= 1464570001151
  
  IF @@ROWCOUNT &gt; 0 GOTO delete_more
  SET ROWCOUNT 0
  </code></pre>