So, Dynamics Crm 2013 is here, and there’s lots to say about
the new UI, and the new features. But, many others are talking about these, so
I thought I’d start with what may seem to be an obscure technical change, but
it’s one that I welcome, and which is a significant contribution to the
stability and performance of Crm 2013.
With Crm 3.0, Microsoft changed the underlying table
structure so that any customisable entity was split across 2 tables; a base
table that contained all system attributes, and an extensionbase table for
custom attributes. For example, there was an accountbase and an
accountextensionbase table. Each table used the entity’s key as the primary
key, and the extensionbase table also had a foreign key constraint from the
primary key field to the primary key in the base table. Each entity has a SQL
view that joined the data from these table to make it appear as one table to
the platform. As I understand it, the main reason for this design was to allow for
more custom attributes, as SQL Server had a row-size limit of 8060 bytes, and
some of the system attributes were already using ~6000 bytes.
The same table design was retained in Crm 4.0 and Crm 2011.
However, Crm 2011 introduced a significant change to the plugin execution
pipeline, which allowed custom plugins to execute within the original SQL
transaction. This was a very welcome change that provided greater
extensibility. However it did mean that the duration of SQL transactions could
be extended, which means that SQL locks may be held for longer, which means
potentially more locking contention between transactions. In very occasional
circumstances, a combination of certain plugin patterns, the design of the base
and extensionbase tables, and heavy concurrent use, could give rise to
deadlocks (see below for an example).
Given this, I’m very glad that the product team retained the
facility to have plugins execute within the original transaction (then again,
it would be hard to remove this facility from us). It wouldn’t be realistic to
ask customers to reduce concurrent usage of CRM, so the only way to reduce the
potential deadlock issue was to address the design of the base and
extensionbase tables. From my investigations (sorry, but I actually quite like
investigating SQL locking behaviour), a substantial improvement could have been
made by retaining the table design, but modifying the SQL view, but a greater
improvement comes from combining the tables into one. An added advantage of
this change is that the performance of most data update operations are also improved.
Deadlock example
Here are two SQL statements generated by CRM:
select
'new_entity0'.new_entityId
as 'new_entityid'
,
'new_entity0'.OwningBusinessUnit as 'owningbusinessunit'
,
'new_entity0'.OwnerId as 'ownerid'
,
'new_entity0'.OwnerIdType as 'owneridtype'
from new_entity as
'new_entity0'
where ('new_entity0'.new_entityId
= @new_entityId0)
And
update
[new_entityExtensionBase]
set [new_attribute]=@attribute0
where ([new_entityId]
= @new_entityId1)
These were
deadlocked, with the SELECT statement being the deadlock victim. The locks that
caused the deadlock were:
-
The SELECT statement had a shared lock on the new_entityExtensionBase table, and was requesting a shared lock on new_entityBase table
- The UPDATE statement had an update lock on the new_entityBase table, and was requesting an update lock on new_entityExtensionBase table
The likely
reason for this locking behaviour was that:
-
Although the SELECT statement was requesting fields from the new_entityBase table, it had obtained a lock on the new_entityExtensionBase table to perform the join in the new_entity view
- The UPDATE statement that updates a custom attribute (new_attribute) on the new_entity entity would have been the second statement of 2 in the transaction. The first statement would modify system fields (e.g. modifiedon) in the new_entityBase table, and hence place an exclusive lock on a row in the new_entityBase table, and the second statement is the one above, which is attempting to update the new_entityExtensionBase table
Both operations
needed to access both tables, and if you’re very unlucky, then the two
operations, working on the same record, may overlap in time, and cause a
deadlock.
The new
design in Crm 2013 solves this in three ways:
-
With just the one entity table, the SELECT statement only needs one lock, and does not need to obtain one lock, then request another
- Only one UPDATE statement is required in the transaction, so locks are only required on the one table and they can be requested together, as they would be part of just one statement
- Both operations will complete more quickly, reducing the time for which the locks are held
Of these 3
improvements, either no. 1 or 2 would have been sufficient to prevent deadlocks
in this example, but it is gratifying that both improvements have been made.
The third improvement would not necessarily prevent deadlocks, but will reduce
their probability by reducing overall lock contention, and will also provide a
performance improvement.
