locked
Workflow causes SQL Deadlocks RRS feed

  • Question

  • I have a workflow that is trying to send multiple e-mail notifications out to several users at the same time.  The symptom i was seeing was the it would send 1 or 2 messages, but the others would never get sent.  I did a Trace and found that I was getting a SQL deadlock error.

    Exception when executing non-query: update QueueItemBase set Title='Idea was Updated CRM:000200141', ModifiedOn='07/20/2010 18:52:14', ModifiedBy='4622b690-99ae-de11-8ef0-0015c588f53d' where (ObjectId = 'e479f8e7-2f94-df11-b1ce-0015c588f53d' and ObjectTypeCode = 4202) Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    So clearly it is having trouble seding out multiple e-mails, or updating the email entity.

    Seems like a big problem if you can't have workflows send out emails?

    Ideas on how to fix this?

    Tuesday, July 20, 2010 7:12 PM

Answers

All replies

  • Can you provide a digest of the workflow conditions and rules designed for the workflow in question?
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Tuesday, July 20, 2010 7:16 PM
    Moderator
  • I have a custom entity with a 1:N relationship to a 'join' table linking it to the contact entity.  This allows for a 'subscription' on the main entity where I can send an email notice to the listed 'subscribers' when the main entity is updated.

    The basic workflow is this:

    Workflow 1 runs when specific attributes are changed on the custom entity. It uses a 1:N workflow distributor to spawn a child workflow to send an e-mail.  http://crm40distributewf.codeplex.com/

    Workflow 2 is a child workflow that sends an email to all of the 'subscribers'.  IF there are 4 subscribers, it will send an email to each one notifying them that the base entity was updated.

    The symptom I am seeing is that only 1-2 emails get sent, and they others get stuck in a 'waiting' mode.  Checking the workflow, it says there was a SQL error.  I setup a trace, which told me the SQL error was a deadlock.

    Tuesday, July 20, 2010 7:35 PM
  • Are the emails being issued to the "N" subscribers one at a time?  Or are all subscribers being placed as recipients on a single email?  I ask this because it sounds like you may have a custom workflow activity kicking out the messages to each subscriber individually... and the code for that needs to be examined, if so.
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Tuesday, July 20, 2010 7:42 PM
    Moderator
  • Yes, the each "N" subscriber gets their own email.  The child workflow just sends an email, there is nothing custom about it.

    The parent workflow does have the custom 1:N distribution that kicks off the children.  I got that from here: http://crm40distributewf.codeplex.com/

    It seems to be setting off the children, so that part is working.  The trouble seems to be that CRM and/or SQL can't handle sending more than one email at a time? 

    Tuesday, July 20, 2010 7:59 PM
  • Well, it could be a race condition.  Any way you can space the child workflows apart, chronometrically?  What happens if the Email Router service is turned off while the child processes work?

    Also, are you actually doing anything else with the email record in the workflow after it's been created?  Are you use the "Send Email" action, or are you using "Create" to instantiate an Email record, and then using a "Change State"?


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Tuesday, July 20, 2010 8:41 PM
    Moderator
  • Since it is distributing the workflows, I can't see how to space them apart without creating a custom workflow activity.  I am trying to avoid writing any code it possible... :)

    However, I did just put a 1 minute delay before it sends the e-mail in the child workflow. So far, that seems to have helped, I am testing some more to see if that fixes it. 

    Tuesday, July 20, 2010 8:47 PM
  • No, the one minute delay didn't work after all.  It got a few more through but sending any more than 3-4 emails and they choke with the same SQL error.
    Tuesday, July 20, 2010 9:05 PM
  • Not sure if you have done so, but it might help if you apply the latest rollup update. 
    Daniel Cai | http://danielcai.blogspot.com
    Wednesday, July 21, 2010 12:38 AM
  • It's on rollup 11, just installed a couple of weeks ago.

    Is this something I should maybe open a support case with Microsoft on?

    Wednesday, July 21, 2010 12:55 AM
  • May not be a bad idea.  I can't think of any user error that might cause it.  Though you didn't answer my second set of questions, I can't see how the answers would have pointed to anything but a situation that MS may need to account for with the platform.  In any case, best of luck sorting it out.

    Oh, and there have been a few curiosities with UR11 that will likely be addressed in UR12; unless you absolutely need UR11 for a certain fix (or several), I would try out UR10 instead.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Wednesday, July 21, 2010 3:22 AM
    Moderator
  • One of the fixes in UR10 (and hence would be in UR11) might resolve this. It needs to be enabled via a registry value (see http://support.microsoft.com/kb/981053/ ), which would explain why you've still got the issue
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Thursday, August 5, 2010 3:46 PM
    Moderator
  • You are correct!  I actually saw this in the notes for UR12, which I applied hoping it would fix the problem.  I did have to manually update the registry, but so far it looks like it's working.

     

    Thanks!

    Thursday, August 5, 2010 5:41 PM