Any reason NOT to use FOR UPDATE?

Question

I'm intrigued by the record locking issues that Apex seems to allow so easily, and I've begun using FOR UPDATE where I can to mitigate them, but it seems so infrequently recommended in documentation. This leads me to wondering - when we query for records we want to update, wouldn't we ALWAYS want to use FOR UPDATE, if there is no downside? It seems I would see it in other code more often if it's really best practice.

By the same token, in an after trigger context, is it best to query with FOR UPDATE, instead of modifying and updating Trigger.new directly? It would cost one SOQL query of course, but it still seems a very worthwhile trade off.

How do you approach it?

Answer 1

General

If you have FOR UPDATE on your query, you cause the record(s) (and potentially related records too) to be locked at the time of the query execution in the transaction against the Salesforce org. You may then have significant processing (perhaps as much as 10 CPU seconds for synchronous executions and 60 CPU seconds for asynchronous processing) between this point and the point at which you perform any DML. This means that if you always use FOR UPDATE you actually increase the likelihood of receiving an UNABLE_TO_LOCK_ROW error in concurrent transactions using the same or different logic flows against the same records.

Depending on the business logic embodied within or based around a given object, you may find that the object has distinct groupings of fields relevant to different use cases and scenarios that may or may not be able to happen concurrently without problem.

While certain, critical logic flows may need to guarantee effectively single-threaded access to the records of interest, i.e. guarantee that race conditions cannot occur (for example assigning some previously unassigned work to a user - you don't want to accidentally notify two people about the same work due to concurrent update), other flows may manage just one or two fields that are not super critical and where race conditions are unlikely to happen or cause issues (for example, updating when the work was last reviewed, where a few seconds difference in this timestamp is likely irrelevant).

As stated by sfdcfox, if you haven't yet locked a record before the DML operation, that operation automatically locks it at that point (and thus for a shorter period of time in your transaction flow).

Batches

A common scenario where you might expect to query records for update is in a Database.Batchable implementation. However, as per the documentation:

Using FOR UPDATE in SOQL queries to lock records during update isn’t applicable to Batch Apex.

Indeed, you are not permitted to use it explicitly in your query locator.

This same documentation describes how you might explicitly re-query the records with FOR UPDATE if you know you need to lock them for guaranteed single-threaded access and update.

SObject content for DML

It is much better to always construct a minimalist SObject (e.g. Account) against which to perform DML, setting only the ID and the field(s) you really want to update, since that way you minimize the footprint of the write to the record and avoid resetting fields you don't care about due to concurrent updates. This applies in trigger after handling as well as in general apex.

Taking this approach means you can minimize the use of FOR UPDATE too, reducing the length of time the record is locked for and thus reducing the chance that you will see the UNABLE_TO_LOCK_ROW error.

Conclusion

Use of FOR UPDATE can actually increase likelihood of seeing UNABLE_TO_LOCK_ROW and bluntly locks the whole record disallowing (or at least delaying) concurrent but non-overlapping-data updates to individual records. It should be used only in those scenarios where you need to guarantee single-threaded access and update (and then you should try to minimize the time between accessing/locking and updating the record).

Answer 2

A lock is always necessary to update a record, but a transaction might already have a lock on the record you want to update, so you don't necessarily need FOR UPDATE in every query.

Performing a DML on a Contact, for example, also automatically locks the Account for that Contact. If you want to update the Account in a trigger, or even after performing a DML operation on the Contact, you can logically assume that the Account is already locked, and you have priority to performing an update on the Account.

In addition, a DML statement has a period of time where it will wait to try and acquire a lock. This means that small, fast updates don't necessarily need to acquire a lock explicitly. However, as you found, this is how you get an UNABLE_TO_LOCK_ROW exception.

The documentation team tends to focus on specific features in their own area. That's why you don't see an entry for FOR UPDATE in the SOQL Developer's Guide, because you can't use it in normal SOQL. Each core documentation has a target audience, and the teams try to write for that audience. Ultimately, the FOR UPDATE keyword only appears in a couple of documents that address row lock.

The fact is, as long as you're obeying the basic principles laid out by salesforce.com, FOR UPDATE is largely unnecessary. The two main rules you should follow are to avoid Lookup Skew and Ownership Skew, and writing your code to be as efficient as possible, ideally no transactions over 5 seconds of CPU time.

Most small to medium orgs can operate with exactly zero uses of FOR UPDATE. It's not until you start getting into the large orgs that explicit locking is really necessary. I think they probably could put a little more emphasis on it, but really not much is needed. Salesforce is usually very capable of managing row locks for developers, as long as they don't write inefficient code.

Edit:

The main reason why FOR UPDATE exists is to make sure that no other transaction can change the record before the current transaction has completed, as well as make sure that no other transaction's DML operations might be overridden. Because the query waits for the lock, and no DML operation can occur on a row-locked record, you can guarantee data consistency.

A normal SOQL query without FOR UPDATE can retrieve stale data, while a query with FOR UPDATE will wait until all previous locks on those records (either by FOR UPDATE or by implicit DML locks) have completed before continuing. DML statements themselves will simply throw an UNABLE_TO_LOCK_ROW error if a lock can't be acquired in a very short time window.