none
Table locks RRS feed

  • Question

  • In a stored procedure, dynamic queries are used. The procedure was executed from .NET application but could not get result due to timeout issue. But when same execution query was run from SSMS, result was instantly obtained. In the procedure nolock hint was used for all queries except for dynamic queries. When the nolock hint was used also in the dynamic queries, the execution from the application also gave result instantly.

    The question is, were the tables really locked? If so why the execution from SSMS gave result? If not why the execution from application gave result after using nolock? The nolock hint was missing in dynamic queries and was later added.


    • Edited by Curendra Thursday, August 8, 2019 11:16 AM
    Thursday, August 8, 2019 11:16 AM

Answers

  • As Dan and Erland said, there may be bunch of reasons and NOLOCK may not be the best option to resolve them. If data consistency is important, consider to enable READ COMMITTED SNAPSHOT instead and run the select under READ COMMITTED isolation level. RCSI eliminates reader/writer blocking at cost of higher tempdb usage/version store support.

    As for blocking troubleshooting - you need to understand why blocking occured by analyzing blocked process report. You can install my framework, which significantly simplifies troubleshooting. I also have plenty of info about blocking and SQL Server concurrency model in my blog and in my latest book.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Thursday, August 8, 2019 9:09 PM

All replies

  • The likely reason for these symptoms is there were different execution plans for the stored procedure when executed from SSMS versus the application code. If the plan used by the application was less efficient (e.g. full scan) and touched rows locked by other sessions, the query might block.

    Erland's article Slow in the Application, Fast in SSMS details why you might get different plans in this scenario.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, August 8, 2019 11:53 AM
    Moderator
  • In addition to Dan's post: sometimes people are a little too concerned about error messages. NOLOCK may have resolved the blocking issues, but what if this means that the queries now produces incorrect results every now and then (in a non-repeatable manner)?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 8, 2019 9:00 PM
  • As Dan and Erland said, there may be bunch of reasons and NOLOCK may not be the best option to resolve them. If data consistency is important, consider to enable READ COMMITTED SNAPSHOT instead and run the select under READ COMMITTED isolation level. RCSI eliminates reader/writer blocking at cost of higher tempdb usage/version store support.

    As for blocking troubleshooting - you need to understand why blocking occured by analyzing blocked process report. You can install my framework, which significantly simplifies troubleshooting. I also have plenty of info about blocking and SQL Server concurrency model in my blog and in my latest book.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Thursday, August 8, 2019 9:09 PM