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.
