NOLOCK takes out shared locks | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

NOLOCK takes out shared locks

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.)
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
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?
I don’t have any other ideas at the moment. Anybody else have any suggestion?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
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?"
how do you find out there is a SHARED LOCK on the table? I might have some suggestion.
]]>