29 December 2013

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

Error:Msg 601: Could not continue scan with NOLOCK due to data movement.
Here are some of the causes for this error: 

1. When scanning with the NOLOCK locking 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 be deleted. When this happens, SQL Server is not able to continue the scan.

2.  SQL BLOB fields in combination with the NOLOCK query hint.“ Because BLOB fields can store big data amount (>8kb) it uses (in default) a pointer to storee this data as a stream outside the tablee page. Due to this pointer construction the reading of a blob field will be in streaming mode. During this strem the error handling is not the same as by normal sql fields. Due to this we get if a blob field is changd during the read process SQL server will close the stream and generate the NOLOCK error.

3.  We can experience this issue after changing some of  C# code that connects to the database.

4.  Index fragmentation causes excessive unnecessary operations which apparently conflict with "dirty" NOLOCK reads.

5.  NOLOCK means there is no lock when you doing the query using NOLOCK parameter, and it allows to read dirty data because there existing no consistence on the part of data, when do the query using NOLOCK on the tables which has frequent data change it may come out the error. This is not the data error, or bug, but just one phenomenon when reading data under NOLOCK model.

No comments:

Post a Comment