We help IT Professionals succeed at work.

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
Comment
Watch Question

ste5anSenior Developer

Commented:
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.

Author

Commented:
It is advice for the optimization of queries
ste5anSenior Developer
Commented:
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.

Author

Commented:
Might you help me understand the read_commited_snapshot
Mark ElySenior Coldfusion Developer
Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
enrique_aeo, do you still need help on this question?

Author

Commented:
an example would be perfect TSQL code
Mark ElySenior Coldfusion Developer

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

Senior Developer
Commented:
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.

Author

Commented:
Please ScottPletcher:
Might complete the example. The code ste5an friend helped me a lot, please your support. Thank You