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

Nolock 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).




Show quoteHide quote

Hi,

NoLock hint in SELECT statement will allow you to read the uncommited
transactions (Dirty reads).

Demerits:-


Show quoteHide quote

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


Show quoteHide quote

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


Show quoteHide quote

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

Show quoteHide quote

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.

Thanks

Show quoteHide quote

Lara (lara***@gmail.com) writes:
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.

INSERT table (column1, column2) (@variable, '100')
Simple JOIN turned ugly - Help!
Is the SQL Server Service Running?
Grouping,Paging Logic
space(0) and replace('123-45', '-', '')