I have a scenario where an external system is calling Salesforce API, causing a trigger to run. That trigger needs to delete EmailMessage records where the RelatedTo field is an Account.
We're getting UNABLE_TO_LOCK_ROW errors when the external system's API calls overlap and the trigger attempts to delete multiple emails on the same Account at the same time.
This is totally understandable based on how record-locking works. RelatedTo is either a required lookup or a Master-Detail so deleting the EmailMessage would require a lock on the related Account.
I tried to solve the problem by adding FOR UPDATE to the query in the trigger where it fetches the EmailMessage records.
But, we still observe UNABLE_TO_LOCK_ROW as a DmlException (if it were a QueryException, then that might be due to the 10-second limit timing out).
Looking again at the locking documents (and reading the actual words FOR UPDATE), it says that a query with FOR UPDATE "Locks all records retrieved in SELECT
statement that uses FOR UPDATE."
So, if I lock the EmailMessage, I should not expect it to lock the related Account. I'm locking as if I want to update the record. When I delete the EmailMessage, it tries to lock the Account and fails.
So, what I'd really need is a SOQL clause like FOR DELETE. Or I'd need to do an extra SOQL on the RelatedToIds to explicitly lock the Accounts.
So locking FOR UPDATE kind-of locks the record as if you want to update it. But it doesn't appear to take into account something like Roll-Up summaries. So you could lock a record with a Roll-Up summary FOR UPDATE and still get an UNABLE_TO_LOCK_ROW error if you attempt to update it. And it's certainly not strong enough if you plan to delete the record.
Fun, and nearly impossible to test!