2010年2月27日 星期六

System.Data.SqlClient.SqlError: Could not continue scan with NOLOCK due to data movement.

Background
I constantly found this exception making occasional appearance in our production server error log, Although i cannot ensure it was connected to a bug we're looking into, I was determined to dig the root cause out.

I surfed through the web, but could not find the reasons related to our scenarios, so I consulted a experienced DBA in our company.

Finding
Inside the offending SPROC, the author use NOLOCK to avoid lock burdens put on querying many tables. Moreover, these tables are updated/deleted regularly by replication services. In consequence, if the data rows retrieved - by SELECT (NOLOCK) - are deleted by replication services during the process, this exception emerges.

PS: Be aware it's separate from dirty read that you mostly hear around NOLOCK.

Reference
For folks who is interesting in lock levels available in SQL server, refer to Locking in Microsoft SQL Server.

沒有留言:

張貼留言