title: Does SELECT query lock a table or a page in SQL Server?
body: <p>We have just upgraded our production sql instance from 2012 to 2016 Standard Edition. As we have been working hard to find deadlocks in case it exists, i have just faced one and didnt quite understand what is exactly happening. The reason i did not understand the issue is that one session is blocking another session but the blocking session is a select query session. it prevents another session to insert the table.</p>
  
  <p>The blocked session query is;</p>
  
  <pre><code>INSERT INTO [AUDITHISTORYLOG_BACKUP_2017_1]([TABLE_NAME],[OPERATION_TYPE],[HOST_NAME],[USER_NAME],[PRIMARY_KEY],[FIELD],[OLD_VALUE],[NEW_VALUE],[CREATE_DATE]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9)
  </code></pre>
  
  <p>The blocking session query is; </p>
  
  <pre><code>SELECT * FROM AuditDB.dbo.AUDITHISTORYLOG_BACKUP_2017_1 WHERE CREATE_DATE&gt;CAST(GETDATE()-30 AS DATE) ORDER BY CREATE_DATE DESC
  </code></pre>
  
  <p>How does this select query block the insert transaction ? </p>
  
  <p>Wait_Type: LCK_M_IX<br>
  Wait_Resource: PAGE: 10:1:20598647<br>
  Transaction Isolation Level: Read Committed</p>
  
  <p>Can anyone help ? </p>