function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Brian CherryBrian Cherry 

Explicit ORDER BY not allowed when locking rows

I'm trying to get pull the oldest case and assign it to a user. Without locking, we are experiening where users are clicking the button to call the query at the same time. If I add in For Update and remove order by, the oldest case isn't ever pulled. The newest case is. Does anyone have a work around to query the oldest case for update?                       


Case caseObj = [select c.CreatedDate,c.ID,c.OwnerId from Case c where

                                                            c.IsClosed=false
                                                            and c.OwnerId in :listGroupIds ORDER BY c.CreatedDate
                                                            limit 1
                                                            for update];
                                                            caseObj.OwnerId = userId;
                                                            update caseObj;
                                                            return caseObj.Id;
                                                            }
                                   catch (QueryException e) {
                                    System.debug ('No Cases Available');
                                   }
Best Answer chosen by Brian Cherry
surasura
  • fist query and find the oldest case record
  • then again query  lock that record using previous query's record id 
       
Case caseObj = [select c.CreatedDate,c.ID,c.OwnerId from Case c where c.IsClosed=false and c.OwnerId in :listGroupIds ORDER BY c.CreatedDate limit 1];

Case caseObjLocked  =  [select c.CreatedDate,c.ID,c.OwnerId from Case c where id=:caseObj.Id FOR UPDATE];

caseObjLocked .OwnerId = userId;

update caseObjLocked ;

 return caseObjLocked .Id;

 

All Answers

Amit Chaudhary 8Amit Chaudhary 8
Locking Statements FOR UPDATE Apex allows you to lock sObject records while they’re being updated in order to prevent race conditions and other thread safety problems. While an sObject record is locked, no other client or user is allowed to make updates either through code or the Salesforce user interface.

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

NOTE:-
1) While the records are locked by a client, the locking client can modify their field values in the database in the same transaction. Other clients have to wait until the transaction completes and the records are no longer locked before being able to update the same records. Other clients can still query the same records while they’re locked.
2) If you attempt to lock a record currently locked by another client, you will get a QueryException. Similarly, if you attempt to update a record currently locked by another client, you will get a DmlException.
3) If a client attempts to modify a locked record, the update operation might succeed if the lock gets released within a short amount of time after the update call was made. In this case, it is possible that the updates will 

You can’t use the ORDER BY keywords in any SOQL query that uses locking

http://amitsalesforce.blogspot.in/2015/03/locking-statements-for-update.html
https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_locking_statements.htm
User-added image

Please mark this as solution by selecting it as best answer if this solves your problem, So that if anyone has this issue this post can help

Thanks,
Amit Chaudhary
amit.salesforce21@gmail.com
Brian CherryBrian Cherry
HI Amit,

Thanks for taking the time to respond. I understand that I can't sort records when doing a a for update.  However, I was seeing if someone has a work around for this. If it's possible to lock the record after the query, have it assign the case, then remove the lock to avoid the case being assigned twice.The problem with Salesforce for update queries, is it pulls the newest records, I need the oldest records.

Thanks,
Brian.
surasura
  • fist query and find the oldest case record
  • then again query  lock that record using previous query's record id 
       
Case caseObj = [select c.CreatedDate,c.ID,c.OwnerId from Case c where c.IsClosed=false and c.OwnerId in :listGroupIds ORDER BY c.CreatedDate limit 1];

Case caseObjLocked  =  [select c.CreatedDate,c.ID,c.OwnerId from Case c where id=:caseObj.Id FOR UPDATE];

caseObjLocked .OwnerId = userId;

update caseObjLocked ;

 return caseObjLocked .Id;

 
This was selected as the best answer
Brian CherryBrian Cherry
Thank you so much SURA!
Burak Yurttas 7Burak Yurttas 7
Hi,

I have a similar issue and i could not get it to work. It throws an error : Compile Error: Initial term of field expression must be a concrete SObject: List<Lead> at line 27 column 77

Here is the code :  
List<Lead> leadsOR = [select l.Id,l.OwnerId from Lead l where 
                                                        l.IsConverted=false 
                                                        and l.OwnerId in :listGroupIds Order By LastActivityDate
                                                        limit 1 
                                                        ];
        
        
        
            //Find an open lead that is assigned to one of those queues
            List<Lead> leads = [select l.Id,l.OwnerId from Lead l where id=:leadsOR.Id limit 1 FOR UPDATE ];


Any help would be appreciated. Thanks