Can a DML retry solve UNABLE_TO_LOCK_ROW race conditions?

Question

I have 2 Batch classes working on different child objects of a common master object. Batch Batches are started parallel to speed up this long running cleansing operation.

It depends on the underlying data structure which batch finishes first. But when both are finished the master object needs to be marked as cleaned.

Do do this both batches add and remove put their job id to a text field in the master object. If it is empty the master record is clean.

My problem now is that both batches sometimes try to write to the master at the same time and I receive a UNABLE_TO_LOCK_ROW exception.

I thought I could simply retry the update operation as long as it fails and my Limits are not consumed. Would that work and be a feasable solution or are there better ways?

public void modify() {
    CustomObject__c record = query();

    //...

    try {
        update record;
    }
    catch(DMLException ex) {
        if(ex.getMessage().contains('UNABLE_TO_LOCK_ROW')) {
            modify();
        }
    }
}

Answer 1

If you use the FOR UPDATE keyword in your query, the database will wait up to about 5 seconds before declaring that it cannot acquire the lock (this should normally be plenty of time). This event can be captured, and you can attempt to retry, assuming you have any SOQL queries/rows remaining. I've found that, in practice, it's rarely necessary to retry a query, however, assuming your transaction time is small (as well it should be).

Answer 2

Another solution might be to create a cleaned checkbox field on the children. After cleaning, the batch job will set that checkbox on the child.

Then create a rollup summary field on the parent that counts all children with cleaned to false.

Then you can filter on all master records that have > 0 uncleaned children?

Answer 3

I spoke with SF about this issue. Apex detects deadlocks via timeout. Retry by repeating the DML may be a viable technique. The important thing to understand is what you are effectively doing is simply increase the wait before timeout.

As others have pointed out, FOR UPDATE may be a good technique to avoid deadlock in some situations so you may wish to employ that technique first.

Regarding the way you coded it, you have two issues, one major and one minor. The major issue is that you will retry forever. You should probably limit the retry to a few tries. If it takes longer than two or three attempts, something may be very wrong. The minor issue is that you are using recursion so the stack is getting deeper and deeper with each attempt. You can fix both issues by putting the DML in a few loop and break from the for loop on success.