Using ROWLOCK
Using ROWLOCK politely asks SQL Server to only use row-level locks. You can use this in SELECT, UPDATE, and DELETE statements, but I only use it in UPDATE and 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 UPDATE or 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.
Additional Note
I get the sense that SQL Server honors NOLOCK requests religiously, but is more discretional with ROWLOCK requests. You can only use NOLOCK in SELECT statements. This includes inner queries, and the SELECT clause of the INSERT statement. You can and should use NOLOCK in joins, for example:
SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)
JOIN UsersInUserGroups WITH (NOLOCK) ON
Users.UserID = UsersInUserGroups.UserID
Results of Implementing NOLOCK and ROWLOCK
It’s difficult to quantify the performance gain had by applying these techniques to Streamload.com, and it is impossible to speculate as to the effects this would have on your website. 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.
Disclaimer
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 (PAGLOCK, TABLOCK) which you might want to use when you know the UPDATE or DELETE query will affect many rows.
Shameless Plug
These and many other cutting-edge techniques power www.streamload.com, a web storage and delivery site offering unlimited free online storage for your files. Upload all your files from your computer to gain format-savvy access to them from anywhere. Features include image slideshows, audio and video streaming and playlists, and streamlined download management. Also, share and sell your own digital creations. This is not another drive site: This is your digital entertainment, online.
Michael Balloni
Streamload Development
http://www.streamload.com
Article published with the express written permission of the author.
Copyright 2000 Michael Balloni
]]>