SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK

Lock Contention Solved

If you visited the website Streamload.com at all during June, July, and August of this year, you probably got a "You were the deadlock loser" error, or a "Lock timeout" error, or an "Object required" error. These were all caused by lock contention. After scouring the documentation and talking to a few people, I learned what I have summarized above and will say again here: 

"SQL Server starts with row-level locks, but often escalates these to page and table locks, causing deadlocks."

SQL Server requires locks for reading from the database (SELECTs), so even folks not trying to modify the database are affected by the lock system. Fortunately, I stumbled across some obscure keywords from the SQL Server lexicon: NOLOCK and ROWLOCK. 

They are used like this: 

SELECT COUNT(UserID)
FROM Users WITH (NOLOCK)
WHERE Username LIKE 'foobar' 

and 

UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar' 

Using NOLOCK

Using 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).