This blog contains posts from the Microsoft Dynamics AX support team highlighting interesting features or issues within Microsoft Dynamics AX.
Consider the following "GM_Real_Test" table and the highlighted index:
Run following code to populate table records:
static void StringInsert_Real(Args _args)
int counter, result, row_number;
counter = 1;
row_number = 500;
while (counter <= row_number)
OperationTable.Status = 'Processed';
OperationTable.DocId = strfmt('WMS0155827' + int2str(counter));
OperationTable.Transaction = strfmt('zzz' + int2str(counter));
counter += 1;
info(strfmt('%1 Rows successfully inserted', row_number));
Running a class with below shown code in its run method has different behavior depends on whether it is running on the Client side or on the AOS side.
public void run()
int iCounter = 0;
Random myRand = new Random();
int r = myRand.nextInt();
info(strfmt('Random num = %1', r));
iCounter = 0;
while select forupdate buff1 where (buff1.Transaction == 'zzz1');
info(strFmt("%1 , %2, %3, %4", iCounter, buff1.RecId, buff1.Status, buff1.Transaction ));
buff1.Status = strfmt('Processed %1',r);
info(strfmt('Pass: total = %1', iCounter));
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.
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.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics