3

The SOQL doc states that FOR UPDATE is available for inline SOQL

To lock a set of sObject records in Apex, embed the keywords FOR UPDATE after any inline SOQL statement. For example, the following statement, in addition to querying for two accounts, also locks the accounts that are returned:

Account [] accts = [SELECT Id FROM Account LIMIT 2 FOR UPDATE];

But what about dynamic SOQL?

Account [] accts = Database.query('SELECT Id FROM Account LIMIT 2 FOR UPDATE');

The Dynamic SOQL doc doesn't say you can't use it and I know you don't get an error if you do use it - but perhaps the FOR UPDATE is just ignored. That would be bad.

1
  • 1
    TIL that what I have always called static SOQL is really called inline SOQL.
    – Phil W
    Commented Jun 1, 2022 at 22:27

1 Answer 1

7

You can't test record locking in testmethods so you'll need to use Execute Anonymous and some simulator that forces the FOR UPDATE locking condition

I borrowed from Dan Appleman's 4th Edition Advanced Apex Programming Chapter 8 Concurrency Handling DML Lock errors, pages 192-6 for a simulator:

public inherited sharing class ConcurrencySimulator {

  public static void delay(Integer seconds) { //serialize large array to impose delay
    Integer[] largeArray = new List<Integer>();
    for (Integer x=0; x < 10000; x++) {largeArray.add(x);}
    for (Integer counter = 0; counter < seconds * 4; counter++) {String s = JSON.serialize(largeArray);}
  }


  @future
  public static void incrementPessimistic(Double amount, Integer delayBefore, 
                                          Integer delayFromQuery, Integer delayAfter) {
    if (delayBefore>0) {delay(delayBefore);}
 // Opportunity[] ops = [SELECT Id, Amount FROM Opportunity WHERE Name = 'ConcurrencySimulator' FOR UPDATE];
    Opportunity[] ops = Database.query('SELECT Id, Amount FROM Opportunity ' + 
                                         'WHERE Name = \'ConcurrencySimulator\' FOR UPDATE');
    for (Opportunity o: ops) {
        o.Amount = o.Amount == null ? amount : o.Amount + amount;
    }
    if (delayFromQuery >0) {delay(delayFromQuery);}
    update ops;
    if (delayAfter >0) {delay(delayAfter);}
  }
} 

You can see I experimented with both inline and dynamic SOQL. The tester assumes there is an Opportunity with Name = ConcurrencySimulator (yes - I could have made this more generalized)

Using execute anonymous, I initiated two future transactions in one go:

ConcurrencySimulator.incrementPessimistic(10,0,12,0);  // wait 12 "secs" after doing the query and before doing the DML
ConcurrencySimulator.incrementPessimistic(10,0,12,0);  // " "

By examining the respective future debug logs, it was easy to see that one of the two future transactions had to wait "several" seconds before returning the SOQL's 1 row.

15:00:11.16  (20698512)  |SOQL_EXECUTE_BEGIN|[20]|Aggregations:0|SELECT Id, Amount FROM Opportunity WHERE Name = 'ConcurrencySimulator' FOR UPDATE
15:00:18.761 (7761103486)|SOQL_EXECUTE_END|[20]|Rows:1. // 7.6 real secs!

Hence, FOR UPDATE works in dynamic SOQL.

Conclusion: Documentation needs amending.

If anyone has a counter-argument, please post a separate answer.

2
  • 2
    I had never doubted that it was supported in dynamic SOQL (having been using it this way in high concurrency scenarios for years) but it is interesting that the documentation seems to suggest otherwise. Nice Q&A!
    – Phil W
    Commented Jun 1, 2022 at 22:29
  • 1
    Imagine my surprise when I read that doc on locking - this is after learning today that FOR UPDATE locks are released as soon as a callout is done - something added to the doc in v46 @PhilW
    – cropredy
    Commented Jun 1, 2022 at 22:34

You must log in to answer this question.

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