September 30, 2005 at 2:31 am
I've inherrited a database where a large number of views and procedures use NOLOCK.
The history is that the systems using the database experienced a lot of deadlocking without using this hint.
My understanding was that if someone reads a row in the database then this row is locked for the duration of the read and that if someone tries to update that row then the update will pause until the previous lock is released. In practice this should take the blink of an eye.
I also believe that SQL Server locks a page at a time, however many records fit on the page.
Am I understanding this correctly?
September 30, 2005 at 3:24 am
My understanding was that if someone reads a row in the database then this row is locked for the duration of the read and that if someone tries to update that row then the update will pause until the previous lock is released. In practice this should take the blink of an eye.
Pretty sure it is, but it couldn't hurt reading the books on line on the topic "locking"
Mostly the select-locks wander around because a program retrieves them by need basis and not all at a time.
a page is +- 8k, amount of records = 8k/amount of space used by a record.
see the Books Online for "pages, described"
September 30, 2005 at 9:14 am
NoLock - Doesn't crate any locks on anything...
so you might get partial info if some one inserting
or updating data at the time.
From BON(NOLOCK - Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. )
Borik
Hope This Helps
P.S. there are performance benefits with it... but use with care...
October 2, 2005 at 10:57 pm
David,
SQL Server 2000 can use row-level, page-level, or table-level locks. By default, the server determines the granularity of the locks, although you have manual control through locking hints.
And yes, nolock can prevent deadlocks. The risk is reading -- and acting upon -- non-committed or inconsistent data.
October 3, 2005 at 6:46 am
With NO LOCK ,
IF THERE IS ANY LOCK SUCH AS TAB LOCK OR PAGE LOCK YOU CAN READ SUCH OBJECT.
USE
SELECT * FROM <TABLE NAME> (LOCK HINT)
LOCK HINT :
TAB LOCK
NO LOCK
PAGE LOCK
ETC.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply