Execute SQL Server SELECT command without locking

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

Problem

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?

Solution

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:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

SQL Server Isolation Levels

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

READ UNCOMMITTED

Equal to a NOLOCK hint for an individual SELECT statement which means that shared locks are issued and no exclusive locks are honored.
More information - Remove some SQL Server blocking issues with the NOLOCK hint

READ COMMITTED

This isolation level is the default isolation level in SQL Server where shared locks are issued and exclusive locks are honored.
More information - How To Use Transactions in SQL Server Integration Services SSIS

REPEATABLE READ

Locks are placed on all data used for a query preventing data updates, but new data can be added outside of the scope of the current query.
More information - Optimistic Locking in SQL Server using the ROWVERSION Data Type

SERIALIZABLE

Equal to a HOLDLOCK which is when a shared lock is issued on all tables until the transaction is completed.
More information - SQL Server DBA Concurrency and Locking Interview Questions

SNAPSHOT

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

Keep in mind adding the NOLOCK hint or changing the isolation level does change how either locking is handled for either an individual query or for all commands in the session. These changes should be thoroughly tested to validate they meet your needs.
Adding NOLOCK to all of your SELECT statements is not a good idea if reading dirty data is not acceptable, which is the case in most environments. So automatically adding NOLOCK hints is typically not a good practice in an OLTP environment.
Stay tuned for more tips on hinting in SQL Server and the associated benefit.
Check out these related tips:

About the author

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