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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
Please ScottPletcher:
Might complete the example. The code ste5an friend helped me a lot, please your support. Thank You
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.