Execute SQL Server SELECT command without locking

By:   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Locking and Blocking


We have two different situations in our SQL Server 2000 and 2005 application where we suspect we have locking caused by SELECT statements. First, we have reports that need to run in our production environment from a third party application. These seem to be the worst offenders since some of the reports are ad-hoc in a sense and others are the same report issued repeatedly. Second, as a portion of our OLTP application we have some fairly complex SELECT statements referencing fairly complex VIEWs. These are isolated to a specific portion of the application and we have a good sense of their usage. In either of these situations can you offer any suggestions to reduce the locking?


Let's take a look at each situation independently to see how we can address the issue. Let's also balance that with the fact that SQL Server has a lock manager for a reason. That is to ensure data integrity in a highly concurrent database environment. So changing SQL Server's default behavior is not be a good idea and should be completely understood before making any changes.

Reporting Locking

With the reporting situation, see if the following options are possible:

  • For both the ad-hoc and repeatable reports, is it possible to execute the reports on another SQL Server i.e. a reporting server? The data can be replicated (transactional replication, log shipping, third party replication, etc.) in a number of different ways, but the data may be slightly out of date. However, it would prevent reporting from competing with OLTP applications.
  • For the repeatable reports, see if the report data can be pre-aggregated every minute or every five minutes. Once again the data will be slightly out of date, but the value can be calculated once then read repeatedly without causing any locking issues on the base tables every time the queries are issued. The locking would only occur the one time a minute or every five minutes when calculation is issued.
  • If these options are not possible then consider the following:
    • If there is no issue reading dirty data in your application and your reporting application supports session level parameters then consider using changing the isolation level for each of the sessions from your reporting application. Most likely they are following the SQL Server (2000 and 2005) default isolation level of READ COMMITTED. The READ COMMITTED isolation level does not permit dirty reads and prevents non-repeatable reads. As such, the appropriate isolation level in your circumstances would be READ UNCOMMITTED because no shared locks are issued and no exclusive locks are honored. As far as where and how to make the isolation level change in your application, reference the documentation from your reporting tool.

Here would be the sample code included at the start of the SQL Server session from your reporting application:


SQL Server Isolation Levels

Since we broached the subject of isolation levels, let's briefly review the options:






  • Any transaction that explicitly sets the transaction isolation level to snapshot will not acquire share locks and will not wait on any locks. Rather, you will get the committed version of all rows at the time the transaction begins.
  • To permit this isolation level, it must be enabled at a database level before a session can use this option.
  • More information - How To Use Transactions in SQL Server Integration Services SSIS

OLTP Locking

In the circumstance of the OLTP application locking with VIEWS, using one of the non default isolation levels probably does not make sense since you do not want to reduce the concurrency or read dirty data for the entire session. In that circumstance you probably just want to add the NOLOCK hint to your SELECT statements for only those sets of code. Keep in mind with the NOLOCK hint no shared locks are issued and no exclusive locks are honored.

For more information about the NOLOCK hint check out - Remove some SQL Server blocking issues with the NOLOCK hint.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms