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:
- 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'?
- 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.