2

I have an integration app that polls records of different types from an internal database every few seconds and upserts them into Salesforce. I have two pollers that run independently from one another that create custom sObjects of type Event and type Person. Event has a foreign-key to Person in the internal database and that's reflected on their sObjects via a lookup, and a Person can have multiple Events associated with him. There's a workflow within the internal database system that creates a new Event and Person simultaneously, and the integration app pollers pick up both of these and upsert them into Salesforce in a non-deterministic order.

My goal is to set the Person lookup on the Event sObject, taking into account that the records may come in out of order (a Person may be inserted before an Event, or an Event may be inserted before a Person). To handle this, I set up two triggers—one on Event and one on Person—that look to ensure that the lookup will always be set. The Event trigger takes the external ID of the Person that lives on the Event and tries to find matching Persons to set the lookup. The Person trigger finds all Events that contain the external ID of that Person and whose Person lookup is null and tries to set the lookup on the Events.

Because the records are inserted very near to each other (often within a few milliseconds), sometimes neither trigger is able to locate the records it needs (verified by placing log statements and seeing that the queries return no results). Both triggers run after insert, but I'm assuming because their execution happens pre-commit, if the trigger execution overlaps, then there's a chance that both triggers can effectively be unaware that the records they expect exist?

  1. What can I do here? Is there a way to run all this logic post-commit?
  2. Do triggers execute concurrently? Is there anyway to run them sequentially?
  • Cant the 3rd part system retry? Sometimes I have seen records inserted are not available for 1-2 seconds. – Pranay Jaiswal Nov 12 '18 at 19:58
  • Retrying is not an option. The pollers don't work that way, and it doesn't make sense to anyway since the records do get inserted. The issue is on the Salesforce side. – zed593 Nov 12 '18 at 20:09
  • I don't think it's salesforce issue, bring any system with this level of concurrency it will be same everywhere – Pranay Jaiswal Nov 12 '18 at 20:26
  • I belive it's one of such occasions where one might have to introduce a Middleware in between that can queue jobs. – Pranay Jaiswal Nov 12 '18 at 20:29
4

What can I do here? Is there a way to run all this logic post-commit?

You do need to do at least some of this asynchronously, because any other technique is going to end up in deadlock or missing records. In both triggers, build a list of unique identifiers, then pass it in to a Queueable:

public class ResolveExternals implements Queueable {
  String[] externals;
  public ResolveExternals(String[] values) {
    externals = values;
  }
  public void execute(QueueableContext context) {
    Event[] events = [SELECT ... FROM Event WHERE External_Id__c = :externals FOR UPDATE];
    Contact[] contacts = [SELECT ... FROM Contact WHERE External_Id__c = :externals FOR UPDATE];
    // match results
  }
}

In each trigger:

String[] externals = new String[0];
for(... record: Trigger.new) {
  externals.add(record.External_Id__c);
}
System.enqueueJob(new ResolveExternals(externals));

The trick here is that FOR UPDATE will wait for a few seconds for either object that might be in-flight (but they must have been started). Since it's asynchronous, even triggers fired within milliseconds of each other should be just fine.

Or, as a minor adjustment, allow one or the other to use FOR UPDATE (but not both, to avoid deadlocks), and make the other asynchronous. This should keep database contention down but use up less daily limits (250,000 asynchronous calls/day).

Do triggers execute concurrently? Is there anyway to run them sequentially?

Yes, they run concurrently. There are some techniques that you could try, but it would still be easy to accidentally construct deadlocks, and you'll cause massive database contention under heavy loads. You'd have to test it to make sure that it doesn't start causing cascading failures.

As long as the locks are always acquired in the same order, you should be just fine. Also read more about avoiding deadlocks.

  • 1
    This is a solid answer. Or you can make use of @future – crc442 Nov 12 '18 at 20:34
  • Thank you, appreciate the detailed response. If I were able to guarantee order, would I be able to do it differently? Is there any way to operate on the same batch of disparate records? – zed593 Nov 12 '18 at 21:06
  • 1
    @zed593 Each trigger operates on exactly one object. That said, if you could guarantee the order, then you'd only have to worry about one trigger at most (or, optionally, use external ID relationships and skip the queries entirely). – sfdcfox Nov 12 '18 at 21:10
  • What do you mean by "external ID relationships" exactly? Is there some automatic way of creating relationships from external ID fields? – zed593 Nov 12 '18 at 21:13
  • 1
    @zed593 There's a ton of awesome options in the API; the composite API, the sobject tree API, the SOAP API, normal REST API calls. Being able to guarantee order would have you a lot of trouble. – sfdcfox Nov 12 '18 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.