Thursday, 29 March 2018

Concurrent or Consistent - or both

A lesser-known feature that CRM 2016 brought to us is support for optimistic concurrency in the web service API. This may not be as exciting as some features, but as it's something I find exciting, I thought I write about it.

Am I an optimist
So, what is it about ?  Concurrency control is used to ensure data remains consistent when multiple users are making concurrent modifications to the same data. The two main models are pessimistic concurrency and optimistic concurrency. The difference between the 2 can be illustrated by considering two users (Albert and Brenda), who are trying to update the same field (X) on the same record (Y). In each case the update is actually 2 steps (reading the existing record, then updating it), and Albert and Brenda's try and do the steps in the following time sequence:
  1. Albert reads X from record Y (let's say the value is 30)
  2. Brenda reads record Y (while it's still 30)
  3. Albert updates record Y (Albert wants to add 20, so he updates X to 50)
  4. Brenda updates record Y (she wants to subtract 10, so subtracts 10 from the value (30) she read in step 2, so she updates X to 20) 
If we had no concurrency control, we would have started with 30, added 20, subtracted 10, and found that apparently 30 + 20 - 10 = 20. Arguably we have a concurrency model, which is called 'chaos', because we end up with inconsistent data.
To avoid chaos, we can use pessimistic concurrency control. With this, the sequence is:
    1. Albert reads X from record Y (when the value is 30), and the system locks record Y
    2. Brenda tries to read record Y, but Albert's lock blocks her read, so she sits waiting for a response
    3. Albert adds 20 to his value (30), and updates X to 50, then the system releases the lock on Y
    4. Brenda now gets her response, which is that X is now 50
    5. Brenda subtracts 10 from her value (50), and updates X to 40
    So, 30 + 20 - 10 = 40, and we have consistent data. So we're all happy now, and I can finish this post.
    Or maybe not. Brenda had to wait between steps 2 and 4. Maybe Albert is quick, but then again, maybe he isn't, or he's been distracted, or gone for a coffee. For this to be robust, locks would have to placed whenever a record is read, and only released when the system knows the Albert is not still about to come back from his extended coffee break. In low latency client-server systems this can be managed reasonably well (and we can use different locks to distinguish between an 'I'm just reading', and 'I'm reading and intending to update'), but with a web front-end like CRM, we have no such control. We've gained consistency, but at a huge cost of concurrency. This is pessimistic concurrency.
    Now for optimistic concurrency, which goes like this:
    1. Albert reads X (30) from record Y (when the value is 30), and also reads a system-generated record version number (let's say it's version 1)
    2. Brenda reads record Y (while it's still 30), and the system-generated record version number (which is still version 1, as the record's not changed yet)
    3. Albert adds 20 to his value (30), and updates X to 50. The update is only permitted because Albert's version number (1) matches the current version number (1). The system updates the version number to 2
    4. Brenda subtracts 10 from her value (30), and tries to update X to 20.This update is not permitted as Brenda's version number (2) does not match the current version number (1). So, Brenda will get an error
    5. Brenda now tries again, reading now read the current value (50) and version number (2), then subtracting 10, and the update is allowed
    The concurrency gain is that Albert, Brenda and the rest of the alphabetical users can read and update with no blocks, except when there is a conflict. The drawback is that the system will need to do something (even if it is just give an error message), when there is a conflict.
    What are the options
    Given this post is about a feature that was introduced in CRM 2016, what do you think happened before (and now, because you have to explicitly use optimistic concurrency). If it's not optimistic concurrency, then it's either pessimistic or chaos. And it's not pessimistic locking, as if Microsoft defaulted to this, then CRM would grind to a locked halt if users often tried to concurrently access records.

    Maybe I want to be a pessimist
    As chaos sounds bad, maybe you don't believe that CRM would grind to a locked halt, or you're happy that users don't need concurrent access, or you've been asked to prevent concurrent access to records (see note 1). So, can we apply pessimistic locking ? The short answer is 'no', and most longer answers also end up 'no'. Microsoft give us almost no control over locking (see note 2 for completeness) within CRM, and definitely no means to hold locks beyond any one call. If you want to prolong the answer as much as you can, you might conceive a mechanism whereby users only get user-level update access to records, and have to assign the record to themselves before they can update it, but this doesn't actually work either, as a user may still be making the update based on a value they read earlier. And you can't make it user-level read access, and the user then wouldn't be able to see a record owned by someone else to be able to assign it to themselves.

    OK, I'll be an optimist
    So, how do we use optimistic concurrency ? First of all, not every entity is enabled for optimistic concurrency, but most are. This is controlled by the IsOptimisticConcurrencyEnabled property of the entity, and by default it is true for all out-of-box entities enabled for offline sync, and for all custom entities. You can check this property by querying the entity metadata (but not in the EntityMetadata.xlsx document in the SDK, despite the SDK documentation)

    Then, to use optimistic concurrency you need to do at least 2 things, and preferrably 3:
    1. In the Entity instance that you are sending to the Update, ensure the RowVersion property is set to the RowVersion that you received when you read this record 
    2. In the UpdateRequest, set the ConcurrencyBehavior to IfRowVersionMatches
    3. Handle any exceptions. If there is a row version conflict (as per my optimistic scenario above), then you get a ConcurrencyVersionMismatch exception. 
    For a code example, see the SDK
    I've described this for an Update request, and you can also use it for a Delete request, and I hope you'll understand why it doesn't apply to a Create request.

    One word of warning; I believe that some entities fail when using optimistic concurrency - this seems to be the entities that are metadata related (e.g. webresource or savedquery). I suspect this is because the metadata-related internals work on different internal (at the SQL level) concurrency from most other entities.

    How much does it matter
    I've left this till last, otherwise you may not have read the rest of the post, as it often doesn't matter. Consistency issues are most relevant if there's a long time between a read and the corresponding update. The classic example is offline usage (hence why it's enabled for out-of-box entities enabled for offline sync). I also see it as relevant for some bulk operations; for example we do a lot of bulk operations with SSIS, and for performance reasons, there's often a noticeable time gap between reads and writes in an SSIS data flow.


    1. During CRM implementatons, if asked 'Can we do X in CRM ?', I very rarely just so no, and I'm more likely to say no for reasons other than purely technical ones. However, when I've been asked to prevent concurrent access to records, then this is a rare case when I go for the short answer of 'no'
    2. We can get a little bit of control over locking within a synchronous plugin, as this runs within the CRM transaction. This is the basis of the most robust CRM-based autonumber implementations. However, the lock can't be held outside of the platform operation
    3. My examples have concentrated on updating a single field, but any talk of locking or row version is at a record level. If Albert and Brenda were changing different fields, then we may not have a consistency issue to address. However, for practical reasons, any system applies locks and row versioning at a record, and not field level. Also, even if the updates are to different fields, it is possible that the change they make is dependent on other fields that may have changed, so for optimistic concurrency we do get a ConcurrencyVersionMismatch if any fields had changed