Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Well I was trying to Implement Record Locking apex but Salesforce Document doesn't seem to help much. Even tried searching stackexchange there was a old question but the answer form StackExchange and details from document seems to be conflicting.

According to Salesforce Doc whenever a second transaction tries to lock a record that is already locked it will throw an "QueryException" whereas from the Stackexchange it seems like the second thread will actually wait.

Q1: I am bit confused about what will happen ? Will it fail(throw exception) or will it wait for 4-5 seconds and then if it doesn't get access it will throw an exception ?

In production we are facing a issue where second thread seems to get access to the record even though we have put a lock, second transaction seems to get access, may be after sometime but doesn't seems to throw any exception immediately. For example we have a method

public static void updateMyAccount(){
Account acc = [SELECT Id FROM Account WHERE isValid = TRUE AND Id='SOME ID' FOR UPDATE];
//some time consuming process...callouts etc here
 acc.isValid__c = false;
 update acc;

}

Scenario:(assuming the system waits)

  • Lets assume Thread 1 calls the above method and acquires a lock on the RECORD.
  • Now while Transaction 1 is processing the record another Thread Transaction 2 tries to access the record and waits for the lock to release
  • While the transaction 2 is waiting thread one changes "isValid__c" to false and the record no more matches the query condition.

Q2 : In the above case will the second thread get access to the record ? or the query will not return any record ?

Q3 : When does the lock gets released ? the Salesforce doc doesn't seems to provide much insight into it. does the lock get released after a DML ? or it waits for full transaction to complete ?

Update 1: Actual Production Scenario In production leads are generally created using web to lead form. We have a page with a button that assign records by pulling records from a queue and changes owner to the current user.

The query looks like

    Lead l = Database.query('SELECT Id,Name ,CreatedDate FROM Leads__c WHERE  Lead_Status__c =\'Open\' AND Owner.Name = 'My Queue' LIMIT 1 FOR UPDATE');
//some calculation 
l.OwnerId = UserInfo.getUserId();

My assumption was when the second user will try to access the same record(by using the same query) it will throw an error. But some how records are getting updated. There are some suspicious records in production where if I check the history

  • User 1 Changed Owner from My queue to User 1 [Same TimeStamp]
  • User 2 Changed Owner from User 1 to User 2 [Same TimeStamp]

The above observation makes me suspicious that the code actually waits. But again the docs :( . What makes it really hard is this situation is really hard to replicate and almost impossible.

share|improve this question
    
Actually, I was able to replicate it. See my answer. There is a small wait time, somewhere between 1-3 seconds, but the query result ends up being modified if the lock timeout doesn't expire. –  sfdcfox Dec 19 '13 at 20:14
1  
Also, on your "suspicious" updates; identical timestamps on a history is easy to do; the time stamps are set to the end of the transaction time (or later), so if a trigger performs one change and then an assignment rule or workflow rule alters the owner again immediately afterwards, you'll get two entries with the exact same time. –  sfdcfox Dec 19 '13 at 20:16

4 Answers 4

up vote 6 down vote accepted

Q1: I am bit confused about what will happen ? Will it fail(throw exception) or will it wait for 4-5 seconds and then if it doesn't get access it will throw an exception?

EDIT: It looks like you get about 5 seconds before it will exclusively fail.

It's an immediate exception. The user will be advised to try their save again. The error will be "UNABLE_TO_OBTAIN_ROW_LOCK."

Q2 : In the above case will the second thread get access to the record ? or the query will not return any record ?

Irrelevant, since the transaction would have already failed. Or, differently, either the prior transaction has already completed, so it would not appear in the results, or you'll get an error because the row can't be locked. There's never a scenario where it would be returned with non-matching criteria.

Q3 : When does the lock gets released ? the Salesforce doc doesn't seems to provide much insight into it. does the lock get released after a DML ? or it waits for full transaction to complete ?

The documentation clearly states:

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.

(Emphasis mine)

Clarification The lock isn't released until sometime after the final commit occurs, usually within a few milliseconds, although post-transaction logic can re-acquire the lock immediately, such as future methods.


So, I decided to test this with some code to get a more definitive answer.

First, I set code that ran around (but less than) 10 seconds, like this:

Controller

public with sharing class rowlock {
    Id accountId = [SELECT Id, name FROM Account where name = 'test' LIMIT 1].Id;

    public void updateAccount() {
        Account[] a = [SELECT Id FROM Account WHERE Id = :accountId and name = 'test' FOR UPDATE];
        for(integer i = 0; i < 3000000; i++) {
            // Do nothing, haha!
        }
        if(!a.isempty()) {
            a[0].name = 'test2';
            update a[0];
        }
        system.debug(logginglevel.error, a.isempty());
    }
}

Page

<apex:page controller="rowlock">
<apex:form id="form">
<script>
addEventListener("load", function() {
    updateAccount();
    updateAccount();
}, true);
</script>
    <apex:actionFunction name="updateAccount" action="{!updateAccount}" reRender="form"/>
    </apex:form>
</apex:page>

During this trial run, the page bombed out on the second transaction with "Record Currently Unavailable..."

Changing 3,000,000 to 100,000, both transactions passed. However, the first transaction (the one with the first lock) returned 1 row, while the second transaction returned 0 rows, thus proving that the lock actually resulted in the query results being modified to exclude the locked record since it no longer met the criteria.

share|improve this answer
    
My initial logic was based on the fact that when the second thread will try to LOCK the record a exception will be thrown but somehow I am getting skeptical about immediate exception looks like it waits. Have a look at the update that I added –  Avidev9 Dec 19 '13 at 19:15
    
Looks like you're right. It shows up in my debug log as 9677ms in a trial run. I'll amend that bit. –  sfdcfox Dec 19 '13 at 20:00
    
well it looks good but this is what I was expecting. I checked all the WF and trigger doesnt they doesnt seem to be interfering. By any chance if the call is initiated using a remoting will it obey the above rules ? I m going try your example with remoting. –  Avidev9 Dec 20 '13 at 5:41
    
Locks are organization-wide, so I'd assume that the locks would be respected with remoting. I encourage you to try it out, though, and let us know. –  sfdcfox Dec 20 '13 at 6:32
    
well locks seem to be with remoting(I was just being skeptical on it to eliminate remoting from probable cause) without any issues. Your script to replicate the issue was really awesome :) . Seems like there is issue somewhere else, Accepting this as a answer as its more closer to what I am observing in the result –  Avidev9 Dec 20 '13 at 7:04

