SQL Server Locks

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!

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”