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?

Community
  • 1
Underscore
  • 121
  • 1
  • 1
    For people who don't know what HOBT means. – Erik Apr 01 '16 at 18:16
  • It will cause blocking as it acquires schema stability lock. Have you looked into RCSI ? Also, dont use nolock instead use read uncommitted isolation level. A proper index (if you put your table ddl), we can recommend. – Kin Shah Apr 01 '16 at 19:42
  • @Kin but the Schema Stability lock shouldn't cause locking, only Schema Modification locks will cause that? It looks like the bulk copy should be acquiring an IX-Table lock in my case (non-empty table without tablock https://msdn.microsoft.com/en-us/library/ms177445(v=sql.105)#Anchor_3). RCSI looks like the way to go, I've managed to get a copy of the affected database so can try it out. – Underscore Apr 04 '16 at 11:51
  • any updates ont his? – variable May 06 '22 at 05:45

0 Answers0