Thursday, July 03, 2008

The joy of WITH (NOLOCK)

For a long while I thought it odd that reading from a SQL Server database caused the rows that were being read to be locked. Thinking about it for a little longer makes it clear that it's a necessary evil. You don't want some other process to update records as you're reading them, since the results you get back may well be inconsistent. But the thing is this often doesn't matter that much, so long as it's near enough correct. I've only recently come across WITH (NOLOCK) and I think it's wonderful. It's a quick and dirty way to boost the performance of your queries since it means the query can execute without acquiring a lock on the rows you're trying to get hold of.

Opponents of using it will say there are more fundamental problems with your database that could be fixed with some better indices, but sometimes we don't have the time to do proper performance analysis, so a quick fix is welcome. Purists will no doubt look down their noses at using this technique since it's just not the right thing to do. But I don't care, it improves performance for very little cost. Saying that, I do wonder how inconsistent the data may be. For instance, could I read a row that has a half-written text field? I haven't seen anything like this yet but I do wonder if it's possible.

No comments: