Microsoft Dynamics CRM is a great platform which has numerous powerful features, but there seem to be a few shortcomings as well. At first glance it looks very much like a relational database with a powerful form engine. With this in mind, it is often used as a data store in the same way as a relational database would be. One limitation is that transactions are not supported within CRM 4.0 through the Web Service.

This has serious implications when building interfacing systems with forms which effect multiple CRM entities. In my opinion there seem to be a few strategies in dealing with this:

1) Logging the exception - Well, this should be done in any case but in effect nothing is done to avoid inconsistent transactions. This is the simplest strategy, but could have some very serious associated risks.

2) Designing forms which avoid updating multiple entities at once and thereby avoiding the need for transactions. Eg. If a form which was intended to update a contact entity and a related address entity, 2 separate forms with “save” buttons on each page would enable updating each entity separately. From a usability perspective it would be understood that the two actions are separate. This is not always possible, and could make for a more cumbersome interface though. Eg. In a Parent-child relationship, the parent record, and by implication, the GUID, may not be available when wanting to insert a related entity.

3) Develop custom code to manage the transaction state. This is far from simple though. Code would have to ensure the ACID properties - Atomicity, Consistency, Isolation and Durability.

Each operation in the transaction would have to be firstly stored in a list. Therefore, if an insert operation succeeds with a subsequent operation failing, the record can be deleted. Once an insert is successfully achieved, the GUID would be stored with the record in the list.

There are some serious failings with this approach though. Firstly, there are often CRM workflows associated with insert/update operations. If a workflow is started upon creation and then the record is deleted, problems may occur with the delete. Records of partially complete transactions may still be modified or read by other processes before completion of the transaction. This could be partially rectified by means of using a singleton class which provides lock mechanism on a record. But this can only be from the perspective of interfacing systems. Nothing would prevent a CRM user from reading dirty data. If an delete operation has to be rolled back, an insert would be performed with the original data - The record would get a different GUID, which could have other implications. These scenarios are fairly simple though - It gets much more complex when trying to restore the state of entities with multiple relationships, as changes would have to cascade.

As can be seen, there doesn't seem to be a simple solution to the transaction problem, and it should be carefully considered before using CRM as data store for interfacing systems, assuming that just because it uses SQL server as an underlying database, it supports all database like functions.



Twitter Delicious Facebook Digg Stumbleupon Favorites More