Avoid Lock Row exception in Batch Processing

Question

I have a Batch on Lead that is scheduled to run every 5 minutes.

The criteria to pick the record in Batch is a field on lead ex. Flag = True.

let's say very first-time batch runs and there are 50k records with Flag = True. (batch size default 200)

The first batch is taking more than 5 minutes to finish because of processing and at last, the Flag will be updated to False. Since the first batch is not finished yet and the other batch for the next 5 minutes is started. The next batch or second batch will again pick the 50k or within the 50k records. Due to this, I am getting a record lock error.

Without changing the scheduling time and chaining the batch I want to avoid record lock exception. Is there any way to avoid the record lock error?

A possible solution could be chain the batch(call Batch in finish method) but I am looking for better or different solutions.

Answer 1

Our solution was to design something we call an "adaptive batch". These are more-or-less normal Batchables (extending an abstract class of our own that itself is a Batchable) that have a built-in chaining that uses a System.scheduleBatch call to ensure there is a 1 minute gap between invocations (so as to avoid exhausting async executions in a day).

These adaptive batches are "kicked" via use of a Platform Event (ensuring they all run as the Automated Process user, since we have a trigger-based consumer, which suits our needs) that are published on the event bus when their data condition is detected (e.g. in a trigger or process builder flow etc.). This invocation is also designed to ensure that only a single instance of the adaptive batch implementation can execute at any one time. This is achieved in two parts:

1. By using a given job name for the System.scheduleBatch (you cannot schedule more than one batch at a time with a given name) and
2. By checking the Async Apex Jobs to see if there is an instance already going.

The latter can be done as a pre-condition before scheduling a new batch, though must also be done in the Batchable's start method in order to avoid race conditions; you are guaranteed that only one batch start method is invoked at any one time on your org (one of the few "synchronization" points on the platform).

If a new platform event is raised (and processed by the trigger-based consumer) we do these tests and if they pass we have the "adaptive batch" ready to roll. Since only one instance can exist there's no worries about the sort of clash you are seeing. If new records would match the criteria after the batch has started, these get processed by us determining (with a COUNT query) that there are records matching the data condition when finish is called. If there are matching records (a non-zero count) here, we schedule the batch again (thereby chaining the batch execution with a slight pause).

The benefits of this approach are:

• Never have a worry about race conditions, with two instances of the batch competing against the same data
• Reduce the number of batch executions, since they are only actually executed when we know there is some data to process (rather than running the batch on a strict schedule)
• For us, ensure the batch runs against a different user than the one that caused the data conditions to be met.

(There are some "delay optimizations" done in our framework too so we don't always have a 1 minute pause before we run the batch, but rather simply ensure that the previous execution was at least 1 minute prior. This allows immediate processing when data infrequently matches the data conditions.)

You can read a little more about some of these points in this answer and this one too.

Answer 2

You should have your execute method re-query the records to make sure they are not stale and still meet the criteria.

public void execute(Database.BatchableContext context, List<SObject> records)
{
    List<SObject> exclusiveAccess = [
        SELECT ...
        FROM MyObject__c
        WHERE MyFlag__c = true
        AND Id IN :records
        FOR UPDATE
    ];
    // remaining logic should operate on this query result
}

If another process is still working on some records they will fail to lock and this execute block will skip them. If another process completes and flips the flag, those records will be omitted entirely.