6

I have created a function that is:

  • Run every hour in a batch job.
  • May be called on demand.
  • May be called on-demand by a webservice method.

It is summarised as follows:

  • Lock the parent records.
  • Query the child records.
  • Process the child records to ensure that there are no duplicates.
  • Write to database.

So, something along these lines:

    public static void runMethod(List<Id> ids) {
      List<MyObject__c> records = [SELECT Id FROM MyObject__c WHERE Id in :ids FOR UPDATE];
      List<ChildObj__c> childRecs = queryChildRecords(records);
      List<ChildObj__c> childRecsIns = checkDuplicatesAndCreateInsertList(childRecs);
      insert childRecsIns;
    }

If the hourly job (A) gets executed at the same time as the on-demand (B), the FOR UPDATE creates a lock and thus forces the 2nd process to wait until the first one commits. Hence, this protects against duplicates created in the ChildObj__c. I believe by doing it this way, it will only ever allow runMethod to run once per recordId.

However, from time-to-time, I occasionally get duplicates created and I cannot figure out why. I estimate it's maybe happening around 0.5% of the time and I'm unable to reproduce the issue. For me, it's as if when (A) finishes the transaction, the queryChildRecords in (B) occasionally does not pickup the latest records if queried immediately after. But to my understanding, this is not how databases work - however, I can't think of any other reason why the issue occurs.

Or another theory is that it also appears that the lock is occasionally released early before the commit and hence (B) is not retrieving the latest records. But to my understanding from Salesforce docs, this should not ever be the case.

I've also edited the post to indicate that this method can also be called by a webservice method. From my research, this shouldn't change anything. I've done testing to simulate this call (via POSTMAN) and the lock appears to be working as intended and I cannot re-produce the issue.

From the logs so far, it appears that either the batch job or the run on demand can create the duplicates.

Hypothetically, if I removed the FOR UPDATE in the SOQL, I can reproduce the duplicates 100% of the time, so I know that it's taking effect.

Appreciate any insight or assistance.

8
  • So you have no issues if you remove for update? I presume your issue is that, while For Update prevents it from being updated by the on demand run, it doesn't prevent it from being queried. Hence, why your query might get "incorrect" results as your insert hasn't finished? Mar 25, 2022 at 12:18
  • 1
    Just to clarify, when I removed the FOR UPDATE, I managed to re-produce the duplicates all the time, indicating that for the 99.5% of the time the FOR UPDATE, is working as intended. I'm unable to explain why the issue only happens intermittently (i.e. 0.5% of the time). I saw your linked article, and to my understanding, it doesn't answer my issue - this is because, the process attempts the lock at the start of the process, and it's not performing a normal query, but rather a lock.
    – Sean
    Mar 25, 2022 at 12:26
  • So, for instance, if I run the code above simultaneously with the IDs (using execute anonymous for example), it throws a QueryException as expected for one of them due to the lock. Despite trying many different timings, I'm unable to reproduce the issue at all.
    – Sean
    Mar 25, 2022 at 12:34
  • Thanks for explaining (should've finished my tea first). Also welcome! It's a good question (+1). FYI - you can edit your post to add any details too as you debug. I'd guess the scenario you'd want to hit is when it appropriately locks the other query for several seconds but then it eventually gets the hold (no queryexception). In my very basic reproduction (and small sample size), the query gets the latest # of child records that were inserted by the first run even when they collide and both complete. However, this is without any triggers and complexity that I'm sure your org might have. Mar 25, 2022 at 12:57
  • Are you able to tell from the CreatedDate, if the duplicates were produced by the batch job calling the runMethod or by the "on demand" action, or if this is a mix? Mar 25, 2022 at 15:34

0

You must log in to answer this question.

Browse other questions tagged .