Lock Contention Solved
If you used Streamload.com at all during June, July, and August, 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 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'
|
What do these extra incantations do? We'll examine these two hints - NOLOCK
and ROWLOCK - on the next page!