Kbase P107230: FOR EACH NO-LOCK does not retrieve newly-created records.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
SYMPTOM(s):
FOR EACH NO-LOCK does not retrieve newly-created records.
Problem only occurs when running in client/server mode.
CAUSE:
In client/server mode, and only with NO-LOCK reads, the remote 4GL server to which the client is attached may decide to pre-fetch records and send a number of them to the client. This depends on the network message size and on the size of the records being pre-fetched.
What happens in then is that the client receives a snapshot of a few records from the database table. Any records inserted later on in the table that would "find place" in such a snapshot, are not sent to the client.
This is entirely intended, and the fact that we do not necessarily see all records is congruent with the "dirty reads" concept connected with NO-LOCK; by using NO-LOCK we explicitly tell the engine that we do not really care about the consistency of each individual records [we can see updates before they are committed, for example] and similarly we indicate that we do not care whether other updates are done on the table at the same time that we are reading. Thus, the remote 4GL server is free to carry out the optimization outlined above to save on the number of network messages between itself and the client.
In shared-memory mode, there is no need for this optimization, as everything is in (shared) memory.
FIX:
Use the NO-PREFETCH option in the FOR EACH loop.
NO-PREFETCH will disable the network-traffic optimization described above: one record will be sent to the client only once the previous one has been released.
The downside is that NO-PREFETCH generates many more network packets, which in turn may impact performance severely, thus use it only in those places where it's absolutely critical that one client catches the records just created by another one. Also, please keep in mind that the reads are still NO-LOCK, which means that what the client reads may be a half-updated version of the final, committed record, or that the client might read a record that has just been created, but is rolled-back soon afterwards.
Alternatively, you can use SHARE-LOCK, which imposes a stricter contract between the client and the server: a record will not be read (and locked) by a client before the previous one has been released, always.