10774 - Instead of using the hint no_lock

Hi experts,

can you give me an example in TSQL for this:
Instead of using the hint no_lock would be better to use isolation levels
read_commited_snapshot
enrique_aeoAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
And here' s why you shouldn't use NOLOCK:

1. Setup

USE tempdb ;
GO

-- Setup

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'TestNoLock')
                    AND type IN ( N'U' ) ) 
    DROP TABLE TestNoLock ;
GO

CREATE TABLE TestNoLock
    (
      ID INT PRIMARY KEY ,
      Payload VARCHAR(255)
    ) ;
GO

WITH    E1 ( N )
          AS ( SELECT   N
               FROM     ( VALUES ( 1) , ( 1) , ( 1) , ( 1) , ( 1) , ( 1) , ( 1) , ( 1) , ( 1) , ( 1) ) T ( N )
             ),
        E2 ( N )
          AS ( SELECT   1
               FROM     E1 a ,
                        E1 b
             ),
        Numbers ( N )
          AS ( SELECT TOP ( 100 )
                        ROW_NUMBER() OVER ( ORDER BY ( SELECT   NULL
                                                     ) )
               FROM     E2
             )
    INSERT  INTO TestNoLock
            SELECT  N ,
                    N
            FROM    Numbers
            WHERE   N > 90 ;
GO

SELECT  *
FROM    TestNoLock ;
GO

Open in new window


2. Query 1 in first SSMS window

USE tempdb ;
GO

-- Query 1

BEGIN TRANSACTION NOLOCK_ROLLBACK ;

INSERT  INTO TestNoLock
VALUES  ( 1, '1' ) ;

SELECT  *
FROM    TestNoLock ;

WAITFOR DELAY '00:00:05' ;

ROLLBACK TRANSACTION NOLOCK_ROLLBACK ;
GO

SELECT  *
FROM    TestNoLock ;
GO

Open in new window


3. Query 2 in second SSMS window

USE tempdb ;
GO

-- Query 2

SELECT  COUNT(*)
FROM    TestNoLock WITH ( NOLOCK ) ;
GO

SELECT  COUNT(*)
FROM    TestNoLock ;
GO

Open in new window


This is a simple case. There are other scenarios where entire pages maybe locked and skipped. So that you will miss more than just one sinngle row.
0
 
ste5anSenior DeveloperCommented:
Without more information about your context, there is no "better" method. Only a caveat: NOLOCK can result in wrong results due to phantom reads or skips on data.
0
 
enrique_aeoAuthor Commented:
It is advice for the optimization of queries
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
The general advice is: Don't use NOLOCK.

It may return really weird results. NOLOCK will make your execute and skip locked pages. When, while reading a table e.g. an bulk insert is in progess, which may touch many pages, it's possible that all those pages are not read, thus you may miss lots of rows.

NOLOCK is not really an performance hint.
0
 
enrique_aeoAuthor Commented:
Might you help me understand the read_commited_snapshot
0
 
Mark ElySenior Coldfusion DeveloperCommented:
As of SQL 2008 R2 there are 5 different isolation levels:

1)  Read uncommitted (same things as No lock) also known as dirty reads
2)  Read committed
3) Repeatable Read
4) Snapshot
5) Serializable

Imagine an automated routine running the following query.  This automated query uses a transaction in the event  you want to roll back in case something goes wrong.

BEGIN TRAN
UPDATE StockPrices
SET ClosingPrice = @StockClosingPrice
WHERE StockTicker = 'AAPL'
ROLLBACK or COMMIT

Now imagine that you run a selection query  requesting information specific to that record at the same time.  

SELECT *
FROM StockPrices
WHERE StockTicker = 'AAPL'

You will notice that your selection query will not complete because the update has not yet been committed whereas read committed provides you only committed data.  If you run the "sp_who2" system query you will be able to see what is blocked.  You will either have to cancel your query or commit/rollback.

Using Nolock allows you to read uncommitted data whereas read committed reads only committed data.
0
 
Scott PletcherSenior DBACommented:
Here's an example.

This command has to be run only one time, to set rcs on for the db:

ALTER DATABASE db_name
SET SET READ_COMMITTED_SNAPSHOT ON;

Then, as long as you're in READ COMMITTED level -- which is the default level -- you don't need to use nolock, since SELECTs will never be blocked by delete/insert/update anyway.

SELECT ...
FROM db_name.dbo.table_name
WHERE ...

This is a very nice feature, but it's not free.  There is a significant amount of overhead to R_C_S on, including 14 bytes per row and increased use of tempdb.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
enrique_aeo, do you still need help on this question?
0
 
enrique_aeoAuthor Commented:
an example would be perfect TSQL code
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Example 1 - Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

UPDATE StockPrices
SET ClosingPrice = @StockClosingPrice
WHERE StockTicker = 'AAPL'

COMMIT TRANSACTION

Open in new window


Example 2 - Nolock

SELECT *
FROM dbo.Stocks
WITH  (NOLOCK)

WHERE stockticker = 'AAPL'

Open in new window

0
 
enrique_aeoAuthor Commented:
Please ScottPletcher:
Might complete the example. The code ste5an friend helped me a lot, please your support. Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.