June 27, 2006 at 5:58 am
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.
June 27, 2006 at 6:48 am
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
June 27, 2006 at 10:01 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply