Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> NoLock option in SQL
 

Pages: 1
LLBLGen Pro Runtime Framework
NoLock option in SQL
Page:1/1 

  Print all messages in this thread  
Poster Message
wayne
User



Location:
Cape Town, South Africa
Joined on:
07-Apr-2004 08:39:37
Posted:
611 posts
# Posted on: 08-Mar-2005 12:23:37.  
Hi frans

Is there any way to include the keyword Nolock in the sqlstatements that the DAL generate?
This option is sometimes required...
Visit http://www.NetReady.co.za - The South African leader in MLM, Network Marketing and Direct selling software.
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37237 posts
# Posted on: 08-Mar-2005 13:05:42.  
No you can't specify this. It is hardly necessary though, you can also specify a readcommitted isolation level for transactions. NOLOCK is only required when you get deadlocks, which is a sign the system's logic should be rescheduled so you do fetches before saves.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
thomas
User



Location:
Los Angeles
Joined on:
21-Oct-2004 22:48:11
Posted:
24 posts
# Posted on: 17-Jul-2005 22:42:45.  
Frans - I've worked on 2 systems that had considerable amounts of data. The current one has a couple of table with 130M rows. Very large. In both systems, I suppose partially because there were 10+ people coding to them, we ended up using NOLOCK on all SELECT procs. It helped a great deal. One of the systems used compound PK's the other used Identity PK's and both had clustered indexes on them (the default behavior of SQL Server).
In your prev post you suggested that deadlocks are purely the result of the timing of a write versus a read operation. In both apps I've worked on we simply developed to what we understood to be best practices. Which meant we did not touch the SQL Server engine in terms of the decision and resource usage whilst it ran through a normal day of operations. So I'm not quite sure what to make of your suggestion there. If you have a webfarm with 3 instances of an app hitting the same db you'll get all sorts of requests for selects and inserts. Its the job of the db engine to execute them in order and without contention. So I'm not sure what you mean by your suggestion about the logic being off.
  Top
PhilD
User



Location:
UK
Joined on:
23-Jun-2005 14:07:55
Posted:
19 posts
# Posted on: 17-Jul-2005 23:09:54.  
MS SQL Server specific comments follow...

NOLOCK (or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED - whew!) can certainly be useful in circumstances where you do not have deadlocks. Thomas is describing one, for example if you have a transaction that INSERTs into a table as part of a long running transaction then the default isolation level will block SELECTs upon that table.

Try this example. Run this in one window in Query Analyzer:
Code:

CREATE TABLE ##FOO (FOOID CHAR(10) NOT NULL PRIMARY KEY)

INSERT INTO ##FOO(FOOID) VALUES ('CANSEEME')

BEGIN TRAN
    INSERT INTO ##FOO(FOOID) VALUES ('NOTSEEME')
    WAITFOR DELAY '00:00:30'
COMMIT

DROP TABLE ##FOO


Then before your 30 seconds are up, try the following variations in another window:

Code:

SELECT * FROM ##FOO


Code:

SELECT * FROM ##FOO WITH (NOLOCK)


Code:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM ##FOO



I think we all know the definition of a deadlock, I think Frans is suggesting that BEGAN TRAN/COMMIT blocks are written such that access to potentially common tables is always done in the same order (A, B, C, D, E...). This is good advice and would eliminate most deadlocks, but it is not possible to do this analysis in non-trivial applications. Also, complex SQL queries may join 10 or more tables and you can't tell in which order the query engine will choose to access them.

It gets worse.

A single SELECT query can block itself in SQL Server! If the query engine decides to use intra-query parallelism it is possible for the separate threads that it spawns to deadlock each other. I wish I was kidding but I am not! See http://support.microsoft.com/?kbid=837983 They claim to have fixed it eg in http://support.microsoft.com/kb/315662 but my experience says "not in all circumstances".


--
Phil
 
Top
PhilD
User



Location:
UK
Joined on:
23-Jun-2005 14:07:55
Posted:
19 posts
# Posted on: 17-Jul-2005 23:23:22.  
I should have added that when you issue

Code:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


