SQLServer 2005 and NOLOCK

16 posts, 0 answers
  1. Hessner
    Hessner avatar
    189 posts
    Member since:
    Mar 2003

    Posted 10 Apr 2009 Link to this post

    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?
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 14 Apr 2009 Link to this post

    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.
  3. DevCraft banner
  4. Hessner
    Hessner avatar
    189 posts
    Member since:
    Mar 2003

    Posted 14 Apr 2009 Link to this post

    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.
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Apr 2009 Link to this post

    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.
  6. Hessner
    Hessner avatar
    189 posts
    Member since:
    Mar 2003

    Posted 17 Apr 2009 Link to this post

    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.
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 17 Apr 2009 Link to this post

    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.
  8. Hessner
    Hessner avatar
    189 posts
    Member since:
    Mar 2003

    Posted 17 Apr 2009 Link to this post

    Here you go:

    Link
  9. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 20 Apr 2009 Link to this post

    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.
  10. Patrice Boissonneault
    Patrice Boissonneault avatar
    28 posts
    Member since:
    Nov 2009

    Posted 08 Oct 2011 Link to this post

    +1 for this feature.
  11. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 14 Oct 2011 Link to this post

    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

    Check out the latest stable build of Telerik OpenAccess ORM. Download it and benefit from our new Project Templates.

  12. Ondřej
    Ondřej avatar
    1 posts
    Member since:
    Oct 2012

    Posted 30 Oct 2012 Link to this post

    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.
  13. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 30 Oct 2012 Link to this post

    Hi Ondřej,

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

    Regards,
    Thomas
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  14. Gene
    Gene avatar
    3 posts
    Member since:
    Nov 2011

    Posted 13 Dec 2012 Link to this post

    +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.
  15. Allen
    Allen avatar
    21 posts
    Member since:
    Aug 2007

    Posted 13 Feb 2013 Link to this post

    +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.
  16. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 18 Feb 2013 Link to this post

    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
     
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
     
  17. Robert
    Robert avatar
    2 posts
    Member since:
    Oct 2011

    Posted 17 Feb Link to this post

    Hello,

     

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

     

    Thanks

    Best

    Robert

Back to Top
DevCraft banner