Kbase P65148: FOR EACH returns records not matching where clause
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/2/2004 |
|
Status: Unverified
SYMPTOM(s):
FOR EACH returns records not matching where clause
FOR EACH ... NO-LOCK WHERE ...
Incorrect record matched WHERE clause before being modified in a separate buffer within an iteration of the FOR EACH
Issue only occurs with Client Server connections to the database.
CAUSE:
This is expected behavior.
By default, a FOR EACH ... NO-LOCK over a client-server connection will read multiple records at once and store these in a client-side prefetch cache. This will reduce network traffic and increase performance somewhat.
However, under some circumstances, this prefetch cache can go out of sync with the actual database. There are no checks to see if this happens, as implementing these would require database access, thus nullifying the performance gained by using the caching mechanism.
FIX:
Add NO-PREFETCH to the FOR EACH ... NO-LOCK.
This will disable the prefetch cache for that loop, avoinding this behavior altogether.