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.

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.