Is “FOR UPDATE” available in dynamic SOQL

dynamic-soqllockingsoql

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.

Best Answer

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.

Related Topic