within a Query Analyzer session the option stays that way until you put it back to normal:

Code:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED


There is a specific item in the index in BOL about this command.
--
Phil
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37237 posts
# Posted on: 18-Jul-2005 11:39:09.  
Thanks for the great info Thomas and Phil Regular Smiley

There is an undocumented feature in the SqlServerDQE (undocumented because I don't want people to abuse it, but it's there for emergencies).
Code:

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;


This will set the NOLOCK hint on selects, this is a global setting. You need 1.0.2004.2 for this.

Warning: only use this in case of emergencies. In either way, try to make sure your logic starts a transaction as LATE as possible and commits/rollsback as EARLY as possible, and move your selects out of the transaction.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
davisg
User



Location:
Bradford, UK
Joined on:
27-Feb-2005 16:18:43
Posted:
113 posts
# Posted on: 18-Jul-2005 12:02:30.  
Otis wrote:
Thanks for the great info Thomas and Phil Regular Smiley

There is an undocumented feature in the SqlServerDQE (undocumented because I don't want people to abuse it, but it's there for emergencies).
Code:

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;


This will set the NOLOCK hint on selects, this is a global setting. You need 1.0.2004.2 for this.

Warning: only use this in case of emergencies. In either way, try to make sure your logic starts a transaction as LATE as possible and commits/rollsback as EARLY as possible, and move your selects out of the transaction.


Frans,

The flexibility in your product together with your knack to read peoples minds ahead of the game is absolutely amazing.... now i'm bloody impressed Laugh

Geoff.
Before software can be reusable it first has to be usable!  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37237 posts
# Posted on: 18-Jul-2005 12:41:38.  
Heh Laugh

Credits go to a customer who requested this though, they needed to alter the DQE for adding NOLOCK everywhere which was a hassle for them so I added the feature to the DQE to make life easier for them Regular Smiley. Tongue


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Rogelio
User



Location:
Panama, Panama
Joined on:
29-Mar-2005 02:57:36
Posted:
221 posts
# Posted on: 18-Jul-2005 13:32:54.  
Otis wrote:
Thanks for the great info Thomas and Phil Regular Smiley

There is an undocumented feature in the SqlServerDQE (undocumented because I don't want people to abuse it, but it's there for emergencies).
Code:

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;


This will set the NOLOCK hint on selects, this is a global setting. You need 1.0.2004.2 for this.

Warning: only use this in case of emergencies. In either way, try to make sure your logic starts a transaction as LATE as possible and commits/rollsback as EARLY as possible, and move your selects out of the transaction.



Frans,

There is any way to set it at conections level? I do not like the idea to set it globaly, because it could affect other users doing transactions. This would be a nice feature to use when doing Select for report data.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37237 posts
# Posted on: 18-Jul-2005 13:38:46.  
Not at the moment as the DQE is a class with static methods. For 1.0.2005.1 this is refactored to instance classes, which could allow you to set it on a per-call basis (adapter). I'll see if I can refactor it into the instance (without breaking current code)

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
jtgooding
User



Location:
Orlando, USA
Joined on:
26-Apr-2004 14:42:01
Posted:
126 posts
# Posted on: 18-Jul-2005 14:31:06.  
Quote:
Credits go to a customer who requested this though, they needed to alter the DQE for adding NOLOCK everywhere which was a hassle for them so I added the feature to the DQE to make life easier for them .


The only complaint I have as the customer the change was made for is, I recoded the DQE to use (NOLOCK) and then Frans goes and adds it to the base code less than a week later invalidating my code Tongue

The man is too damn fast sometimes Wink

We use NOLOCK on EVERY select query, with about 8 Terabytes of data and 2000 users its a necessity, especially with most of them being remote users which creates additional issues.

John
John Gooding  Top
thomas
User



Location:
Los Angeles
Joined on:
21-Oct-2004 22:48:11
Posted:
24 posts
# Posted on: 18-Jul-2005 17:30:21.  
Now we're talking!
I'm so glad to hear you do have a nolock option albeit undocumented. Wew.
I'm with John on this one. My system has 7M registered users. Some of our tables are ridiculously large so without this option we are pretty much hosed (to use a California term Regular Smiley)

