4

Below is our scenario:
We have a requirement to tag unique number to self service applications which are created through customer community.

We tried to use salesforce auto number but our client had a unique format which they wanted us to use and they gave a bunch of sequence numbers which we loaded into a custom object.

Every time when an application is submitted we run a SOQL to pull a number from this custom object, mark it as unavailable and set it on the application record.

To Prevent issues to due to race condition we did a SOQL for update when we query a record from the unique sequence number object as below:

select id,TNumber__c,Status__c from Application_Number__c where Status__c ='Available' LIMIT 1 FOR UPDATE

if another user gets UNABLE_TO_LOCK_ROW error, we do a retry again and try to get another record from this object.

I read from this link that if 2 users get the same record , for the second user he will UNABLE_TO_LOCK_ROW error after 5-10 seconds.

Here is my question on SOQL for update:

  1. If the lock from the first user gets released within 5 seconds, will the second user get access to the same record even though now the status of the record is 'unavailable'?
  2. If the lock from the first user gets released within 5 seconds and the record doesn't meet the status search criteria where Status__c ='Available' will SF automatically query for a new record?

The reason for these questions are if the second user gets the same record it will result in duplicate sequence number issued to 2 applications.

I did not find anything in the documentation which answers these questions.

P.S: We know that this is a very sub-optimal solution but our client wanted it this way.

4

If the lock from the first user gets released within 5 seconds, will the second user get access to the same record even though now the status of the record is 'unavailable'?

I had to answer the same thing for some of my own architecture, and I can definitively say that if your other operation changes the Status__c to no longer equal 'Available', then your locking query will not return that record.

If the lock from the first user gets released within 5 seconds and the record doesn't meet the status search criteria where Status__c ='Available' will SF automatically query for a new record?

I believe you will get an empty query result, but I have not been able to confirm.

| improve this answer | |
  • thank you, based on question 2 response we may need to re-run the query to get a record. very tricky to understand without documentation – RedDevil Apr 8 at 19:27
  • Yes, another thing to note is that you cannot use both FOR UPDATE and ORDER BY in the same query. So if you want non-arbitrary retrieval, you will need two queries anyway, one to retrieve, then one to lock. If the second query fails, you would rerun the first. Maybe in some type of do/while loop. – Adrian Larson Apr 8 at 19:32
  • Both 1 and 2 (without re-query) are dirty reads...so it sounds like it's behaving similar to a Read Committed isolation level (dirty reads not allowed). – identigral Apr 8 at 20:06
  • @identigral - so what happens for my question 2 it returns no records or does it re-query and get another record? – RedDevil Apr 9 at 11:47
  • 2
    It will return an empty result set. – identigral Apr 9 at 17:58
3

We created Case #26144866 with salesforce and below was their response. Just posted it as an additional answer for benefit of others since for Q2, SF Support mentioned it will query and return a new record.

Question 1. If the lock from the first user gets released within 5 seconds, will the second user get access to the same record even though now the status of the record is 'unavailable'?

Answer: No - the query will be re-run and the user will get a new result. The previous record will no longer be a valid result and will not be returned by the query.

Question 2. If the lock from the first user gets released within 5 seconds and the record doesn't meet the status search criteria where Status__c ='Available' will SF automatically query for a new record?

Answer: Yes - for the same reasons stated above. The query will re-run and will return a new result.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.