10

I use FOR UPDATE locking in a query but when the first execution completed, the second execution (started while records were locked) seems to use the old data set.

I played around for a while and figured out, that when I query them by Id I get the updated records, but when I query them by a text field (generic parent Id), I get the old set.

So for my solution I have to query it twice, first to wait until the first execution releases the records, and again to get the right data afterwards. I would really love to get rid of this second query, since I'm close to the limits already.

So why is this and is there a way to solve this with a single query?

All I have is parent Ids. They are both using the same selector. I never know, which executes first.


This is some code glued together from SE to reproduce this problem:

vf page:

<apex:page controller="Rowlock" >
    <script>
        addEventListener("load", function() {
            insert();

            setTimeout(function(){
                update();

                setTimeout(function(){
                    query();
                }, 1000);

            }, 1000);
        }, true);
    </script>

    <apex:form id="form">
        <apex:actionFunction name="insert" action="{!insertAccount}" reRender="form"/>
        <apex:actionFunction name="update" action="{!updateAccount}" reRender="form"/>
        <apex:actionFunction name="query" action="{!queryAccount}" reRender="form"/>
    </apex:form>
</apex:page>

Controller:

public with sharing class Rowlock {
    Id accountId;
    String name = randomString(8);
    String changedName = name + 'changed';

    public void insertAccount() {
        Account a = new Account(Name = name);
        insert a;
        accountId = a.Id;
    }

    public void updateAccount() {
        Account a = [SELECT Name FROM Account WHERE Name = :name FOR UPDATE];

        lockItForSomeSeconds(2);

        a.Name = changedName;
        update a;
    }

    public void queryAccount() {
        List<Account> updatedRecords = [SELECT Name FROM Account WHERE Name = :changedName FOR UPDATE];

        if(updatedRecords.isEmpty() || updatedRecords[0].Name == name) {
            throw new MyException('Working on invalid Records');
        }
        else {
            throw new MyException('Working on updated Records');
        }
    }

// Replace with Id SOQL to make it work:
// [SELECT Name FROM Account WHERE Id = :accountId FOR UPDATE]

// Unimportant helpers:

    private void lockItForSomeSeconds(Integer x) {
        DateTime start = DateTime.now();

        for(integer i = 0; i < 3000000; i++) {
            if(DateTime.now() > start.addSeconds(x)) {
                break;
            }
        }
    }

    private static String randomString(Integer len) {
        final String chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyz';
        String randStr = '';
        while (randStr.length() < len) {
           Integer idx = Math.mod(Math.abs(Crypto.getRandomInteger()), chars.length());
           randStr += chars.substring(idx, idx+1);
        }
        return randStr; 
    }

    class MyException extends Exception{}
}
  • 3
    From what I understand, this shouldn't happen. Perhaps you've stumbled across a bug? – sfdcfox Jul 17 '18 at 14:28
  • It does sound odd that a query by a field other than ID is bringing back the stale data. Is the query field indexed? – Daniel Ballinger Jul 18 '18 at 23:54
  • 1
    @DanielBallinger I tried both, indexed and regular text, both behave the same. Ps. I just updated the question, to use the name in the queries, so it fails. – Basti Jul 19 '18 at 7:35
  • 1
    @Basti Maybe you just have race condition and the query executes successfully before records are updated? Since you query by updated name the query will return nothing since the record has not been updated yet and has not been committed to Database. Have you tried to refactor your functionality into a trigger? Instead of making two queries, write a trigger once name is updated, execute your business logic. – Patlatus Jul 19 '18 at 11:40
  • 1
    Per my understanding, the query [SELECT Name FROM Account WHERE Name = :changedName FOR UPDATE] doesn't wait at all for executing of update a. It just queries the committed data in the database and finds nothing since update hasn't finished yet and it cannot know in advance that this record will be updated and will have expected name – Patlatus Jul 19 '18 at 11:43
3

Database commit happens after transaction is successfully committed.

So I believe the described behavior is expected.

There is committed record in the database with Name A.

Process A queries this record from the database by Name A and locks it and hangs for two seconds.

Meanwhile Process B tries to query a record by Name B but returns nothing since there is no committed to database record with Name B at the moment. Process B starts in race condition before Process A completes and Process B is unaware of plans of Process A to change the name of record from A to B.

Process B completes.

Process A restores execution after two seconds, changes the name of record A to B and commits this change to database AFTER the process B completes.

Process A completes.

Suggestion solution to desired business logic is not to mess up with queries, but implement one of the following approaches.

  1. Use invariant query parameters which are not changed by another process, like primary or external identifiers.
  2. Use trigger to perform the business logic requirements once the name of record is updated.
  • It's nothing of that, I lost some information while refactoring the question, see the updated if condition for details. it should now make it clear. Both ways always retrieve a different set of data, it's 100% related to the id/name thing. – Basti Jul 19 '18 at 12:11
  • @Basti, what have you updated? Have you tried to use id or external id which you don't change in update dml operation? Have you tried to refactor your business logic using triggers? – Patlatus Jul 20 '18 at 19:36
  • Hi there, sorry I was wrong back then, it took me a while to understand why your description, in fact, makes sense. Sorry! The problem I had (have) is that it solves the abstract issue above but not the issue I really had. Seems like I asked the wrong question. We found another solution for our issue, I will see if I find time to reproduce the original issue. Thanks for your response! – Basti Oct 23 '18 at 14:15

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.