Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

nolock hint is causing a table lock

Status
Not open for further replies.

kprogrammer

Programmer
Feb 28, 2003
27
We are running SQL Server 2000 on a windows 200 platform. Recently we started having a problem with our nolock hints, even directly out of query maker. The associated select queries are causing table level locks. The table we wroking against has approximatly 7 million rows. Has anyone had this problem and found a solution to it or know what causes it?

Kirk
 
Query maker is my shortcut name for SQL Query Analyzer (sorry for not being specific)

This happens with any querry, even basic ones such as:
Select * from tbltimesheet with (nolock) where cl_code='xyz'

Thanks
Kirk
 
How specifically did you identify that it was table locking that was causing the problem?



"NOTHING is more important in a database than integrity." ESquared
 
Hint: if you see some table-level locks of your table, it can be just harmless schema-stability lock, not data lock.
 
It is a table sch-s lock. We are using enterprise manager to identify the locks. The queries we have been testing are just simple select statments, but of our selects come out of stored procedures. So these locks are not a problem that we should be worrying about? What casues them?

Kirk
 
from BOL
Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.

"NOTHING is more important in a database than integrity." ESquared
 
How do you set the Sch-M lock? I tried using this to no avail:
@LockMode = 'Sch-M'

I get the following message:
Procedure or function 'sp_getapplock' expects parameter '@LockMode', which was not supplied.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top