2

I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query shown below:

SELECT  MAX([ModifiedDate])

FROM    [dbo].[LogEvent] WITH(NOLOCK) 

WHERE   [CompanyId] = '00000000-0000-0000-0000-000000000000'
AND     [UserId] = '00000000-0000-0000-0000-000000000000'
AND     [ComponentId] = 64
AND     [LogLevel] >= 70
AND     [LastUpdate] >= '2016-01-01'

However it seems that when the bulk inserts try to obtains their IX and X exclusive locks they are blocked by the select query and therefore fail.

I believe this is because the LogEvent table is a heap table with no clustered index and therefore even with NoLock a HOBT shared lock is taken out.

Will the HOBT shared (S) lock prevent inserts or does this only apply to lower level locks?

If so could I fix this simply by adding a clustered index?

This is very similar to this question Shared Lock issued on IsolationLevel.ReadUncommitted but I'm looking for an explicit confirmation that this will prevent inserts?

Underscore
  • 121
  • 1

0 Answers0