SOQL - concurrent reads of rows when FOR UPDATE clause is used

Question

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.

Answer 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'?

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.

Answer 2

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.