1

Inside an Apex before insert trigger, what is the correct way to get an exclusive lock all records of a particular object for the length of the trigger's transaction?

This trigger needs an exclusive lock to perform validation that can't easily be expressed any other way. The canonical example I see is to check for overlapping dates in a record. Plenty of questions and answers on how to do that, but none seem to address the race condition between doing the select followed by the insert.

It's our understanding that a single trigger runs serially within its own transaction. But it doesn't have exclusivity across multiple clients.

Example:

Check if a new record has overlapping dates with any existing records:

  • Client A: Enters before insert trigger
  • Client A: Fetches records and checks for overlapping dates.
  • Client B: Enters before insert trigger
  • Client B: Fetches records and checks for overlapping dates.
  • Client B: Writes new record to the database, trigger ends.
  • Client A: Now has a stale result with an insert pending.

The Salesforce documentation says to use FOR UPDATE to acquire an exclusive lock on certain records:

Apex Developer Guide: Locking Statements

Given the example in the documentation, is the following a valid (or even recommended) way to lock all records?

Account [] accts = [SELECT Id, StartDate, EndDate FROM Account FOR UPDATE];

Note that we're only likely to be looping over a small number of records. Think of it like a membership with monthly billing records. The dates for each monthly Billing record should not overlap with any others for a given membership.

Update 1:

Refer to: Salesforce Platform: Order of Execution Overview

When using FOR UPDATE in a SOQL query, is the "lock" effectively acquired in step 3 of the execution order and then released after step 19/20?

If so, is the following true:

  • The trigger using FOR UPDATE in step 3 will wait for all in-progress events on those records to complete before it can acquire the lock.

  • Once acquired, all subsequent events will then wait on step 1 until the event with the lock completes step 19/20?

  • The code that initiated the FOR UPDATE does not have to do any explicit clean-up to release the lock.

4
  • YMMV but performing SOQL without a WHERE clause is likely to cause performance issues or even governor limit issues. I suggest you do, indeed, include per-Account date range comparison, via the use of dynamically generated SOQL that uses FOR UPDATE on the related objects. There are SOQL query length limits, but here you can have at most 200 unique related objects since you are in a trigger.
    – Phil W
    Oct 30, 2023 at 7:52
  • 2
    why not use a Mutex__c custom object for this use case and do a FOR UPDATE on it? that will single-thread the multiple clients of the trigger
    – cropredy
    Oct 30, 2023 at 16:59
  • @cropredy... That's kind of a neat idea. It's something I'm familiar with in the world I normally live in but didn't consider for Salesforce. Thanks for sharing.
    – kennyc
    Oct 30, 2023 at 18:05
  • It sounds to me like you need to FOR UPDATE on the N Accounts that have Billings in scope of the trigger execution. That gives you the scope of lock you need to uphold this invariant.
    – David Reed
    Oct 30, 2023 at 18:26

1 Answer 1

1

For these kind of use cases, in my org, I have a custom object called Mutex__c with one custom field

  • Lockable_Item__c that is Text (255) External Id Unique

Then I have a service layer class called LocksServiceImpl with a method called acquire(String lockItem)

  • Does a SELECT Id FROM Mutex__c WHERE Lockable_item__c = :lockItem For Update
  • If no rows returned, inserts a Mutex__c
  • If UNABLE_TO_LOCK_ROW, executes a retry x times before giving up

All this built with the Lightning Platform Enterprise pattern of selectors and service layers so they can be mocked for unit tests

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .