Does SELECT query lock a table or a page in SQL Server?

Question

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.

The blocked session query is;

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)

The blocking session query is;

SELECT * FROM AuditDB.dbo.AUDITHISTORYLOG_BACKUP_2017_1 WHERE CREATE_DATE>CAST(GETDATE()-30 AS DATE) ORDER BY CREATE_DATE DESC

How does this select query block the insert transaction ?

Wait_Type: LCK_M_IX
Wait_Resource: PAGE: 10:1:20598647
Transaction Isolation Level: Read Committed

Can anyone help ?

Answer 1

How does this select query block the insert transaction ?

Yes it can cause the types of locks are not compatible. A SELECT query requires SHARED lock whereas INSERT requires EXCLUSIVE lock and both are not compatible. That is, a shared lock if present on the same resource (in your case AUDITHISTORYLOG_BACKUP_2017_1 table) on which exclusive lock is requested; that exclusive lock can't be granted until the shared lock is taken off or shared lock have been released.