SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK, Part 2By Michael Balloni
In Part 1 we discussed what row-locking was, why it was done, and its impact on database performance. In this part we'll examine two query-level hints you can provide SQL Server with to specify how you want SQL to handle locking!
NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means
you completely circumvent the lock system, which is a major performance and scalability improvement. However,
you also completely circumvent the lock system, which means your code is living dangerously. You might read the
not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.
For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique. But I think you'll find that for better than 90% of your application, it would not be that big of a deal if a user (or even intermediate code) saw an uncommitted modification. In fact, you'll probably find that most of your data never or only very rarely changes, in which case the overhead of locking the data is almost always completely wasted.
For example, if I want to count all users that joined Streamload.com between June 1 and August 31 of Y2K, there's no reason for me to lock anything: that number was cast in stone the moment September 1, 2000 rolled around. Another example is the file listings you see on Streamload.com: it doesn't much matter if you don't see the exact perfect data, since either you don't own the data and it doesn't much matter what you see, or you do own the data and you know perfectly well whether you just modified the data or not and whether new files have finished uploading.
Just don't use this type of data as the basis for modifications to the database, and don't use it when it's really important that the user not see the wrong thing (an account statement or balance, for instance).
ROWLOCK politely asks SQL Server to only use row-level locks. You can use this in
DELETE statements, but I only use it in
DELETE statements. You'd think that an
UPDATE in which you specify the primary key
would always cause a row lock, but when SQL Server gets a batch with a bunch of these, and some of them happen to
be in the same page (depending on this situation, this can be quite likely, e.g. updating all files in a folder,
files which were created at pretty much the same time), you'll see page locks, and bad things will happen. And if
you don't specify a primary key for an
DELETE, there's no reason the database
wouldn't assume that a lot won't be affected, so it probably goes right to page locks, and bad things happen.
By specifically requesting row-level locks, these problems are avoided. However, be aware that if you are wrong and lots of rows are affected, either the database will take the initiative and escalate to page locks, or you'll have a whole army of row locks filling your server's memory and bogging down processing. One thing to be particularly aware of is the "Management/Current Activity" folder with Enterprise Manager. It takes a long time to load information about a lot of locks. The information is valuable, and this technique is very helpful, but don't be surprised if you see hundreds of locks in the "Locks/Processes" folder after employing this technique. Just be glad you don't have lock timeouts or deadlocks.
I get the sense that SQL Server honors
NOLOCK requests religiously, but is more discretional with
You can only use
SELECT statements. This includes inner queries, and the
SELECT clause of the
You can and should use
NOLOCK in joins:
It's difficult to quantify the performance gain had by applying these techniques to Streamload.com, and impossible to speculate as to the effects this would have on your site. Before we did it, the site was slow, often unusable, and always unreliable. After we did it, the site was fast, usable, and reliable. Truly, it was a night and day improvement. And you won't find this if you go searching through the documentation for help with lock contention. The docs recommend rewriting your app so that tables are referenced - and hence, locks are attained - in the same order throughout (yeah, right!), keeping transactions short and in one batch (a good idea, but in practice "yeah, right!"), use a low isolation level (also a good idea: NOLOCK takes this to an extreme), and use bound connections to allow processes to (share locks and) cooperate (sounds like a very complicated bad idea). I don't get the sense the consultants of the world are aware of (or comfortable with?) this technique either. But you heard it here, and it's worked great for Streamload.com. If you're having lock contention problems with SQL Server, it could work for your site, too.
Use these techniques with caution and discretion. The way I approached it was to look at all my stored procedures and ad hoc queries, and based on my understanding of where and how they were used, I decided whether it would be acceptable for the caller or user to get possibly incorrect results for
NOLOCK, and whether
it was likely that more than a few dozen rows would be locked with
ROWLOCK. In almost all cases it
was fine, but maybe for your code you should be more careful. You might need to produce separate procedures based
on whether or to lock, and how to lock. There are other incantations (
which you might want to use when you know the
DELETE query will affect many rows.
These and many other cutting-edge techniques power Streamload.com, a digital entertainment delivery site offering unlimited free online storage. Upload all your files from your computer - and a bevy of media-savvy services including de-digitization (CD burning), and a full micro-transaction digital commerce engine (create memberships, sell files to subscribers and the world). This is not another drive site: This is your digital entertainment, online.