Dataverse: Implement Pre-lock in a plug-in transaction

Lately, my mind has traveled a lot on designing a plugin that can handle concurrency issues. Even though the framework itself has UpdateRequest and we can pass the RowVersion, I found the implementation does not always fit with some of the scenarios especially if you want to avoid throwing "Error" in the design. And, after several days, I stumbled onto the "theory" about concurrency issues which didn't make sense to me at first (stupid me 🤣). And hey, it definitely works which makes me write this blog post! So, to help you understand the scenario, I created the below tables:

Dataverse tables for the scenario + logic

We want to ensure that the Applied Promotion (SUM of Promotion Amount) will not be applied more than the Promotion Limit.

To test the logic, I created the below exe which will be triggered 6 times to mimic busy operations (to trigger the concurrency issues):

using DataverseClient;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;

var builder = Helper.CreateHostBuilder().Build();
var serviceProvider = builder.Services;

var service = serviceProvider.GetRequiredService<ServiceClient>();

var random = new Random();

var promotion = GetPromotions(service)[0];

var members = GetMembers(service);
var errorCount = 0;
var index = 0;
var listTask = new List<Task>();

var start = DateTime.Now;
do
{
    var randMemberIndex = random.Next(members.Length);
    var member = members[randMemberIndex];

    var promotionValue = (decimal)random.Next(1, 50);
    var appliedPromotion = new Entity("tmy_appliedpromotion")
    {
        ["tmy_promotionid"] = promotion.ToEntityReference(),
        ["tmy_contactid"] = member.ToEntityReference(),
        ["tmy_promotion"] = promotionValue
    };
    var task = new Task(() =>
    {
        try
        {
            var id = service.Create(appliedPromotion);
            Console.WriteLine($"Created with id: {id} - {DateTime.Now}");
        }
        catch (Exception ex)
        {
            errorCount++;
            Console.WriteLine($"Error: {ex.Message} - {DateTime.Now}");
        }
    });
    listTask.Add(task);
    index++;
} while (index < 50);

// This will start almost at the same time 50 create operations
Parallel.ForEach(listTask, (task) =>
{
    task.Start();
});

Task.WaitAll(listTask.ToArray());
var end = DateTime.Now;
Console.WriteLine($"Finished! Elapsed time {(end - start).TotalSeconds}. Error count: {errorCount}.");

Console.ReadLine();

static Entity[] GetMembers(IOrganizationService service)
{
    var query = new QueryExpression("contact")
    {
        ColumnSet = new ColumnSet(false)
    };
    var result = service.RetrieveMultiple(query);

    return result.Entities.ToArray();
}

static Entity[] GetPromotions(IOrganizationService service)
{
    var query = new QueryExpression("tmy_promotion")
    {
        ColumnSet = new ColumnSet(false)
    };

    var result = service.RetrieveMultiple(query);

    return result.Entities.ToArray();
}

IfRowVersionMatches

Here is the plugin using IfRowVersionMatches:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;

namespace BlogPackage
{
    public class PreOperationAppliedPromotion : PluginBase
    {
        public PreOperationAppliedPromotion() : base(typeof(PreOperationAppliedPromotion))
        {
        }

