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?