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?

4
  • 1
    For people who don't know what HOBT means.
    – Erik
    Commented Apr 1, 2016 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
    Commented Apr 1, 2016 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 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
    Commented Apr 4, 2016 at 11:51
  • any updates ont his?
    – variable
    Commented May 6, 2022 at 5:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.