        protected override void ExecuteDataversePlugin(ILocalPluginContext localPluginContext)
        {
            var target = localPluginContext.PluginExecutionContext.InputParameterOrDefault<Entity>("Target");
            var promotionRef = target.GetAttributeValue<EntityReference>("tmy_promotionid");
            var appliedAmount = target.GetAttributeValue<decimal>("tmy_promotion");
            if (promotionRef == null) throw new InvalidPluginExecutionException("Promotion Id is null", PluginHttpStatusCode.BadRequest);

            var promotion = localPluginContext.InitiatingUserService.Retrieve("tmy_promotion", promotionRef.Id, new ColumnSet("tmy_appliedpromotion1", "tmy_limitpromotion"));

            var limitPromotion = promotion.GetAttributeValue<decimal>("tmy_limitpromotion");
            var appliedPromotion = promotion.GetAttributeValue<decimal>("tmy_appliedpromotion1");

            if (limitPromotion < appliedPromotion + appliedAmount)
            {
                // Remove the promotion
                target["tmy_promotionid"] = null;
                return;
            }

            var update = new Entity("tmy_promotion", promotionRef.Id)
            {
                ["tmy_appliedpromotion1"] = appliedPromotion + appliedAmount,
                RowVersion = promotion.RowVersion
            };

            var updateReq = new UpdateRequest
            {
                Target = update,
                ConcurrencyBehavior = ConcurrencyBehavior.IfRowVersionMatches
            };

            localPluginContext.InitiatingUserService.Execute(updateReq);
        }
    }
}

The result of the above:

IfRowVersionMatches result

As you can see above, the result varies between 18 - 22 seconds. But the highlight on the above is about the error count if, during the update of the Promotion, the Promotion RowVersion is updated by another operation, the system will reject and throw this error.

Pre-lock in a plug-in transaction

To avoid the error and ensure concurrency, here is what we can implement:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Query;

namespace BlogPackage
{
    public class PreOperationAppliedPromotion : PluginBase
    {
        public PreOperationAppliedPromotion() : base(typeof(PreOperationAppliedPromotion))
        {
        }

        protected override void ExecuteDataversePlugin(ILocalPluginContext localPluginContext)
        {
            var target = localPluginContext.PluginExecutionContext.InputParameterOrDefault<Entity>("Target");
            var promotionRef = target.GetAttributeValue<EntityReference>("tmy_promotionid");
            var appliedAmount = target.GetAttributeValue<decimal>("tmy_promotion");
            if (promotionRef == null) throw new InvalidPluginExecutionException("Promotion Id is null", PluginHttpStatusCode.BadRequest);

            // lock the promotion (db lock)
            var lockpurpose = new Entity("tmy_promotion", promotionRef.Id) { ["tmy_updatestatus"] = target.Id.ToString() };
            localPluginContext.InitiatingUserService.Update(lockpurpose);

            var promotion = localPluginContext.InitiatingUserService.Retrieve("tmy_promotion", promotionRef.Id, new ColumnSet("tmy_appliedpromotion1", "tmy_limitpromotion"));

            var limitPromotion = promotion.GetAttributeValue<decimal>("tmy_limitpromotion");
            var appliedPromotion = promotion.GetAttributeValue<decimal>("tmy_appliedpromotion1");

            if (limitPromotion < appliedPromotion + appliedAmount)
            {
                // Remove the promotion
                target["tmy_promotionid"] = null;
                return;
            }

            var update = new Entity("tmy_promotion", promotionRef.Id)
            {
                ["tmy_appliedpromotion1"] = appliedPromotion + appliedAmount
            };

            localPluginContext.InitiatingUserService.Update(update);
            // once the transaction finished, relased the db lock.
        }
    }
}

As you can see, once we know the Promotion reference, the first thing that we do is to update the Promotion itself. This triggers what is in the documentation called "Write Lock". Once, the operation is done the system commits the transaction. It would release the "Write Lock". But, what you need to understand here is, that if your Table/Entity has Pre-Operation and also Post-Operation logic (as it shares the same Transaction mechanism), the lock time will be increased.

Here is the result of the above Plugin:

Pre-lock result

As you can see, the exe finished around and bumped to around 20 - 25 seconds. The result in SQL 4 CDS does not exceed the Promotion Limit which is correct!

Summary

I took the below picture from the documentation that you can read here:

Auto-numbering example from MS Learn doc

To summarize this, as you can see in the above flow. The first user will lock (via the Update method) the row no matter what's the logic. When this "Lock" was implemented, it caused the other requests that need this row in the "Wait" condition. After the "Lock" has been removed, it continues the next process until finished.

Hope you learn something and happy CRM-ing!