SQL Server and NOLOCK on queries...

[CrazyVBr]

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not continue scan with NOLOCK due to data movement

I get this error off and on... is there something I should know more about when it comes to using NOLOCK. I have people constantly hitting the server using standard, non-complex search queries... but that data CAN change at a moments notice...

I just need a direction in which to look. MS doesn't have anything I can find on this matter. Thank you all in advance

Thanks,
CrazyVBr

 

[JayKusch]

Here we go ...

What it says. You are using NOLOCK, which means that you are not honouring exclusive lock. This means if another process is performing an INSERT/UPDATE/DELETE statement on the table, which leads to data being shuffled around, for instance due to update of the columns clustered index or page splits, the process which is not honouring locks may suddenly find itself in the middle of nowhere.

NOLOCK is nothing to be used lightly, and there might be serious implications using it.

In this case I would suggest that you look into the NOLOCK query, and see if you can add an index to speed it up. You may then be able to remove NOLOCK.

Depending on your query, you may also be interesting in trying the READPAST hint.

BOL may be a good place to read up on this info.

Thanks

J. Kusch

 

[nikhilparchure]

Suggest not to use NOLock will dealing with financial data.
Generally these are used with reports that travel long queries and resultsets , but unless very trivial and non-financial information like customer information etc.
NoLock should not be used in heavy hitting databases.