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.

2010年2月6日 星期六

Mysterious Hashtable Exception "Hashtable insert failed. Load factor too high"

Background
Our production servers (.NET 2.0) ran into this exception suddenly a few months ago. It came from a snippet of codes updating Hashtable. Weird enough, the web sites are running for a long while and, and from SVN logs, no evidence shows anyone modified associated codes recently. More strange, the exception appears merely on a couple of web servers.

Even though I've checked with our system administrators that http://support.microsoft.com/kb/968432 is updated on the offending servers, the exception still emerged intermittently (You might search on the web to see somebody declaring this can be resolved by the KB).

Let's begin the measure that I adopted to tackle it in our environment.

Workaound
Restart IIS - this bothers pretty much our supporting engineers.

Solution
Referring to this thread, a master points out the exception might arise when multiple threads concurrently modify the Hashtable. True enough, our web sites use the Hashtable as a cache, and around 4 threads will modify it quite frequently. So I warp the updating codes around with lock keyword as follows:

readonly static object _sync = new object();
...
lock (_sync)
{
// codes modifying Hashtable
}

After the fix updates to production servers, the exception disappears for more than two weeks, looking like the solution really works out for our environment!