Friday, February 15, 2013

AX Issue: Cannot edit a record in Sales Orders (SalesTable). An update conflict occured due to another user process deleting the record or changing one or fields in the record

Occasionally you can see the error 'Cannot edit a record in Sales Orders (SalesTable). An update conflict occurred due to another user process deleting the record or changing one or fields in the record' (See Figure 1 below). The SalesTable table may not be the actual table name you are seeing but it was for me. This table can be any table in AX. I'm just going to use the example I dealt with.

Figure 1 -The error of interest

There are a lot of  solutions out there that people post about but no one really talks about why this is occurring; they only provide the solution. I'm not a fan of just providing answers as that answer may not be correct or may not apply to everyone. Also, I'm making an effort to add to the community with new content instead of barfing out copy and pasted things from other sites.

The reason why this is occurring is due to a conflict error on the order from a field list selection. When a record is selected forUpdate, the system will store the RecVersion number on that record in the buffer (with Optimistic Concurrency Control (OCC) table property set to 'Yes'). Each time the table is updated, the field RecVersion is updated with a random number that is saved by kernel. When the .update() is called, the system will check if the RecVersion on the table in the DB is equal to the RecVersion in the buffer which was stored at the time the select forUpdate. If these numbers are different, the system indicates that the record has already been changed since it was last selected and throws the error. I used this post as a spring board: Dynamics AX Czech Support Team - Conflict error when list of fields is used in select during update transaction I did my own testing using the code I'm providing at the bottom.

There are three ways to resolve this issue (see case 1 below for issue occuring) :
  1. Add RecVersion field into the selection (see case 3 below)
    1. ex/ select firstonly salesId, custAccount, RecVersion from salesTable; 
  2. Use forupdate keyword in the select statement (see case 2 below)
    1. ex/ select firstonly forupdate salesId, custAccount from salesTable; 
  3. Call [TableName].reread() before the update. (see case 4 below)
    1. ex/  salesTable.reread();
I ran into this issue with the Find method on the SalesTable so I couldn't use options 1 or 2. Option 3 worked for me.

Here is some proof of concept for people if you want to learn more about this:

static void daxTestRecordLock(Args _args)
{    
    daxTestScenarioRecords daxTestScenarioRecords;
    int scenarioToRun = 1;

    ttsBegin;    
    switch (scenarioToRun)
    {
        case 1 : 
            info ("SCENARIO 1 - This will FAIL due to the 'selectForUpdate' being its own line");
            select firstOnly RecId, Parameter6, Parameter7 from daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;
           
            daxTestScenarioRecords.selectForUpdate(true);    
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.update();    
            daxTestScenarioRecords.Parameter8 = 'Second Update';
            daxTestScenarioRecords.update();  
            break;
    
        case 2 :
            info ("SCENARIO 2 - This will PASS due to the 'forUpdate' in the select statement");
            select firstOnly forUpdate RecId, Parameter6, Parameter7 from daxTestScenarioRecords
               where daxTestScenarioRecords.recId == 5637153185;
               
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.update();    
            daxTestScenarioRecords.Parameter8 = 'Second Update';
            daxTestScenarioRecords.update();  
            break;
    
        case 3 : 
            info ("SCENARIO 3 - This will PASS due to the recVersion being selected even though 'selectForUpdate' was its own line");
            select firstOnly RecId, Parameter6, Parameter7, recVersion from daxTestScenarioRecords
               where daxTestScenarioRecords.recId == 5637153185;           
            
            daxTestScenarioRecords.selectForUpdate(true);  
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.update();    
            daxTestScenarioRecords.Parameter8 = 'Second Update';
            daxTestScenarioRecords.update();  
            break;
            
        case 4 : 
            info ("SCENARIO 4 - This will PASS due to the reread() before the second update");
            select firstOnly RecId, Parameter6, Parameter7 from daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;
            
            daxTestScenarioRecords.selectForUpdate(true);    
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.update();    
            daxTestScenarioRecords.reread();
            daxTestScenarioRecords.Parameter8 = 'Second Update';
            daxTestScenarioRecords.update();  
            break;
           
        case 5 : 
            info ("SCENARIO 5 - This will PASS due to not selecting field list");
            select firstOnly * from daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;    

            daxTestScenarioRecords.selectForUpdate(true);    
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.update();    
            daxTestScenarioRecords.Parameter8 = 'Second Update';
            daxTestScenarioRecords.update();  
            break;
    
        case 6 : 
            info ("SCENARIO 6 - This will PASS due to not selecting field list");
            select firstOnly daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;     
            
            daxTestScenarioRecords.selectForUpdate(true);    
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.update();    
            daxTestScenarioRecords.Parameter8 = 'Second Update';
            daxTestScenarioRecords.update();      
            break;
    }    
    ttsCommit;
}


Figure 2 - Proof of concept output from the code above 

4 comments:

  1. Hi we faced a similar issue now but surprisingly this issue happens in a job but the same code when executing as batch or even normal client the issue doesn't come. We found consistency with the job when we select the field list the same code in a batch or class does not throw the error. Any ideas why this behavior is not consistent

    ReplyDelete
  2. I have a few ideas but hard to pin it down without more info. Just to clarify the issue, a set of code that can be condensed into a job (with all code included in that job) is throwing the error above, but not when that same job is executed from the batch engine or through a form button (or similar).

    The next thing I'd do to sleuth would be to create a batch job which executes the job that is throwing the error. Can you try that?

    you can email me code or paste below if you are allowed and want me to try to recreate issue. I'm interested as to the cause...

    ReplyDelete
  3. By 'executes the job' I mean actually calls your job object from the AOT that is throwing the error and not just similar code. You can also do this from a button which should be as easy as a property change on an action button.

    ReplyDelete
  4. Try putting a .reread() between your updates to reread the record buffer...that might work.
    daxTestScenarioRecords.Parameter7 = 'First Update';
    daxTestScenarioRecords.update();
    daxTestScenarioRecords.reread();
    daxTestScenarioRecords.Parameter8 = 'Second Update';
    daxTestScenarioRecords.update();

    ReplyDelete