Quick Fire Queries too Hot to Handle for Database Locking

Today I experienced a somewhat frustrating issue with performing asynchronous queries to MSCRM 2011 through Silverlight.  I had a collection of records, which I wanted to delete. All I had to do was iterate through the list, call BeginDelete and handle the asynchronous callback. This should be easy but an unhelpful exception was being raised intermittently with an unhelpful error message.

If you have never used the BeginDelete method believe me when I say it is extremely simple. There really isn’t much that can go wrong with it. Don’t believe me? Well heres the method!

///
///The schema name of the entity. ///The id of the record that is to be deleted. ///Callback to handle the returned result on. ///An object that is passed in to the call back. ///

BeginDelete(string logicalname, Guid id, AsyncCallback method, object state)

So the only things that can really go wrong with this method is if I pass in the wrong logicalname, id or my call back handles the return incorrectly. I was was pretty sure nothing was wrong with my code but after spending time searching through event logs that didn’t exist and an unhelpful exception error message I found the cause of the problem! It was in a plugin that ran post delete of the entity I was deleting.

This is going to sound funny, the thing is, the plugin was fine! It was doing what the developer had intended, which was to update the parent of the record to be deleted by looking to the children left associated with the parent. It would perform a calculation on the children and update its self with the calculated value. The speed at which I was performing my delete requests was too quick for the plugin though! It was crashing with a lock exception. The problem was that the update could not obtain a SQL lock. It got me thinking about the best ways to handle this situation.

Now I decided that there was two options. One more ideal than the other but it depends on what your situation is with the plugin.

Solution 1

If you do not have access to the plugin causing the issue, or it is too complicated and time consuming to alter the plugin you can change your Silverlight code so that it gives the plugin time to process everything. So instead of iterating through my collection and quickly firing off deletes I can process one item delete at a time. This can be achieved by calling a delete on a collection item and only processing the next item when your callback has handled the response.

Solution 2

This is the most preferred solution.

If we can alter the plugin we should change it so that it only processes its associated children and update its self when a specified field is set. This means the plugin is more efficient, and shouldn’t suffer from query overload if my Silverlight application sets the field correctly. Ideally the field should be set for the last record to be deleted!

These two solutions can be used in any case, it doesn’t just have to be delete queries that can cause exceptions with database locking. The delete query for me was my means to the cause. For someone else it could be completely different. However, if you find yourself iterating through a collection and performing queries fast you may experience the problem and hopefully remember my two solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *