Execute SQL Server SELECT command without locking

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More

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:

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

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:
  • Remove some SQL Server blocking issues with the NOLOCK hint
  • How To Use Transactions in SQL Server Integration Services SSIS
  • Optimistic Locking in SQL Server using the ROWVERSION Data Type
  • SQL Server DBA Concurrency and Locking Interview Questions
  • Using SQL Server Indexes to Bypass Locks
  • Processing Data Queues in SQL Server with READPAST and UPDLOCK

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code