How do you acquire an exclusive lock on an object within an Apex before insert trigger?

Question

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.

Answer 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