External ID Upsert as a Race Condition Defense Strategy

When integration platforms (Boomi, MuleSoft, Informatica, or custom APIs) send data to Salesforce, multiple payloads often arrive simultaneously. Two API calls might attempt to create the same Account or Order record at the same instant. Without concurrency controls, both succeed -- and you have duplicate records.

The Race Condition

Consider two API calls arriving within milliseconds, both carrying a payload for external customer ID CUST-4892:

Transaction A: INSERT Account WHERE External_Customer_Id__c = 'CUST-4892'
Transaction B: INSERT Account WHERE External_Customer_Id__c = 'CUST-4892'

Both transactions query for an existing record, find none (because neither has committed yet), and both insert. Result: two Account records for the same customer.

How External ID Upsert Solves This

Database.upsert() with an External ID field performs an atomic find-or-create operation at the database level. Salesforce uses a row lock on the External ID index to prevent concurrent inserts of the same value:

public class CustomerUpsertHandler {

    public static List<Account> upsertCustomers(List<Account> incomingAccounts) {
        Schema.SObjectField externalIdField =
            Account.External_Customer_Id__c;

        List<Database.UpsertResult> results =
            Database.upsert(incomingAccounts, externalIdField, false);

        List<Account> failures = new List<Account>();
        for (Integer i = 0; i < results.size(); i++) {
            if (!results[i].isSuccess()) {
                for (Database.Error err : results[i].getErrors()) {
                    System.debug(LoggingLevel.ERROR,
                        'Upsert failed for ' + incomingAccounts[i].External_Customer_Id__c
                        + ': ' + err.getMessage());
                }
                failures.add(incomingAccounts[i]);
            }
        }

        return failures;
    }
}

When Transaction A and Transaction B both attempt upsert on External_Customer_Id__c = 'CUST-4892':

1. One transaction acquires the lock and inserts the record.
2. The other transaction either updates the now-existing record (if it arrives after commit) or receives a DUPLICATE_VALUE error (if it collides during the lock window).

Handling the DUPLICATE_VALUE Collision

When two concurrent upserts collide on the same External ID value, the losing transaction throws a DmlException with status code DUPLICATE_VALUE. The correct response is to re-query and update:

public static void upsertWithRetry(Account incoming) {
    Schema.SObjectField extIdField = Account.External_Customer_Id__c;

    try {
        Database.upsert(incoming, extIdField);
    } catch (DmlException e) {
        if (e.getDmlType(0) == StatusCode.DUPLICATE_VALUE) {
            // Another transaction won the race -- find their record and update it
            Account existing = [
                SELECT Id FROM Account
                WHERE External_Customer_Id__c = :incoming.External_Customer_Id__c
                LIMIT 1
            ];
            incoming.Id = existing.Id;
            update incoming;
        } else {
            throw e;
        }
    }
}

When External ID Upsert Is Not Sufficient

External ID upsert protects against duplicates on a single object. It does not help with:

• Multi-object transactions: Creating an Order and its OrderItems together. The Order upsert may succeed, but a concurrent transaction could insert duplicate OrderItems if they share no External ID. Each child object needs its own External ID field and upsert logic.
• Read-modify-write patterns: If your logic queries a record, performs calculations based on its current values, and then updates it, a concurrent transaction can read stale data. Use FOR UPDATE in SOQL to acquire a row lock during the read:

Account acct = [
    SELECT Id, Lifetime_Order_Total__c
    FROM Account
    WHERE External_Customer_Id__c = 'CUST-4892'
    FOR UPDATE
];
acct.Lifetime_Order_Total__c += newOrderAmount;
update acct;

Defense-in-Depth Strategy

For mission-critical integrations, layer multiple protections:

Layer	Mechanism	Protects Against
1	External ID Upsert	Duplicate record creation
2	DUPLICATE_VALUE catch + retry	Concurrent upsert collisions
3	FOR UPDATE row locking	Stale-read race conditions
4	Application-level dedup	Duplicate payloads from the source system
5	Unique field constraints	Last-resort database-level enforcement

Key Takeaway

Database.upsert() with an External ID field is the first and most important line of defense against integration-induced duplicates. It handles the most common race condition -- concurrent creates -- atomically and without complex locking code. Layer additional protections (FOR UPDATE, retry logic, dedup queues) based on the complexity of your integration's data model and the concurrency profile of your source system.