Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

Updating index fields while fetching data from the table using the same index can result in the same row being fetched twice in X++

Conclusion:
There are situations where updating index fields while fetching data from the table using the same index can result in the same row being fetched twice. Recommendation here is to avoid updating index fields while selecting records using the same index. 

For example:

1)
Consider following “GM_Real_Test” table and the highlighted index:

2)
Run following code to populate table records:

static void StringInsert_Real(Args _args)
{
     GM_Real_Test   operationTable;
     int counter, result, row_number;
     Random rand;
     ;
    counter = 1;
    row_number = 500;
    while (counter <= row_number)
    {

        ttsbegin;
        OperationTable.Status = ‘Processed’;
        OperationTable.DocId = strfmt(‘WMS0155827’ + int2str(counter));
        OperationTable.Transaction =  strfmt(‘zzz’ + int2str(counter));
        OperationTable.Insert();
        ttscommit;
        counter += 1;
    }
    info(strfmt(‘%1 Rows successfully inserted’, row_number));

3)
Running a class with below shown code in its run method has different behavior depending on whether it is running on the Client side or on the AOS side.

public void run()
{
    GM_Real_Test    buff1;
 
    int             iCounter = 0;
 
    Random myRand = new Random();
    int r = myRand.nextInt();
   
    info(strfmt(‘Random num = %1’, r));
       
   
    ttsbegin;
    iCounter = 0;
 
    while select forupdate buff1
        where (buff1.Transaction       ==  ‘zzz1’);     
    {
        iCounter++;
           
        info(strFmt(“%1 , %2, %3, %4”,
            iCounter,
            buff1.RecId,
            buff1.Status,
            buff1.Transaction
            ));
           
        buff1.Status = strfmt(‘Processed %1’, r);
        buff1.update();
           
    }
 
    info(strfmt(‘Pass: total = %1’, iCounter));
    ttscommit;
 
    info(‘Done’);
 
}

 

The code runs differently depending upon the tier it executes on Server or Client, this is because:

1) When the class is running on the client side we have the following:
a. Client executes the ‘While select’ statement – the kernel calls the ‘ServerNext’  
    method on the AOS . To avoid round trip, the AOS fetches all the data satisfying the “Where”
    clause (buff1.Transaction == ‘zzz1’) from DB, puts the record set to the buff1 and returns buff1
    back to the client.

b. Client enters the ‘While select’ body and goes through all the records in the buff1. At the end of
    each iteration the ‘While select’ calls kernel “SQL Cursor::next” method implicitly, but this action
    only moves buff1 inner pointer to the next record;

2) When the class is running on the AOS side we have the following:
a. AOS executes the ‘While select’ statement – this statement creates instance of the kernel  
    “SQL Cursor” class and calls the “SQL Cursor::next” method. The “SQL Cursor::next” method 
    fetches the first record satisfying the “Where” clause (buff1.Transaction == ‘zzz1’) from DB and 
    puts the record to the buff1.

b. AOS enters ‘while select’ body and goes through all the records in the buff1. At the end of each
    iteration ‘While select’ calls the “SQL Cursor::next” method implicitly and this action fetches the
     next record satisfying the “Where” clause from DB again. At this point the behavior from the SQL
     server side is as follows:

        Though there is only one record in the DB table satisfying the ‘Where’ clause – in case when we
        have updated the index field of the record inside the ‘while select’ body – it considers the same
        record as not the same and returns it to the AOS – that is why  we have the second iteration.