Another Reason not to use NOLOCK query hint

Just say 'No'

When there is corruption in a database (or) when scanning the data with the NOLOCK query hint (or) with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to have been deleted or moved by page splits caused by Inserts/Updates/Deletes making SQL Server not able to scan further and cause

Error 601: Could not continue scan with NOLOCK due to data movement.

This is a “by design” behavior. The error messages are being issued from SQL Server to alert the client that data in the query was updated while the read was executing, rendering the data inconsistent and invalid.¬† NOLOCK allows for data to be read¬† while another process updates the data. It was never designed to read data from a field while that specific field was being updated. “SNAPSHOT” isolation level will allow this, by creating a separate copy of the data in TempDB for the duration of the read. The other fix is to simply remove the NOLOCK query hint

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s