Database.upsert() Lock Errors: The Silent Failure Pattern

Overview

Database.upsert() with allOrNone=false returns errors as UpsertResult objects rather than throwing exceptions. When an UNABLE_TO_LOCK_ROW error occurs at the upsert level, it does not trigger catch blocks or Transaction Finalizers -- it silently reports the failure in the result array. If your code only checks for specific error codes like DUPLICATE_VALUE, lock errors fall through unhandled and affected records are silently lost.

This article explains the gap between upsert-level errors and exception-level errors, shows the real-world failure pattern, and provides the fix using a safety-net throw to re-engage retry mechanisms.

Database.upsert() Lock Errors: The Silent Failure Pattern

In Salesforce Apex, there are two distinct error surfaces for DML operations, and confusing them leads to silent data loss in concurrent processing scenarios.

Two Error Surfaces

Surface 1: Exceptions (Caught by try/catch and Finalizers)

When FOR UPDATE cannot acquire a lock, Salesforce throws an exception:

try {
    List<Order> locked = [SELECT Id FROM Order WHERE Id IN :ids FOR UPDATE];
} catch (QueryException e) {
    // UNABLE_TO_LOCK_ROW — caught here
    // RetryFinalizer also catches this via UNHANDLED_EXCEPTION
}

Exceptions propagate up the call stack. If uncaught, they trigger Transaction Finalizers with ParentJobResult.UNHANDLED_EXCEPTION, enabling automatic retry.

Surface 2: UpsertResult Objects (NOT Caught by try/catch)

When Database.upsert(..., false) encounters a lock error, it does not throw an exception. Instead, it returns the error inside the UpsertResult array:

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

for (Integer i = 0; i < results.size(); i++) {
    if (!results[i].isSuccess()) {
        // Lock error is HERE — as a Database.Error object
        // No exception was thrown. Finalizer was NOT triggered.
        for (Database.Error err : results[i].getErrors()) {
            System.debug(err.getStatusCode()); // UNABLE_TO_LOCK_ROW
        }
    }
}

This is the critical gap: upsert-level lock errors are invisible to exception handlers and Transaction Finalizers.

The Real-World Failure

Consider a Queueable that processes records from a bulk API upload:

1. Data Loader uploads 219 records sharing the same external key
2. Salesforce splits this into two API batches (200 + 19)
3. Each batch enqueues a separate Queueable
4. Both Queueables attempt to upsert the same parent Order

Queueable 1 succeeds — upserts the Order, inserts 200 child records.

Queueable 2 runs 3 seconds later:

• Database.upsert(orders, ExternalId__c, false) hits UNABLE_TO_LOCK_ROW
• The error appears in UpsertResult, not as an exception
• If the error handler only checks for DUPLICATE_VALUE:

// BEFORE FIX — only handles DUPLICATE_VALUE
if (err.getStatusCode() == StatusCode.DUPLICATE_VALUE) {
    failedIds.add(records[i].External_Key__c);
}
// UNABLE_TO_LOCK_ROW falls through to else → logged but not retried

Result: Queueable 2 cannot find the Order (not yet committed by Queueable 1), creates 0 child records for its 19 overflow records. 19 records silently lost.

The Fix: Two Changes

Change 1: Treat Lock Errors as Recoverable

Add UNABLE_TO_LOCK_ROW alongside DUPLICATE_VALUE in the error handler:

for (Database.Error err : results[i].getErrors()) {
    if (err.getStatusCode() == StatusCode.DUPLICATE_VALUE ||
        err.getStatusCode() == StatusCode.UNABLE_TO_LOCK_ROW) {
        // Both mean: the record exists (or will exist) — recover gracefully
        failedExternalIds.add(records[i].External_Key__c);
    } else {
        // Genuinely unexpected error — log and handle differently
        logUnexpectedError(err);
    }
}

Change 2: Safety-Net Throw for Finalizer Re-Engagement

After the upsert, attempt to re-fetch the parent record. If the other Queueable has not yet committed, the re-fetch returns nothing. Throw an exception to trigger the RetryFinalizer:

// After processing upsert results, check for unresolved external IDs
for (String failedId : failedExternalIds) {
    if (!externalIdToRecordMap.containsKey(failedId)) {
        // Other Queueable hasn't committed yet — force retry via Finalizer
        throw new QueryException(
            'UNABLE_TO_LOCK_ROW: Record for key ' + failedId +
            ' not yet committed. Retrying via finalizer.'
        );
    }
}

The thrown exception:

1. Rolls back the current transaction (no partial writes)
2. Triggers the RetryFinalizer with UNHANDLED_EXCEPTION
3. Finalizer re-enqueues the Queueable after a brief delay
4. On retry, the first Queueable has committed, and the re-fetch succeeds

Verified Recovery Timeline

Time	Event
T+0s	Queueable 1: Upserts Order, inserts 200 child records
T+3s	Queueable 2: Upsert hits UNABLE_TO_LOCK_ROW → safety net throws → RetryFinalizer re-enqueues
T+14s	Queueable 2 retry: DUPLICATE_VALUE (Order exists) → finds Order → skips 200 existing children → inserts 19 remaining

Result: All 219 records accounted for.

The General Pattern

Any time you use Database.upsert(..., false) or Database.insert(..., false) in a concurrent context:

1. Check for lock errors in the result array — they are not exceptions
2. Decide if the error is recoverable — lock errors usually are (the record exists or will exist)
3. Re-engage your retry mechanism — throw an exception if needed to trigger Finalizers
4. Include the error type in the thrown message — so the Finalizer can distinguish retryable failures from permanent ones

// General pattern for concurrent-safe partial DML
List<Database.UpsertResult> results = Database.upsert(records, keyField, false);
Boolean needsRetry = false;

for (Integer i = 0; i < results.size(); i++) {
    if (!results[i].isSuccess()) {
        for (Database.Error err : results[i].getErrors()) {
            if (err.getStatusCode() == StatusCode.UNABLE_TO_LOCK_ROW ||
                err.getStatusCode() == StatusCode.DUPLICATE_VALUE) {
                needsRetry = true;
            }
        }
    }
}

if (needsRetry && !canResolveLocally()) {
    throw new RetryableException('Lock contention detected — re-enqueue via Finalizer');
}

Key Takeaways

• Database.upsert(..., false) returns lock errors in UpsertResult objects, not as exceptions.
• Transaction Finalizers only trigger on unhandled exceptions — they cannot see upsert-level errors.
• Always check for UNABLE_TO_LOCK_ROW alongside DUPLICATE_VALUE in partial DML result handlers.
• Use a safety-net throw to bridge the gap between upsert-level errors and exception-based retry mechanisms.
• This pattern is critical for any Queueable that processes bulk data where multiple jobs may target the same records.