NOLOCK

Hi
can any one tell me about the use of NOLOCK. Whrere can we use it and where
dont ?
pros and cons of the above ?
rgards laraNolock will read all records, regardless of their state, it will bypass any
locks on the table.
For example;
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
CREATE TABLE ##tmp ( ID INT PRIMARY KEY CLUSTERED , Data CHAR(1) )
INSERT INTO ##tmp
SELECT TOP 0 NULL AS ID , NULL AS Data
UNION ALL SELECT 1 , 'A'
UNION ALL SELECT 2 , 'B'
UNION ALL SELECT 3 , 'C'
UNION ALL SELECT 4 , 'D'
BEGIN TRANSACTION
INSERT INTO ##tmp VALUES ( 5 , 'E' )
-- run this on connection 2
SELECT * FROM ##tmp WITH(NOLOCK)
ROLLBACK TRANSACTION
Connection 2 will return 5 records, even though the inserted record didn't
actually get entered.
Without the nolock, the 2nd connection will wait until
connection1-transaction is rolledback or committed and return 4 records
(without the discarded 5th record).
"Lara" <lara169@.gmail.com> wrote in message
news:ulv5R8h1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi
> can any one tell me about the use of NOLOCK. Whrere can we use it and
where
> dont ?
> pros and cons of the above ?
> rgards lara
>|||Hi,
NoLock hint in SELECT statement will allow you to read the uncommited
transactions (Dirty reads).
Demerits:-
"Lara" <lara169@.gmail.com> wrote in message
news:ulv5R8h1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi
> can any one tell me about the use of NOLOCK. Whrere can we use it and
> where dont ?
> pros and cons of the above ?
> rgards lara
>|||Look in the BOL:
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.
pros: can read data without waiting for a lock to be opened up for your
query, Cons: Could reflect non-acutal data.
HTH, Jens Suessmeyer.|||Hi,
NoLock hint in SELECT statement will allow you to read the uncommited
transactions (Dirty reads).
Demerits:-
NOLOCK hint will open yourself up to the risk of reading incorrect data. If
possible this should be probably be avoided.
Merits:-
Do not issue shared locks and do not honor exclusive locks. Thus blocking
can be avoided. This is not actually a merit as far as data consistency
is concerned :)
Thanks
hari
SQL Server MVP
"Lara" <lara169@.gmail.com> wrote in message
news:ulv5R8h1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi
> can any one tell me about the use of NOLOCK. Whrere can we use it and
> where dont ?
> pros and cons of the above ?
> rgards lara
>|||Hi,
NoLock hint in SELECT statement will allow you to read the uncommited
transactions (Dirty reads).
Demerits:-
NOLOCK hint will open yourself up to the risk of reading incorrect data. If
possible this should be probably be avoided.
Merits:-
Do not issue shared locks and do not honor exclusive locks. Thus blocking
can be avoided. This is not actually a merit as far as data consistency
is concerned :)
Thanks
hari
SQL Server MVP
"Lara" <lara169@.gmail.com> wrote in message
news:ulv5R8h1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi
> can any one tell me about the use of NOLOCK. Whrere can we use it and
> where dont ?
> pros and cons of the above ?
> rgards lara
>|||Hi,
NoLock hint in SELECT statement will allow you to read the uncommited
transactions (Dirty reads).
Demerits:-
NOLOCK hint will open yourself up to the risk of reading incorrect data. If
possible this should be probably be avoided.
Merits:-
Do not issue shared locks and do not honor exclusive locks. Thus blocking
can be avoided. This is not actually a merit as far as data consistency
is concerned :)
Thanks
hari
SQL Server MVP
"Lara" <lara169@.gmail.com> wrote in message
news:ulv5R8h1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi
> can any one tell me about the use of NOLOCK. Whrere can we use it and
> where dont ?
> pros and cons of the above ?
> rgards lara
>|||Lara (lara169@.gmail.com) writes:
> can any one tell me about the use of NOLOCK. Whrere can we use it and
> where dont ?
> pros and cons of the above ?
One should be very careful with NOLOCK, and if you don't understand the
exact implications of it, don't use it.
NOLOCK is probably OK if you are reading a table that has INSERT activity,
but where rows from yesterday and before are usually not affected, and
you are only reading historic data. NOLOCK can prevent that an accidental
table blocks the writers.
NOLOCK can also be OK for reading current data, if you are only interested
in trends, and the data will not be used for reconcilliation.
If the clustered index on the table are on columns that may be updated,
be extra careful - I've seen reports where SQL Server have read the same
row twice in this case.
NOLOCK queries can also result in errors that indicate serious corruption.
The errors themselves are false alarm, but they are quite ugly.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks
"Lara" <lara169@.gmail.com> wrote in message
news:ulv5R8h1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi
> can any one tell me about the use of NOLOCK. Whrere can we use it and
> where dont ?
> pros and cons of the above ?
> rgards lara
>