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 (
SELECT
s), 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)
|
and
UPDATE Users WITH (ROWLOCK)
|
What do these extra incantations do? We'll examine these two hints - NOLOCK
and ROWLOCK
- on the next page!
Comments