I'm glad someone mentioned scenarios where procs have lots of joins. That was one of my experiences as well. We had procs that regularly joined 5+ tables and some as many as 10.

Back to the nature of a nolock - it allows dirty reads. In many apps thats not a good thing. However, in my experience web applications are more likely to need the option and frequently lend themselves better to it because they tend to have less inserts/updates and more selects. Ok, I'm generalizing really badly here I know. There are probably people with different experiences that this, but you know what I mean.


  Top
Marcus
User



Location:
Dublin, Ireland
Joined on:
23-Apr-2004 11:49:41
Posted:
747 posts
# Posted on: 18-Jul-2005 18:14:44.  
Can someone explain the benefit of NOLOCK over Isolation Level READ UNCOMMITTED?

You guys are talking about its usefullness in very large tables where lots of joins are made.... but read uncommitted doesn't lock anything... AFAIK It only gets stuck when a user is modifying table design and at that point a read uncommitted blocks...

Is there an additional saving in SQL Server resource overhead of applying the lowest level isolation?

Marcus
Blog: http://www.styledesign.biz/weblogs/macinnesm/
Website http://pix.ie
 
Top
PhilD
User



Location:
UK
Joined on:
23-Jun-2005 14:07:55
Posted:
19 posts
# Posted on: 18-Jul-2005 18:31:11.  
Marcus wrote:
Can someone explain the benefit of NOLOCK over Isolation Level READ UNCOMMITTED?

You guys are talking about its usefullness in very large tables where lots of joins are made.... but read uncommitted doesn't lock anything... AFAIK It only gets stuck when a user is modifying table design and at that point a read uncommitted blocks...

Is there an additional saving in SQL Server resource overhead of applying the lowest level isolation?

Marcus

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED will apply to all statements issued on that connection until it is cancelled. Therefore it will apply to every table used in the SELECT. If you use it at the top of a stored proc it will apply to every statement in the proc.

On the other hand, you have to specify NOLOCK against all the tables you join to (if you join 7 tables you have to repeat WITH (NOLOCK) after each JOIN). This gives you finer control, but is a bit of a pain and it clutters the SQL.

They behave identically in terms of transactioning/isolation.

Marcus wrote:

Is there an additional saving in SQL Server resource overhead of applying the lowest level isolation?

Probably. Since no locks need to be maintained by SQL server it can presumably just "firehose" the data to you, but I haven't actually checked that.

From BOL:

Quote:
READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.




--
Phil
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37237 posts
# Posted on: 18-Jul-2005 18:41:30.  
Still, starting a transaction with isolationlevel ReadUncommited, then running the query gave different results than with the NOLOCK, as in: more blocked threads. At least that's what I remember from the conversation with John some time ago, perhaps he can elaborate a bit on this, as I don't have access to a terabyte datastore Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
jtgooding
User



Location:
Orlando, USA
Joined on:
26-Apr-2004 14:42:01
Posted:
126 posts
# Posted on: 18-Jul-2005 20:10:54.  
The primary purpose for us using NOLOCK over ReadUncommited is that you can apply NOLOCK to subquery/predicate statements during a transactional write, providing a 'best of both worlds' for us.

so basically,

Select *
from dbo.MyTable M (NOLOCK)
inner join dbo.MyTable2 (NOLOCK) M2 on M.ID = M2.MID
Where M.ID > 1000

and

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Select *
from dbo.MyTable M
inner join dbo.MyTable2 M2 on M.ID = M2.MID
Where M.ID > 1000

Are equivilant, but if you are doing an update

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- The above line may not be desireable so we use nolock instead
BEGIN TRANS
update dbo.MyTable M
set dbo.MyTable.Field1 = 'John'
where dbo.MyTable.Field2 in
(Select dbo.MyTable2.MyField3 from dbo.MyTable2 (NOLOCK) where MyTable2.MyField4 = 'gooding')
COMMIT TRANS

In the LLBLGen world, it also requires the developer to set the isolation level before each call since it may or may not of changed.

