Failure setting lookups in triggers when upserting multiple records of different types very quickly

Question

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?

Answer 1

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.