Library tutorials & articles

SQL Server Locks

By Michael Balloni, published on 08 Oct 2001
Michael Balloni
Page 3 of 5
  1. Introduction
  2. Lock Contention Described
  3. Lock Contention Solved
  4. NOLOCK and ROWLOCK
  5. Conclusion

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!

Related tags

db, sql, sql server, visual basic

Comments

  1. Jonathan Griffiths
    15 Feb 2007 at 15:59

    I've implemented the [WITH (NOLOCK)] hint and it's been a reasonable success.  The only additional note of caution I would add is that we have one table that is occasionally updated (batch inserts).

    The problem is that the data is inserted and then the index is updated.  If the index is read after the data has been updated but before the index is updated then this can cause the following error:

    Number = -2147217900, Description = Could not continue scan with NOLOCK due to data movement., Source = Microsoft OLE DB Provider for SQL Server, SQLState = 42000, Native Error = 601.

  2. Heiko von Schalscha
    23 Sep 2005 at 14:32

    Great.


    This little hint with those little word (nolock) saved my life - and the life of our application. (http://www.gpsoverip.de)


    Thank you VERY much ...

  3. Richard Armstrong-Finnerty
    29 Sep 2004 at 06:22

    You say (with regard to locking): "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"


    What do you mean? If data is being interrogated, then the returned result set MUST be consistent (ACID).


    Seeing uncommitted code is a no-no, whether Financial, denormalized or whatever.

  4. Alok John Tigga
    27 Oct 2003 at 06:46

    Please contribute your suggestion in relation with various issues related with log file management in SQL server. Such as
    1.    Methods to Shrinking the log file.
    2.    Recover the database form Log file
    3.    Recovery mode etc


  5. Developer Fusion Bot
    01 Jan 1999 at 00:00

    This thread is for discussions of SQL Server Locks.

Leave a comment

Sign in or Join us (it's free).

AddThis

Related articles

Ask a question

Related discussion

Related podcasts

  • Stack Overflow: Podcast #28

    This is the twenty-eighth episode of the StackOverflow podcast, where
Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management — among other things. 

Jeff met the inestimable Joey DeVilla aka Accordion Guy...

Events coming up

  • Jun 16

    Code Generation 2009

    Cambridge, United Kingdom

    A developer event with a practical focus on helping people get to grips with code generation tools and technologies.

Languages

Web Development

Frameworks & Architecture

Other major sections