With the nolock modification, every select/join/etc generated by LLBLGen has (NOLOCK) included and eliminates the 'woops I missed a query' problem.

We still use a custom DQE even with Frans adding in the change for us, but since he supports it, it makes us a lot closer to the 'standard'.

In addition to having nolock on all our queries, we require every query to be prefixed with a comment, in most cases this is the 'real' user id of the user running a process, so our queries look like this:

/* jgooding */ Select * from dbo.MyTable (NOLOCK)

This is very useful if you are not using Windows Authentication and want to find a 'blocking' user in the system through the input buffers.

John


John Gooding  Top
Marcus
User



Location:
Dublin, Ireland
Joined on:
23-Apr-2004 11:49:41
Posted:
747 posts
# Posted on: 18-Jul-2005 21:54:57.  
Thanks guys! Laugh
Blog: http://www.styledesign.biz/weblogs/macinnesm/
Website http://pix.ie
 
Top
PhilD
User



Location:
UK
Joined on:
23-Jun-2005 14:07:55
Posted:
19 posts
# Posted on: 18-Jul-2005 23:09:14.  
jtgooding wrote:


Are equivilant, but if you are doing an update

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- The above line may not be desireable so we use nolock instead
BEGIN TRANS
update dbo.MyTable M
set dbo.MyTable.Field1 = 'John'
where dbo.MyTable.Field2 in
(Select dbo.MyTable2.MyField3 from dbo.MyTable2 (NOLOCK) where MyTable2.MyField4 = 'gooding')
COMMIT TRANS


John, not sure I understand the difference you would expect to see here, could you explain further? Your subquery is just going to return all the MyField3's that are extant in the table at the time it runs, whether you use SET or NOLOCK. The SET has no effect on the UPDATE AFAIK.

TIA!

jtgooding wrote:

/* jgooding */ Select * from dbo.MyTable (NOLOCK)

This is very useful if you are not using Windows Authentication and want to find a 'blocking' user in the system through the input buffers.

Now that's cunning (Phil tucks technique away into long term wet storage...)


--
Phil
 
Top
PhilD
User



Location:
UK
Joined on:
23-Jun-2005 14:07:55
Posted:
19 posts
# Posted on: 19-Jul-2005 00:03:58.  
While we are on this subject, another general tip that can improve concurrency is to use the WITH (ROWLOCK) hint on UPDATEs, DELETEs and INSERTs.

Code:

UPDATE MyTable WITH (ROWLOCK) SET
X = Y
WHERE
....


This provides a strong hint to SQL server to use row-level locks only, ie not to escalate to page, extent or table locks (which have a nasty habit of causing deadlocks). The hint seems to be honoured most of the time.
--
Phil
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37237 posts
# Posted on: 19-Jul-2005 09:54:12.  
When will Sqlserver escalate to a page lock during an insert/update? Isn't it always rowlocking when you're executing it inside a transaction?

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
thomas
User



Location:
Los Angeles
Joined on:
21-Oct-2004 22:48:11
Posted:
24 posts
# Posted on: 19-Jul-2005 20:22:13.  
I suppose one way it might escalate is maybe as the result of some funcky indexing where the engine can't remain as granular as a row. But my understanding is that it will always always try to go from smallest number of rows to larger. And only escalate if it really has no choice. Ordinarily I would think that a typical update with a PK as part of the where clause should work fine. I suppose there could be cases in which the where clause contains non indexed columns..... but that would be a performance problem long before it becomes a locking issue. Right?
  Top
garisenda
User



Location:
USA
Joined on:
18-Aug-2008 15:27:46
Posted:
5 posts
# Posted on: 18-Aug-2008 16:48:05.  
Otis wrote:
Not at the moment as the DQE is a class with static methods. For 1.0.2005.1 this is refactored to instance classes, which could allow you to set it on a per-call basis (adapter). I'll see if I can refactor it into the instance (without breaking current code)


Has this been addressed in a newer version? I am having issues with the global nature of the NOLOCK hint and would like to be able to set the hints at the time of the call.


So Antaeus looked to me while I watched
Him bending over, and at such a moment
I wished that I had gone some other way
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.