SQLServer 2005 and NOLOCK

[Hessner]

Hi,

In the "far past" I had some trouble with locking while reading, therefore I added:
"WITH (NOLOCK)" to all my select statements(inside my stored procedures).

I had no problem since that.

Now I started using OpenAccess and are beginning to experience timeouts again, I
suspect it have to do with locking.

I have experienced it after OpenAccess had failed to execute its SQL. Either because some fields were
null and the DB said "no thanks", or similar reasons - the next read operation(not OpenAccess) then hangs up.

Can "WITH (NOLOCK)" be set inside OpenAccess - or do you know about another method?

[Thomas]

Hello Bo,

there is currently no way to specify such a behavior. In general it is also somewhat dangerous as it allows uncommitted data to be read.
Are your queries / SP so complicated that they will block the server completely? Are you missing an index somewhere?

All the best,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Hessner]

The uncommitted part I have no trouble with.

Yes, it is a cms system where, when many users online, will block for each other.

The trouble I had was that it would keep the locks, therefore the NOLOCK on all selects.

[Thomas]

Hi Hessner,

although you cannot specify WITH(NOLOCK) for the statements, it is possible to use the READ_UNCOMMITTED isolation level. You can specify that in the backend configuration with
<isolationLevel>READ_UNCOMMITTED</isolationLevel>
This will use the said mode for all transactions on all connections OpenAccess uses (there is no way out).
Please have a quick check whether this is fulfilling your needs.

All the best,
Thomas
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Hessner]

Hi,

Yes, I have already implemented this solution - was just "curious" to hear if OpenAccess supported this functionality, as I have been reading about it regarding LINQ2SQL.

[Thomas]

Hello Bo,

I didn't know L2S supports a nolock per query specification. Can you enlight me on this (links)?

All the best,
Thomas
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Hessner]

Here you go:

Link

[Thomas]

Hello Hessner,

thanks for the hint. Maybe we can include a way to specify NOLOCK on the query level rather than on the underlying connection level in a later version of OpenAccess.

Kind regards,
Thomas
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Patrice Boissonneault]

+1 for this feature.

[Thomas]

Hi Patrice ,

 we talked about such a feature here and came to the conclusion that it is potentially very dangerous to have.
Imagine, what kind of data inconsistencies on Order and OrderDetails instances (f.e.) can be possible which can then enter the first and second level cache. At the moment we think it is best not to allow for such hard to reproduce/diagose/fix behavior.
Currently we are more in favor of a NOWAIT specification so that blocking locks cannot occur.

All the best,
Thomas
the Telerik team

[Ondřej]

Hi Thomas,

I can not agree with you. Read uncommited affects whole context while NOLOCK (equiv to RU) affects only given query.
Regarding data consistency - it's common chimera that NOLOCK=data integrity loss.
Please see eg. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/

The core sentece is "There are no guarantees that your query will retrieve the most recent data."
Okay, this is  application designer's choice. But there's nothing about data integrity, it's about recentness.
Do we need 0.0001sec old changes when editing order?
If yes, then do not apply NOLOCK, if no, then apply.

Again, designers choice.

Please consider NOLOCK implementation, it can have dramatical effect on performance, especially in multi-user scenarios.
Our ERP and B2B web uses NOLOCKs widely (hundreds of users), there're no data integrity problems in the ERP. If we didin't use NOLOCK we would go into gigantic lockings.

[Thomas]

Hi Ondřej,

I will bring this topic up again in the discussions; thanks for your input on this.

Regards,
Thomas
the Telerik team

[Gene]

+1 for this issue.  This would eliminate a lot of needless Nolock errors and overall increase performance drastically within our multi-user application.

Our only alternative to recover from lock errors when they occur would be to wrap every single ORM read operation in a try/catch block with retry logic.  This would be non-trivial to implement to say the least.

The way our application is structured, the data integrity from multiple users is not a significant issue, but the performance problems from selecting with locks is.

[Allen]

+1 for me too.  And I agree with several of the comments posted here.  it is the developer's decision, not the ORM when to implement NOLOCK.  Further, there are some locations I have worked that it was the DBA's decision and the developer had to comply.  If the ORM tool didn't allow that functionality, it could not be used.

[Ivailo]

Hi,

Thank you both for the provided feedback.

While we will indeed consider this request posted in the forum, I would recommend you to open an item for it in the Ideas & Feedback Portal, where the votes can be counted even more clearly and you will have higher chances to get the support of the community for this feature.

 

Regards,
Ivailo
the Telerik team

 

 

[Robert]

Hello,

 

Sorry for digging out this issue but I'm wondering if this feature is included now?

 

Thanks

Best

Robert