In Dynamics CRM, the database is at the heart of almost all requests to the system and the place data consistency is primarily enforced.
- No CRM activities, either core platform or implementation, work completely in isolation.
- All CRM activities interact with the same database resources, either at a data level or an infrastructure level such as processor, memory, or IO usage.
- To protect against conflicting changes, each request takes locks on resources to be viewed or changed.
- Those locks are taken within a transaction and not released until the transaction is committed or aborted.
A common reason that problems can occur in this area is the lack of awareness of how customizations can affect transactions. SQL Server determines the appropriate locks to be taken by transactions on that data such as:
- When retrieving a particular record, SQL Server takes a read lock on that record.
- When retrieving a range of records, in some scenarios it can take a read lock on that range of records or the entire table.
- When creating a record, it generates a write lock against that record.
- When updating a record, it takes a write lock against the record.
- When a lock is taken against a table or record, it’s also taken against any corresponding index records.
Let’s consider SQL Server database locking and the impact of separate requests trying to access the same data. In the following example, creating an account has set up a series of processes, some with plug-ins that are triggered as soon as the record is created, and some in a related asynchronous workflow that is initiated at creation. The example shows the consequences when an account update process has complex post processing while other activity also interacts with the same account record. If an asynchronous workflow is processed while the account update transaction is still in progress, this workflow could be blocked waiting to obtain an update lock to change the same account record, which is still locked.
It should be noted that transactions are only held within the lifetime of a particular request to the platform. Locks aren’t held at a user session level or while information is being shown in the user interface. As soon as the platform has completed the request, it releases the database connection, the related transaction, and any locks it has taken.
**REFERENCE – Scalable Dynamics CRM Customization document.