Given: 

1)

Consider  the 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 depends 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.

 

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.