Tuesday, 3 July 2012

SQL SERVER : The Scene Behind NOLOCK


SELECT *FROM Sales.SalesOrderHeader a CROSS JOIN Sales.SalesOrderHeader b

SELECT ResourceName = CASE resource_type
WHEN 'database' THEN DB_NAME(resource_database_id)
WHEN 'object' THEN OBJECT_NAME(resource_associated_entity_id, resource_database_id)
request_status, FROM sys.dm_tran_locksWHERE request_session_id = 53



UPDATE Sales.SalesOrderHeader SET status=5 WHERE SalesOrderID=43659


Let us see the same with NOLOCK hint

FROM Sales.SalesOrderHeader a WITH (NOLOCK)
JOIN Sales.SalesOrderHeader b WITH (NOLOCK)  





  1. Hi Nelson,
    Thanks for the article. I have been using the nolock hint for sometime. I wanted to use READ UNCOMMITTED isolation level and your article confirms this to be the same. Using it on an sp, do we need to reset the isolation level at the end of the sp?
    Also is there a function that we can use to check if the query returned a dirty read?



    1. Thank your for reading the article. It is a best practice to reset the isolation level at the end of procedure to avoid any surprises.I believe there is no function to check the dirty read,but the isolation level of the connection/session can be found in the DMV sys.dm_exec_sessions (transaction_isolation_level)


  2. I like your simple, clear and concise style! I passed this on the our Developer group, for those who may be interested in this aspect of how SQL Server works.

  3. Hi Nelson,
    Nice set of examples. It would be nice if you went one step further. Modify the the Select With (NoLock) by explictly begining a transaction.
    The run the select first, followed by the Update.
    Many people think the NoLock hint means the Select will not acquire locks and that is what reduces blocking. If you try my suggestion, I think you will see the Update query will be blocked by the Select.

    1. Thank you for reading the article. I have tried that while writing this article. What i did is, in one window I ran below query

      SELECT *
      FROM Sales.SalesOrderHeader a WITH (NOLOCK)
      CROSS JOIN Sales.SalesOrderHeader b WITH (NOLOCK)

      Verified the locks , it acquired only a shared schema lock on the table.

      In another window I did an update on same table as below
      update Sales.SalesOrderHeader set DueDate='2008-04-12 00:00:00.100' where SalesOrderID= 67258

      it got executed successfully.

    2. Ray Herring is definitely incorrect, as Nelson stated the update will be executed without any blocking.

  4. Nice, clear and to the point. READ UNCOMMITTED Isolation Level & NOLOCK hint though used for same purpose, NOLOCK do not need explicit TRANSACTIONS, where as to SET ISOLATION LEVELS you need to explicitly specify BEGIN TRAN.... END.

    1. Thank you for reading the article . No, To set the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED , you do not have to explicitly start the transaction.
      In one session you can try
      insert into NoLockSample values(1)

      In another session you can try
      select * from NoLockSample

      and you will get the expected result.




    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل

  7. شركة نقل عفش واثاث بالدمام ابيات الشرقيه لخدمات نقل العفش والاثاث بالدمام
    شركة نقل عفش بالدمام
    نقل عفش بالخبر
    شركة نقل اثاث الدمام
    نقل عفش الدمام
    نقل عفش بالدمام


  9. Good article and knowledge for me! I found a lot of information here!