DevForce Community Forum : Implementing [NOLOCK] http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : Implementing [NOLOCK] Thu, 21 May 2026 18:55:01 -700 Thu, 12 Jul 2007 16:08:18 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=221 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Implementing [NOLOCK] : You're right, I looked that... http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=488#488 Author: IdeaBlade
Subject: 221
Posted: 12-Jul-2007 at 4:08pm

You're right, I looked that up quickly in SQL Server Help and didn't notice the SQL Server Mobile part.
 
In any event, your mechanism for influencing DevForce's locking behavior on queries is the one I previously described: setting the IsolationLevel in the TransactionSettings parameter of a QueryStrategy.  However, I happened to discuss some of this with Albert Wang and Jay Traband, both of whom are somewhat familiar with the history of your company's app, and they both expressed doubt that any deviation from the default locking behavior is really necessary.  Apparently there were some performance issues at one point with the Broadlane app that were ultimately addressed by the effective use of data pre-fetch; and other performance problems that were addressed by turning off query inversion in certain queries involving subqueries, where the subqueries caused a great deal of subordinate data to be downloaded. We think the NOLOCK queries may have been tried before the actual problems were completely understood, and that they aren't needed. It seems likely that you can replace them with ordinary EntityQueries or RdbQueries, using the default settings, and suffer no perceptible negative consequences.
]]> Thu, 12 Jul 2007 16:08:18 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=488#488 Implementing [NOLOCK] : Per SQL server documentation,... http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=487#487 Author: Customer
Subject: 221
Posted: 12-Jul-2007 at 4:07pm

Per SQL server documentation, SQL Server operates with READCOMMITTED as the default isolation level, and thus for selects default is LOCK. The statement you have provided applies to SQL Server 2005 Compact edition and is different for SQL Server 2005.

Here is the documentation note from SQL Server books online: (http://msdn2.microsoft.com/en-us/library/ms171885.aspx)

Although the default isolation level in SQL Server Compact Edition is Read Committed, using this isolation level does not result in S locks being taken when data is read. This behavior is unlike Microsoft SQL Server. In SQL Server, when using Read Committed (the default is ‘READCOMMITED’), an S lock is requested whenever a row is read, and this will wait if there is a conflicting lock on that row. SQL Server Compact Edition does not require an S lock because versions of data pages are automatically maintained to ensure that committed data can be read without the need to take a lock. This is important because, in SQL Server Compact Edition, SELECT operations do not need to take any locks on the data and almost always succeed. SELECT operations will not wait if some data has an X lock, as opposed to SQL Server. The SELECT operation still requires a Sch-S lock. The operation will fail only if the table is being modified, because a conflicting Sch-X lock will exist.

So, with the above statement, NOLOCK is not the default behavior/setting for SQL SERVER 2005 and that needs to be set manually. Earlier we used to convert RdbQuery to PassThruRdbQuery and replace the string to set NOLOCK (for e.g., pPassthruRdbQuery.SqlSelect = pPassthruRdbQuery.SqlSelect.Replace("from \"dbo\".\"<TABLE_NAME>\"", "from \"dbo\".\"<TABLE_NAME>\" (NOLOCK)");)

Correct me if my analysis is incorrect or I miss something.

]]> Thu, 12 Jul 2007 16:07:46 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=487#487 Implementing [NOLOCK] : According to the SQL Server documentation: ... http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=486#486 Author: IdeaBlade
Subject: 221
Posted: 12-Jul-2007 at 4:06pm

According to the SQL Server documentation:
 
    "NOLOCK does not issue any locks. This is the default for SELECT operations.  It does not apply to INSERT, UPDATE, or DELETE statements"
 
We do not change this default.
 
You can control the isolation level of queries by setting the IsolationLevel property of the TransactionSettings parameter of a QueryStrategy or SaveOptions object. However, we recommend that you leave these settings at their defaults unless you have identified a specific problem with the default behavior.
 
]]> Thu, 12 Jul 2007 16:06:46 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=486#486 Implementing [NOLOCK] : I am wondering if there is a way... http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=485#485 Author: Customer
Subject: 221
Posted: 12-Jul-2007 at 4:05pm

I am wondering if there is a way to set NOLOCK when querying database table. One way I know is using PassThruRdbQueries but I am using ORM generated entities to query and want to set NOLOCK at entities level instead of getting sql from the entities and manually setting NOLOCK.

Is there a way to accomplish this? I remember something you said in the training class but I couldn’t recall. I appreciate your response on this.

]]> Thu, 12 Jul 2007 16:05:59 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=221&PID=485#485