Preventing Duplicate Records with FOR UPDATE Locking in Concurrent Queueables

When multiple Queueable jobs execute simultaneously, they operate in separate transactions with independent database visibility. This creates race conditions where deduplication queries return empty results because concurrent transactions have not yet committed their inserts.

The Race Condition Timeline

Consider two Queueable jobs that both need to create or update a summary record for the same parent:

Time	Job A	Job B
T1	Query: "Does summary exist?" -> No	(not started yet)
T2	Preparing to insert summary	Query: "Does summary exist?" -> No
T3	INSERT summary record	Preparing to insert summary
T4	COMMIT	INSERT summary record (duplicate)
T5		COMMIT

At T2, Job B cannot see Job A's uncommitted insert. Both jobs conclude no summary exists, and both insert one -- creating a duplicate.

FOR UPDATE Locking

The FOR UPDATE clause in SOQL acquires a row-level lock on the queried records. Any other transaction attempting to lock the same rows will wait (up to approximately 10 seconds) before proceeding or timing out.

public class SummaryProcessor implements Queueable {
    private Id parentId;

    public SummaryProcessor(Id parentId) {
        this.parentId = parentId;
    }

    public void execute(QueueableContext ctx) {
        // Lock the parent record to serialize concurrent access
        List<Parent__c> locked = [
            SELECT Id FROM Parent__c
            WHERE Id = :parentId
            FOR UPDATE
        ];

        // Now safe to check and create child records
        List<Summary__c> existing = [
            SELECT Id FROM Summary__c
            WHERE Parent__c = :parentId
        ];

        if (existing.isEmpty()) {
            insert new Summary__c(Parent__c = parentId);
        }
    }
}

By locking the parent record first, Job B must wait until Job A commits. When Job B's lock is acquired, it re-queries and finds Job A's committed summary -- no duplicate is created.

Handling Lock Timeouts

When FOR UPDATE cannot acquire the lock within the timeout window, Salesforce throws one of two exceptions:

• QueryException with message containing "Record Currently Unavailable"
• DmlException with message containing "UNABLE_TO_LOCK_ROW"

Both must be caught:

public void execute(QueueableContext ctx) {
    try {
        List<Parent__c> locked = [
            SELECT Id FROM Parent__c
            WHERE Id = :parentId
            FOR UPDATE
        ];
        processSummary();
    } catch (QueryException e) {
        if (e.getMessage().contains('Record Currently Unavailable')) {
            handleLockFailure();
        } else {
            throw e;
        }
    }
}

The RetryFinalizer Pattern

Salesforce Transaction Finalizers (available in API v54.0+) allow a Queueable to re-enqueue itself when a lock timeout occurs:

public class SummaryProcessor implements Queueable {
    private Id parentId;
    private Integer retryCount;

    public SummaryProcessor(Id parentId, Integer retryCount) {
        this.parentId = parentId;
        this.retryCount = retryCount;
    }

    public void execute(QueueableContext ctx) {
        System.attachFinalizer(new RetryFinalizer(parentId, retryCount));
        // Processing logic with FOR UPDATE
    }
}

public class RetryFinalizer implements Finalizer {
    private Id parentId;
    private Integer retryCount;
    private static final Integer MAX_RETRIES = 3;

    public RetryFinalizer(Id parentId, Integer retryCount) {
        this.parentId = parentId;
        this.retryCount = retryCount;
    }

    public void execute(FinalizerContext ctx) {
        if (ctx.getResult() == ParentJobResult.UNHANDLED_EXCEPTION
            && retryCount < MAX_RETRIES) {
            System.enqueueJob(
                new SummaryProcessor(parentId, retryCount + 1)
            );
        }
    }
}

Defense-in-Depth Strategy

No single technique is bulletproof. Combine multiple layers:

Layer	Technique	What It Prevents
1. External ID Upsert	upsert record ExternalId__c;	Duplicates based on unique key
2. FOR UPDATE Lock	Lock parent before querying children	Race conditions between concurrent jobs
3. Dedup Query	Check existence before insert	Duplicates from sequential re-runs
4. Unique Field Constraint	Unique index on the object	Last-resort database-level prevention

// Layer 1: Upsert with External ID
Summary__c summary = new Summary__c(
    External_Key__c = parentId + '_SUMMARY',
    Parent__c = parentId,
    Total__c = calculatedTotal
);
upsert summary External_Key__c;

The External ID upsert is often the simplest and most effective single defense. The database enforces uniqueness atomically, regardless of transaction timing. Use FOR UPDATE locking when you need to read-then-write with guaranteed consistency, and add a Finalizer for automatic retry on contention.

Key Takeaways

• Concurrent Queueable jobs execute in isolated transactions that cannot see each other's uncommitted data.
• FOR UPDATE serializes access but introduces potential lock timeouts.
• Always handle both QueryException and DmlException variants of lock errors.
• External ID upsert is the most robust single-layer defense against duplicates.
• Combine techniques for defense-in-depth in high-concurrency scenarios.