How to handle UNABLE_LOCK_ROW errors when this is caused by a batch APEX job

Question

we are facing a serious issue. We have a batch APEX job running daily which makes updates on records fulfilling certain conditions. There is another job running parallel to this, in some cases updating or accessing to the same records than the batch job and hence, causing the error:

Update failed. First exception on row 400 with id XXX; first error: UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record or 200 records:

So, I had then read that in order to fix this kind of error you should use FOR_UPDATE in your select statement (records that have to be updated). The problem is, that you can not use FOR UPDATE for batch jobs. We tested this and effectively, we get the error:

System.QueryException: Locking is implied for each batch execution and therefore FOR UPDATE should not be specified

It will however lock the records within each execution of execute. See QueryLocator.getQuery()usage:

You cannot use the FOR UPDATE keywords with a getQueryLocator query to lock a set of records. The start method automatically locks the set of records in the batch.

Could you find a solution to this?. We would like to handle this in the code itself, because we don't have the permission to schedule the batch job for another time as the another job.

Answer 1

FOR UPDATE is not really a fix for this type of row locking issue, and there isn't a simple one-line fix. The better approach is rearchitecting how you update the data so that you don't attempt to modify the same records in parallel. The Record Locking Cheat Sheet is a great resource to help determine exactly which records are being locked and why, although in this case you already know the primary source of these errors.

There are several approaches that can help with this. The most 'guaranteed' solution would be to ensure the batch jobs run in serial order, rather than parallel. It's legal to chain batch jobs, so one could kick off the other when it finishes.

If that's not an option, you may want to add exclusion logic to your batch processes, so that they don't hit the same records in the same batch. One batch process could include in its query, for example, criteria to exclude records that are going to be updated by the other batch process that day, provided you can ensure that it will pick up the records on the following day's run.

Answer 2

With the complementary to David's answer, I would suggest you to limit the scope size, in Database.executeBatch method.

Refer Batch Apex

Database.executeBatch method takes two parameters:

• An instance of a class that implements the Database.Batchable interface.
• An optional parameter scope. This parameter specifies the number of records to pass into the execute method. Use this parameter when you have many operations for each record being passed in and are running into governor limits. By limiting the number of records, you are limiting the operations per transaction. This value must be greater than zero. If the start method of the batch class returns a QueryLocator, the optional scope parameter of Database.executeBatch can have a maximum value of 2,000. If set to a higher value, Salesforce chunks the records returned by the QueryLocator into smaller batches of up to 2,000 records. If the start method of the batch class returns an iterable, the scope parameter value has no upper limit. However, if you use a high number, you can run into other limits.