locked
WithNoLock issue RRS feed

  • Question

  • Hi, we need your assistance in the following:
    when we are deleting record from entity A the plagin logic updates related record from entity B (A:B = N:1 relationship).
    If we start this process simultaneously as a result of  multiple user actions on many DIFFERENT records (from entity A) we face
    deadlock error/ Generic SQL error. Out traces show that the issue is related with these tables, they are locked.
    We use plugin which is  working in sandbox mode, and its steps are running synchronously.
    We need to understand the reasons of  this error:
    - Could this happen because of update/delete/insert transactions, and if so, how could  we fix it?
    As far as we know we can't  use WITH (NO LOCK) statement in these cases.
    We use NO LOCK=true only in select  queries. Please help.
    Also, we need to know, is it possible to lock tables when doing  REST queries (select/update/delete/insert) from javascript code?
    Please provide us detailed info about locking issue as we don't have a good background in these areas:
      is entire table becomes locked, or only records which are processed?  
    We would point to the fact that this happens on different records (both from A, B entities).
    Thank you in a advance.
    Thursday, August 1, 2013 2:16 PM

All replies

  • Hi,

    Are you sure this is a deadlocking issue and not just a timeout?

    When you update entity B, it will be locked during the current delete transaction and so the other updates will be serialised. This will slow down the delete process and could lead to a timeout if you are deleting many records.

    The should be no deadlocking unless you are performing an operation on the update of entity B. The golden rule of avoiding dead locks is to always perform updates in the same order - so update entity A first and then B - never the other way around.

    I suggest you look for other plugins/workflows that might be coming into play here.

    Also:

    1) Enable Read committed isolation - http://technet.microsoft.com/en-us/library/ms345124.aspx if you've not already

    2) If above doesn't fix the issue, Use SQL profiler to find which SQL Statements are causing the deadlocks - and then find the plugins that are causing the locks. Re-write them to ensure that the updates are always done in the same order in all plugins. - http://msdn.microsoft.com/en-us/library/ms188246(v=sql.105).aspx

    hth


    Scott Durow
    Blog www.develop1.net    Follow Me
    Rockstar365
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, August 1, 2013 9:30 PM
    Answerer
  • You can still sometimes get into SQL locking issues because of the way the base and extensionbase tables work under the covers when you do entity deletes/updates. I assume that REST queries would by default put read locks on the data being read, but you can find some recommendations on setting your isolation mode in SQL to minimize some of this. If that doesn't get you close enough, you could also use some kind of 'gating' logic in your plugin to serialize the deletes, but keep in mind will force only one delete to happen at a time, and could cause timeouts too if you have a large backup of items to delete. One way to 'serialize' access is similar to what we can do for autonumber plugins, the same type of approach should work 1) update the 'lock' entity record (without retrieving it first) as the first step in your event pipeline/plugin - this prevents any other plugin from moving past step 1 until your entire operation is complete. 2) perform your other logic (which will be in the same transaction). http://connect.greenbeacon.com/2012/01/technical-post-implementing-robust-microsoft-dynamics-crm-2011-auto-numbering-using-transactions/
    Friday, August 2, 2013 6:23 PM
  • Hi,

    How did you get on with this - any luck tracking down the problem?


    Scott Durow
    Blog www.develop1.net    Follow Me
    Rockstar365
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, August 8, 2013 3:02 PM
    Answerer