·
4 min read

Optimal Use of Transactions within Microsoft Dynamics CRM Plug-ins

By default, Microsoft Dynamics CRM 2011 plug-ins that are triggered by the same event run within the same SQL Server database transaction, as do any related calls to the SDK. For example, both the creation of an account and a subsequent call to the SDK to generate a related task to welcome the customer would be included in a single transaction. This functionality is intended to ensure that in the event of an error or failure, an entire activity is rolled back together, which is an incredibly valuable benefit in most scenarios.

While this capability offers strong protection against failure, however, the longer a transaction is help open the greater the chances that it can impact other concurrent activity in the system, thereby causing delays or errors. As a result, be sure to keep this in mind during plug-in design to help minimize any application blocking implications associated with initiating actions that result in database locks.

Some common causes of locking are described below.

Performing queries. When a user queries the database, SQL Server uses transaction locking strategies to ensure that the transaction is completed consistently and reliably. When data is queried, SQL Server determines whether or not to lock the data for read, thereby preventing other transactions from writing to that data. SQL Server will determine the most appropriate range of data to lock, and for queries that access large volumes of data in a table or that search for data that is spread across a range of table storage, then the entire table may be locked. In this scenario, the lock would not be released until the enclosing transaction is committed or aborted, which can in turn impact other transactions that also may need to lock data in the table. For example, if a user accesses Accounts with an owner of the current user, this could cause the application to query data across the Account tables.

Most queries in Microsoft Dynamics CRM use database hints if possible to indicate that locks are not needed before querying. The use of database hints is particularly helpful in the case with views, where we know that no direct resulting action will be taken based on the result set returned at that point within the system so the view results are designed to show a point in time data set. As a result of the database hints indicating to SQL Server not to take out a read lock during these common retrieve requests, queries of Microsoft Dynamics CRM occurring via the web service are unlikely to trigger this type of lock under normal conditions.

Note that in some scenarios, such as situations in which a user’s availability is checked prior to booking a service activity, the system does require transactional locking, locking can have an impact on related requests.

Creating records in tables that may be locked by concurrently running queries. While the record creation itself is not problematic, other simultaneously running processes that do require a lock on the table to perform a large query would be blocked until completion of the transaction that contains the record creation.

Updating common resources. Scenarios in which multiple processes are likely to update a common resource, such as a situation in which every request by a member of a team to view a record updates an attribute on the record indicating the last time viewed, potentially can create blocking within the system. If a large number of users regularly view a record, blocking would occur because only one user at a time can update the record.

Although the nature of locking is that where long running locks occur, other transactions may be blocked waiting for these to complete, what is not always obvious is
that a SQL transaction is held across the plug-ins registered on a particular event pipeline such that for example long running actions unrelated to the database extend the length the transaction and any related locks are held. The point to note is that the action itself may not require the lock for a long period, but as the lock is held until the transaction is committed understanding the lifecycle of a transaction that is used across an event pipeline is important. This is important as it may be that SQL blocking may be caused by long running actions occurring outside of the database action as they prevent the event pipeline completing and the related SQL transaction from committing releasing any related locks.

While this locking in itself is not a problem, and in fact is deliberate behaviour for situations in which consistency within a transaction is required, it can force serialization of transactions that need access to the same data. The overall execution time of each blocked plug-in lengthens as it wait for the needed resource to come free before it can begin performing its own task. For scenarios in which this occurs regularly, the overall scalability of a solution can be impacted as the system is less capable of processing multiple concurrent requests.

To minimize or the potential impact of SQL database transactions on scalability, during plug-in design, be sure to consider the following suggestions:

  • Minimize the amount of complex or long running processing within any plug-in that is part of a transaction that holds resources that may be required by other processes
  • Register a plug-in as a stage that does not participate in the parent transaction for situations in which the action performed by the plug-in does not need the protection of the transaction
  • Register a plug-in to run asynchronously

Should a plug-in start to execute inconsistently, either by failing intermittently or by showing variable performance, it is worthwhile to review the plug-in design to determine whether or not the intermittent nature of the failures or the fluctuations in performance can be attributed to transaction locking.

For more information about writing plug-ins, see Write Plug-ins for Microsoft Dynamics CRM and Inclusion in Database Transactions.

Roger Gilchrist

Solution Architect PM, CRM Engineering for Enterprise