SQL Server Performance

NOLOCK takes out shared locks

Discussion in 'T-SQL Performance Tuning for Developers' started by brourke, Nov 8, 2002.

  1. brourke New Member

    Everything I've read seems to say that the following statement:

    SELECT ColumnWhatever FROM TableWhatever (NOLOCK)

    should not take out any locks on TableWhatever.

    However. A shared lock is being taken out on TableWhatever.

    Whyever?

    (READUNCOMMITTED does the same as NOLOCK.)
  2. bradmcgehee New Member

    Is is possible that the table lock is coming from another query? NOLOCK works for a single query at a time, and using it will prevent locking on the table it is run against, but not on other queries running at the same time.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. brourke New Member

    Nope. No other queries. Even with absolutely no other activity in the database. Running:

    SELECT ColumnWhatever FROM TableWhatever (NOLOCK)

    from the Query Analyser takes out a shared lock.

    So does:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    BEGIN TRANSACTION

    SELECT ColumnWhatever FROM TableWhatever

    COMMIT TRANSACTION

    Any ideas?
  4. bradmcgehee New Member

    I don't have any other ideas at the moment. Anybody else have any suggestion?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. royv New Member

    Create a dummy database and see if you get the same problem. If you do, then I am clueless. If you do not get the same problem on the dummy database, then as brad suggested, you might have another process running on the table.


    "How do you expect to beat me when I am forever?"
  6. burbakei New Member

    how do you find out there is a SHARED LOCK on the table? I might have some suggestion.

Share This Page