I recommend doing this as an optimization technique, as long as you are aware of the tradeoff you are making. The default database settings are optimized for ACID transactional safety, but most Web sites don't need this level of safety unless you are handing financial transactions. Most of the NoSQL databases that are gaining popularity do away with this excessive locking for the benefit of substantial speed increases. In SQL Server establishing and checking locks are a significant percentage of the query time for fast queries, and there is always the risk of deadlocks or lock timeouts.
Disabling the locking can have some problems in SQL Server. On very rare occasions you will have one row appear twice in a result set or a row will be skipped, in addition to the obvious issue when you are reading data that is being updated at the same time. These downsides are well documented. The duplicate rows in the result set issue is one most people don't know about. I think Itzik Ben-Gan has written about this problem in detail. Look into the WITH (NOLOCK) query hint. I use WITH (NOLOCK) more frequently than changing the entire transaction to be read uncommitted because you can be more precise with it. For example, not establishing locks on static lookup tables that are involved in a join, but maintaining locks on the transactional tables. If your fast select statements are reading from tables that are never updated, then checking for locks on those tables is wasted overhead. Another related tip is that in the SQL Server Management Studio options, consider changing the default query execution to be read-uncommitted and the deadlock priority to be low. These settings help when you want to jump onto the production server to run a select statement without having much locking impact on the production database. -Mike Chabot http://www.linkedin.com/in/chabot On Fri, Sep 30, 2011 at 5:45 PM, John M Bliss <bliss.j...@gmail.com> wrote: > > We have a fairly high-traffic ACF 8 site where SQL Server database gets hit > with two types of queries: > > type 1 - about 1-5 SELECTS per second that have to run as fast as possible > *always* (< 1 second) > type 2 - about 100-500 "other" queries per day that are allowed to run > "slower" (5-20 seconds). (These are reports, automated processes, etc.) > > I know I can declare "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" on > some of those type 2 queries so that they do not issue locks on tables that > type 1 requires to do its thing. > > My question is: if the goal is to make sure type 1 remains as fast as > possible, will it help/hurt to also declare "SET TRANSACTION ISOLATION LEVEL > READ UNCOMMITTED" on type 1 queries? > > -- > John Bliss - http://about.me/jbliss ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347862 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
