Comments (9)

  1. On the back of this , using the Lock:Acquired event in profiler i could see a Sch-S lock there too.

    I used the simpler,
    ” Select *
    from Sales.SalesOrderDetail a with(nolock)”

    using your cross join more lock acquires were reported. Just saying :)

    1. Good info Dave!! The only reason for using the Cross Join is to make the transaction last longer.

  2. IMHO, I would always avoid using system tables and the master database for showing a locking hint. Master database queries most of its information from the mssqlsystemresource database starting from SQL Server 2005. Since the resource database is a hidden database, it shouldn’t be used for any kind of repro to display any locking behavior as you do not know which tables you are touching in the background. My normal repro for a locking scenario is to start an explicit transaction and then execute the query,

    1. Agree 100%. Running his test against the system catalogs in master were his first mistake.

  3. Thank you Robert,

    I have learned something new. You are correct.

    Kind Regards,

    1. Thanks!! I’m always happy to hear when someone learns something from my posts!!

  4. I tested this in SQL 2005 as well and see the same results as SQL Server 2008. Thanks to Robert for the repro scripts.

    Also Thanks you Robert for Providing the Simple DMV to check locks.


  5. […] The Truth About NOLOCK Hints – Another great post from Robert L. Davis(Blog|Twitter) this time looking at the locking behaviour of the NOLOCK hint. I think you’ll be surprised to see what actually goes on. […]

Leave a Reply

Your email address will not be published. Required fields are marked *