Hi frans

Is there any way to include the keyword Nolock in the sqlstatements that the DAL generate? This option is sometimes required...

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 - 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.

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:

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:

SELECT * FROM ##FOO

SELECT * FROM ##FOO WITH (NOLOCK)

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".

I should have added that when you issue

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

There is a specific item in the index in BOL about this command.

Thanks for the great info Thomas and Phil

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

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.

Otis wrote:

Thanks for the great info Thomas and Phil

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

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

Geoff.

Heh

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 .

Otis wrote:

Thanks for the great info Thomas and Phil

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

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.

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)

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

The man is too damn fast sometimes

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

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 )

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.

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

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:

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.

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

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

Thanks guys!

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...)

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.

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.

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?

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?

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.