Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Execute SQL Server SELECT command without locking

MSSQLTips author Jeremy Kadlec By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > 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

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

SQL Server Isolation Levels

ID Isolation Level Description SQL Server Versions
1 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

SQL Server 2000
SQL Server 2005
2 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

SQL Server 2000
SQL Server 2005
3 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

SQL Server 2000
SQL Server 2005
4 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

SQL Server 2000
SQL Server 2005
5 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

SQL Server 2005

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



Last Update: 12/15/2008


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.