SOQL Record Locking (FOR UPDATE)

Question

I am looking for some more definitive and in depth information about record locking in Salesforce. I will admit I am quite a novice when it comes to the topic of record locking, and am not sure it is even going to solve my issue.

The reason I am investigating the feature is a difficult random bug that has been discovered. The code in question is a trigger that rolls up (lookup relationships hence code needed) some numeric values onto a parent, grandparent and great grandparent object. The value is being correctly calculated on the grandparent and great grandparent, but on rare occasions for reasons unknown the parent value is not calculated correctly. 99 times out of a 100 everything is calculated as expected.

The Salesforce Docs are quite bare of any real information.

There is a forum post which is 2.5 years old that as I read it has conflicting information. Anand claims the record locking is pessimistic, but cbarry9's quote from the unspecified manual is that other processes will wait, suggesting optimistic locking. FWIW I can not find that quote anywhere.

If I was to implement record locking, I would want to use optimistic locking.

Answer 1

FOR UPDATE will take an underlying database write lock on the selected rows, other requests that require the lock while another thread has it locked will wait a short amount of time (in the order of 5-10 seconds) for this lock, and then timeout if it wasn't able to get the lock (this lock timeout gets propagated as a UNABLE_TO_LOCK_ROW error). for roll-up type operations you'll want to ensure that you lock all the parents in a deterministic order.

The forum thread you mention uses an odd definition of optimistic locking, which might be confusing things more than helping.

Answer 2

I performed 4 experiments, take a look:

Experiment 1

Description:

• User A SOQL for a record without FOR UPDATE statement and has long running transaction.
• User B SOQL for a record without FOR UPDATE statement and tries to update record.

Results:

• User B can update record.

Experiment 2

Description:

• User A SOQL for a record with FOR UPDATE statement and has long running transaction.
• User B SOQL for a record without FOR UPDATE statement and tries to update record.

Results:

• User B can NOT update record! Exception has been thrown.

Exception:

• Update failed. UNABLE_TO_LOCK_ROW, unable to obtain exclusive access to this record.

Experiment 3

Description:

• User A SOQL for a record with FOR UPDATE statement and has long running transaction.
• User B SOQL for a record with FOR UPDATE statement and tries to update record.

Results:

• User B can NOT update record! Exception has been thrown.

Exception:

• Record Currently Unavailable: The record you are attempting to edit, or one of its related records, is being modified by another user. Please try again.

Experiment 4

Description:

• User A SOQL for a record without FOR UPDATE statement and has long running transaction.
• User B SOQL for a record with FOR UPDATE statement and tries to update record.

Results:

• User B can update record.

Answer 3

If you wish to test or look for some side effects in your application, a quick way to do it is via Execute Anonymous:

opportunity locked = [select id from opportunity where id ='a03q000000AAAAe' for update];

for (integer i= 0; i<999999999; i++)
    system.debug('nothing '+i);

So you get about a minute until you run out of CPU time and thread will time out. If you try to modify this record via API or via other means, you will get an exception like UNABLE_TO_LOCK_ROW: unable to obtain exclusive access to this record or 1 records a03q000000AAAAe