SQL SERVER – Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction

Just received very interesting question in email:

“How do I apply NOLOCK hint to my whole query. I know that I can use NOLOCK at every table level but I have many tables in my query and I want to apply the same to all the tables.

I want to do something like following script.

SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON
    sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate
OPTION (NOLOCK)

When I ran it it gives me following error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘NOLOCK’.

Please recommend.”

I just never thought of using NOLOCK such a way, when I had to use NOLOCK in such a way, I always use isolation levels.

Here is how I use it:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
*
FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON
sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate
-- Set isolation level to original isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Have you ever faced situation like this when you need to set NOLOCK isolation level at all the transaction level? If yes, how did you do this?

Here is the script to know your database isolation level: SQL SERVER – Check the Isolation Level with DBCC useroptions

Reference: Pinal Dave (http://blog.sqlauthority.com)

17 thoughts on “SQL SERVER – Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction

  1. Lets say the query takes a lot of time to execute say 1 hr..then there would be impact in other places right??..
    So, What say?

    Thanks,
    Prawin

  2. @Pinal Dave you can use ‘nolock’ like this
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail as sod with(nolock)
    INNER JOIN AdventureWorks.Sales.SalesOrderHeader as soh with(nolock)
    ON sod.SalesOrderID = soh.SalesOrderID
    ORDER BY sod.ModifiedDate
    but it has two drawbacks
    1-another tansaction is rolled back while your query still running you will get this exception:
    Could not continue scan with NOLOCK due to data movement.
    2-another tansaction is rolled back after your query had ended
    then you have dirty data

    i recomment to use ‘readpast’ as the database engine not consider any locked rows ordata pages when returning results.

    • If you have a long running query, odds are blocking side effect will be observed with NOLOCK. While bypassing locking might sound tempting, sometimes deadlock might be a better alternative

  3. I have similar kind of requirement but I have to query production databases to run some scripts. Is it safe to work with transaction isolation levels on production databases.

    Thanks,
    Sai Pavan Viswanath

  4. Hi Pinal,
    Most of the situtations where i have used NOLOCK is at the table level on reporting databases (part of Datawarehouse), very rarely use it in OLTP. Interesting viewpoint on using NOLOCK at transaction level.

  5. Yes, I’d be interested to know if there is any impact on a production database when setting NOLOCK isolation level at the transaction level?

    If not, could you clarify the need to change it at the end of the query?

    Thanks.

  6. Pingback: SQL SERVER – Importance of ANSI ISOLATION Levels in SQL Server Database – Quiz – Puzzle – 1 of 31 « SQL Server Journey with SQLAuthority

  7. Pingback: SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Locking, Blocking and Deadlock – Quiz – Puzzle – 9 of 31 « SQL Server Journey with SQL Authority

  9. Thanks! you are a life savor, spent a while trying to figure out how to dynamically add to a generated nhibernate query.

  10. I have a process which pulls the data and send SMS. My concern is, if i start inserting the data in the Table from where i read and send sms and the same time i run the application which send sms, it should not read the inserted data until its committed. But in the mean time it should fetch all the previously commited transaction. How can i achieve the save.

    BEGIN TRAN A
    INSERT INTO test_name VALUES (‘ABC’)

    SELECT * FROM test_name (Should return me data other then ‘ABC’ as its not committed yet.

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s