Q1: I am bit confused about what will happen ? Will it fail(throw exception) or will it wait for 4-5 seconds and then if it doesn't get access it will throw an exception ?

This is how I understand it. When you query a record with FOR UPDATE, it will lock the record for that transaction. Anyone can get access to it through a standard SOQL query (aka not attempting to lock it) still, but if they attempt to access it with FOR UPDATE as well, the system will return the QueryException blocking both transactions from locking the record. The delay will occur on DML.

So, in the scenario where Transaction 1 locks a record with FOR UPDATE (the record is locked), Transaction 2 queries and gets the record without FOR UPDATE (the record is not locked), when DML is attempted on that record in Transaction 2, it will give a brief delay to wait for the lock from Transaction 1 to release (if it hasn't already). If it does not release, Transaction 2 will return the UNABLE_TO_LOCK_ROW error.

Q2 : In the above case will the second thread get access to the record ? or the query will not return any record ?

Once a record is queried, it is locked for the remainder of the transaction. Even if you change the values that caused the record to return, that record will still be locked. In the above scenario, Transcation 2 won't be able to access the record until Transaction 1 is completely finished running (assuming both were attempting to query with FOR UPDATE).

Q3 : When does the lock gets released ? the Salesforce doc doesn't seems to provide much insight into it. does the lock get released after a DML ? or it waits for full transaction to complete ?

According to the docs, the lock gets released when the transaction completes.

share|improve this answer
    
Thanks for the clarification, I did some trial with the help of the script provided by @sfdcfox, and the results were same as described by you. The salesforce doc still seems a bit lagging to me. Anyways thanks –  Avidev9 Dec 20 '13 at 7:07

A DML exception will be returned after 10 seconds if the lock has not been released, if I am not wrong the error will be "Unable to Lock Row".

The lock should be released after the first transaction.

share|improve this answer
    
We did some trial run and seems like the wait time was around 4-5 seconds. Anyways I appreciate your help. Thanks! –  Avidev9 Dec 20 '13 at 7:08

It sounds like your code may be susceptible to a race condition because you're re-saving the value that you don't want to necessarily update. This doesn't explain the underlying database logic, but you can issue updates with a new sobject so you don't re-write fields that were not actually impacted:

update new Record__c(Id=rec.Id, Field__c='value');
share|improve this answer
    
Nice suggestion, but my problem was more because of the DB logic and data being fetched by query. Anyways will keep this in mind. Thanks! –  Avidev9 Dec 20 '13 at 7:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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