Controlling table locking

  • Hi All

    Does anybody know whether there is a way to prevent table locking on certain tables in MSSQL 2000 - and if so then how?

    sp_indexoption only seems to apply to row and page locks.

    Dave.

  • You can control locking to a certain extent using locking hints. This might not do what you expect and you need to read up on the various hints so that you know what they can and can't do. You can find this in BOL by searching on locking and then find hints.

    The most common type of locking we use in production is the NOLOCK hint as we don't want updates to block our select statements.

    An example would be:

    SELECT FirstName, LastName, Phone

    FROM TablePeople WITH (NOLOCK)

    The downside of using this hint is that if someone is updating the record you are trying to view then you might see the old phone number if your select runs after the update statement changes the phone number and before the update commits. Also known as a dirty read.

    I hope this helps.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • As Robert said, look in BOL for information on how to implement locking hints. 

    It is important to point out that using a locking hint is like giving SQL Server your suggestion or preference for a lock type, but SQL Server does not have to take that suggestion.  If you are having a problem where you are seeing table locks and you start using the ROWLOCK hint, SQL Server will still escalate the locking level to a table lock if it sees that as the most optimal lock.  Locks take resources and if SQL Server sees that it more efficient to lock the table than to place 100,000 row locks on the individual rows, it will still lock the table. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply